How SQL-Ledger fits together

SQL-Ledger is one application with one database, not a bundle of modules that exchange files. Every screen reads the same master data, every document that has a financial effect posts into the same ledger table, and every report is a query over that table. This page explains the three layers and how information moves between them; the feature pages then cover each screen in turn.

The three layers

  • Master data — customers, vendors, parts and assemblies, employees, projects, and the chart of accounts. These are set up once and referred to by everything else. The chart is the linking structure: every part, customer, tax and payment method points at accounts in it.
  • Documents — quotations, orders, invoices, receipts and payments, time cards, payroll runs, journal entries. Each is keyed on its own screen, refers to master data, and carries its own header table (ar, ap, oe, gl) with line tables (invoice, orderitems) underneath.
  • The ledger — a single table, acc_trans, holding one row per debit or credit, keyed to the document that made it and the account it hit. Financial statements, the trial balance, ageing, tax returns and the GL report are all read from here.

The everyday picture: sales, purchasing, cash and the ledger

Most businesses live in four modules. You invoice customers and receive their money; you record what vendors bill you and pay them; the bank account sits in the middle; and anything else goes in as a journal entry. Every one of those actions writes rows into the same ledger, and the reports read them back.

Sales, purchasing, cash and journal entries all posting into acc_trans, read by the reports Sales (AR) Purchasing (AP) Cash General ledger Customer Sales invoiceor AR transaction for non-stock Receipt Vendor Vendor invoiceor AP transaction for expenses Payment / cheque Bank & cash accounts Receipts & paymentsmoney in, money out Bank reconciliation Chart of accounts Journal entryaccruals, depreciation, corrections Year end & lock date every Post writes rows here acc_trans — the ledger one row per debit or credit, against an account in the chart Reports Who owes you (ageing) Whom you owe (ageing) Income statement Balance sheet Tax return & trial balance
The four modules most businesses use. A receipt or payment is the same thing seen from two sides: it settles an invoice in AR or AP and moves money through Cash. Everything with a solid outline posts to the one ledger, and the reports read from it.

The full picture

The rest of the system — quotations and orders, point of sale, inventory and assemblies, payroll, time cards, vouchers, the storefront — either feeds those four modules or posts into the same ledger alongside them. Solid arrows are postings to the ledger. Dashed arrows are documents that carry information forward without posting anything: a quotation or an order changes nothing in the books until it becomes an invoice.

Flow of information from master data through documents into the acc_trans ledger and out to reports Master data Customers Vendors Parts & assemblies Employees Projects Chart of accounts every account, tax and link Documents SALES Quotation Sales order Sales invoice AR transaction POS saleposts at once Receipt PURCHASING RFQ Purchase order Vendor invoice AP transaction Payment / cheque buy in for the order PEOPLE & LEDGER Time cardbilled via an invoice Payroll run GL entry Voucher batch, posted on approval Recurring schedule Storefront order → sales order post acc_trans — the ledger one row per debit or credit: trans_id, chart_id, amount, date headers in ar / ap / gl; lines in invoice Inventory & cost on hand, COGS, average cost Audit trail & lock date who changed what; locked periods accounts referenced by every row Reports — all read from the ledger Trial balance Balance sheet Income statement GL report AR / AP ageing Tax collected / paid Bank reconciliation Transaction reports → drill back to the document Year end: close income & expense to equity
Master data at the top is referred to by every document. Documents on the left and centre are the screens you work in; only the ones with a solid outline post. Everything that posts writes rows into acc_trans, and every report at the bottom is a query over those rows.

What happens when a document posts

Take a sales invoice. Pressing Post does all of the following in one database transaction, so the books can never be half-updated:

  1. A header row is written to ar: customer, dates, invoice number, currency and rate, totals, and who posted it.
  2. One row per line goes to invoice: the part, quantity, price, discount, and the project if any.
  3. For each stocked part, the on-hand quantity is reduced in the chosen warehouse and the cost of goods is worked out from the layers it came in at.
  4. The ledger rows are written to acc_trans: a debit to the receivables control account for the total, a credit to each income account for its lines, a credit to each tax account, and a debit to COGS with a matching credit to inventory for the stock that left. Every row carries the invoice’s trans_id, the chart_id of the account, the amount and the date.
  5. If a payment was keyed on the same screen, a debit to the bank account and a credit to receivables follow.
  6. If the audit trail is on, the action is logged with the user and the moment.

A vendor invoice does the same in the other direction (payables, expense or inventory, tax paid). A payment or receipt writes just the bank and control-account rows against the invoice it settles. A payroll run writes wages expense, each deduction’s liability, and the net pay to payables. A journal entry writes exactly the rows you keyed. Whatever the origin, the rows look the same in acc_trans, which is why one trial balance covers all of them.

What does not post

  • Quotations, RFQs, sales orders and purchase orders. They live in oe and orderitems, reserve or expect stock, and carry their lines forward — but nothing reaches acc_trans until an invoice is raised from them.
  • Time cards, until they are billed onto an invoice.
  • Vouchers, until the batch is approved and posted. Until then they are held with a voucher number and can be edited or discarded.
  • Recurring schedules: the template document is stored; each occurrence is posted when it is processed.

Why the chart of accounts matters

Every row in the ledger points at an account, so the chart decides what the reports can say. Accounts carry flags for what may use them: a part’s income, COGS and inventory accounts, a customer’s receivables account, the bank accounts offered on a receipt, the tax accounts that appear as tax codes. Departments and projects are additional keys on the same rows, which is how a division or a job can be reported without a second chart.

Chart of accounts listing with account types and drop-down links
The chart: each account’s type and the screens it is linked to.

Reading it back

Because everything is in one table, every report is a filter over it: the trial balance sums each account between two dates, the balance sheet sums the asset, liability and equity accounts up to a date, the income statement sums income and expense between two dates, ageing groups the open receivables rows by customer and age, the tax report sums the tax accounts. Every figure on every report is a link back to the document that made the rows, and that document links back to the customer, vendor, part or employee it referred to.

General ledger report for one account with running balances
The GL report on one account: the ledger rows themselves, each one a link to its document.

Where to go next

Accounts receivable →