PostgreSQL backend

Your books live in PostgreSQL — transactional, backed up, and readable by any tool that speaks SQL.

• PostgreSQL under everything, with proper transactions and referential integrity.
• A documented schema; the tables mean what they say.
• Query it directly for reporting, or point a BI tool at it.
• Scales well past the point most small businesses will reach.
• Standard dumps mean no lock-in: take your data and go, at any time.

Where to find it Dashboard › Backup

Working with the database directly

1. Each dataset is one PostgreSQL database. The main tables are ar, ap and gl for transaction headers, acc_trans for every ledger line, chart for the accounts, customer, vendor, parts, oe for orders and quotations, and invoice/orderitems for their lines.
2. A backup from the dashboard is a plain pg_dump; restore it into any PostgreSQL and the same tables are there.
3. Ask us for a read-only connection if you want to point a reporting tool at a live dataset.

Queries that answer real questions

The schema is small enough to query by hand. A few that customers actually use, against a read-only connection:

1. Sales summary: SELECT ar.invnumber, ar.transdate, c.name, ar.netamount, ar.amount - ar.netamount AS tax, ar.amount, ar.paid FROM ar JOIN customer c ON c.id = ar.customer_id;
2. Customer balances at a date: SELECT ct.customernumber, ct.name, SUM(0 - ac.amount) FROM customer ct JOIN ar ON ar.customer_id = ct.id JOIN acc_trans ac ON ac.trans_id = ar.id JOIN chart c ON c.id = ac.chart_id WHERE ac.transdate <= ’2026-06-30’ AND c.link = ’AR’ GROUP BY 1, 2 ORDER BY 1;
3. Cash accounts with balances: SELECT accno, description, (SELECT SUM(amount) FROM acc_trans WHERE chart_id = chart.id) FROM chart WHERE link LIKE ’%_paid%’;
4. Sales by month and department: SELECT TO_CHAR(transdate, ’YYYY-MM’), d.description, SUM(netamount) FROM ar JOIN department d ON d.id = ar.department_id GROUP BY 1, 2 ORDER BY 1;
5. Integrity check — unbalanced transactions (should return nothing): SELECT trans_id, SUM(amount) FROM acc_trans GROUP BY trans_id HAVING SUM(amount) <> 0;
6. Sign convention: in acc_trans debits are negative and credits positive, so receivables balances come out as SUM(0 - amount).

From the blog

• ERROR: relation “id” already exists — In a forum post from July 2009 on the SQL-Ledger User Forum, a user named Matt (username “red69p51”) reported an issue while creating a new database using…
• Upgrade Failure Due to Duplicate Invoice IDs — In August 2009, Dominic Raywood (username “RubiconCSL“) posted on the SQL-Ledger User Forum seeking assistance with an upgrade issue.