Tables & SQL

We support a focused subset of PostgreSQL syntax focused on analytical queries. Below we describe the schema and syntax you can leverage to build your analytical queries and applications.

The Tables

There are several tables in the public schema. Table names are given in idiomatic snake_case. Each table and table column also carries a description inside the database.

transactions

One row per QuickBook transaction in your books. Note that unlike the QuickBooks Online API, line level information (i.e. amounts, account, etc.) is not provided directly in the transaction table. You must join with transaction_lines for that information.

transaction_lines

One row per line of a QuickBooks transaction. Joins to transactions on both transaction_type and transaction_oid.

accounts

The chart of accounts, as a tree. Each account may have a parent. To simplify matching all subaccounts under a parent account, the fully qualified name of an account is given in the fqn column.

classes

One of the two tags that split activity — classes by line of business. A tree, like the chart of accounts, so fqn is the full path. Join it from transaction_lines.class_oid.

departments

The other tag, by place — called locations in some books. Same shape as classes, and also a tree. Join it from transaction_lines.department_oid.

customers

Who a line is attributed to on the income side. Join it from transaction_lines.customer_oid.

vendors

Who a line is attributed to on the expense side. Join it from transaction_lines.vendor_oid.

readme

A one-row table holding a plain-text description of everything on this page, so a client — or an LLM writing SQL for you — can learn the dialect without leaving the database: select guide from readme;

Supported SQL

One SELECT per statement. Within that:

• Projection: *, columns, aliases, literals, arithmetic (+ - * /), and || string concatenation.
• FROM with joins: INNER, LEFT, RIGHT, FULL, CROSS.
• WHERE: =, <>, <, >, <=, >=, AND/OR/NOT, IS [NOT] NULL, IS TRUE/FALSE.
• LIKE / NOT LIKE, case-sensitive as in PostgreSQL.
• IN over a literal list — where type in ('Invoice', 'Bill').
• CASE WHEN … THEN … ELSE … END, and casts (x::int, x::date, x::text).
• GROUP BY and HAVING; a projected column must be grouped by or sit inside an aggregate.
• ORDER BY with ASC/DESC and NULLS FIRST/LAST.
• SELECT DISTINCT, LIMIT, OFFSET, and UNION / UNION ALL.
• Parameters: $1, $2, … bound by your driver. Types are inferred.

Functions

Aggregates: count (including count(*) and count(distinct x)), sum, avg, min, max, and bool_and/bool_or/every.

Scalar: coalesce, nullif, lower, upper, length, substr, abs, and date_trunc.

Dates

date_trunc('year'|'quarter'|'month'|'week'|'day', date) buckets server-side and can be a GROUP BY or ORDER BY key — so a monthly or yearly trend is one grouped query, not one query per period.

Filter dates by comparing to literals: where date >= '2023-01-01' and date <= '2023-12-31'. A timestamp column (created_at, modified_at) has to be cast to a date first, and that cast really converts: date_trunc('month', created_at::date).

The account tree

accounts.fqn is a colon-joined path, so a whole subtree is a prefix match — and it works inside a join, with no round trip to collect ids first:

select a.type, sum(l.amount)
  from transaction_lines l
  join accounts a on l.account_oid = a.oid
 where a.fqn like 'Expenses:%'
 group by a.type;

Not supported

Attemping to use the following syntax returns a PostgreSQL error. The error can in some cases include useful information about alternatives.

• Writes, DDL, and COPY. The connection is read-only.
• Subqueries — both a derived table in FROM and IN (SELECT …). Use a join, a literal value list, or two queries.
• Window functions (OVER), INTERSECT, and EXCEPT.
• BETWEEN (write two comparisons) and ILIKE (write lower(x) like lower(…)).
• extract(…) — use date_trunc. Also now(), round(), and most other scalar functions.
• Aggregates with no equivalent here: stddev, variance, string_agg, array_agg, the percentile family.
• Cursors (DECLARE/FETCH), PREPARE, and more than one statement per message.

Transaction control (BEGIN, COMMIT, ROLLBACK) and SET are accepted and do nothing.

Gotchas

• active columns come back as t/f, and dates as YYYY-MM-DD.
• LIKE is case-sensitive, as in PostgreSQL, so 'expenses:%' matches nothing.
• An account name containing a colon makes its fqn path ambiguous, since the path separator is a colon too.

Examples

-- Look before you choose: what account types exist?
select type, count(*)
  from accounts
 group by type
 order by type;

-- Monthly revenue.
select date_trunc('month', l.date) as month,
       sum(l.amount) as revenue
  from transaction_lines l
  join accounts a on l.account_oid = a.oid
 where a.type = 'Income'
 group by date_trunc('month', l.date)
 order by month;

-- Expense by account, for one year.
select a.fqn, sum(l.amount) as amount
  from transaction_lines l
  join accounts a on l.account_oid = a.oid
 where a.type = 'Expense'
   and l.date >= '2024-01-01' and l.date <= '2024-12-31'
 group by a.fqn
 order by sum(l.amount) desc;

-- Expense by class, quarter over quarter.
select date_trunc('quarter', l.date) as quarter,
       coalesce(c.fqn, '(unclassified)') as class,
       sum(l.amount) as amount
  from transaction_lines l
  join accounts a on l.account_oid = a.oid
  left join classes c on l.class_oid = c.oid
 where a.type = 'Expense'
 group by date_trunc('quarter', l.date), coalesce(c.fqn, '(unclassified)')
 order by quarter, class;

-- Find the transactions behind a number.
select t.date, t.type, t.doc_num, l.combined_memo, l.amount
  from transaction_lines l
  join transactions t
    on t.type = l.transaction_type and t.oid = l.transaction_oid
  join accounts a on l.account_oid = a.oid
 where a.fqn like 'Expenses:Travel%'
 order by t.date desc
 limit 50;

Querying the schema in SQL

You can use the information_schema table to explore the schema via a SQL query.

select table_name from information_schema.tables;

select column_name, data_type
  from information_schema.columns
 where table_name = 'transactions'
 order by ordinal_position;