# 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.

| Column | Type | Description |
| --- | --- | --- |
| `type` | text | The kind of transaction, as QuickBooks names it, such as `Invoice`. |
| `oid` | text | The QuickBooks id, unique within its type rather than across the table. |
| `date` | date | The date the transaction is posted under, not when it was entered. |
| `memo` | text | The memo on the transaction as a whole; note that in some cases, lines can carry their own. |
| `doc_num` | text | The document number shown to people, such as an invoice number. Not unique. |
| `created_at` | timestamp | When QuickBooks first created this transaction. This is *not* the transaction post date. |
| `modified_at` | timestamp | When this transaction was last changed in QuickBooks. |

### transaction_lines

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

| Column | Type | Description |
| --- | --- | --- |
| `id` | text | Identifies this line. |
| `transaction_type` | text | The type of the transaction this line belongs to; joins to `transactions.type`. |
| `transaction_oid` | text | The oid of the transaction this line belongs to; joins to `transactions.oid`. |
| `date` | date | The parent transaction's date, copied here so a query can filter without joining. |
| `amount` | numeric | The signed amount this line posts, as an exact decimal. See the double-entry note above. |
| `home_amount` | numeric | The same amount in the company's own currency, for multi-currency books. |
| `memo` | text | The memo on this line. |
| `combined_memo` | text | All relevant memos for this line joined together. |
| `account_oid` | text | The account this line posts to; joins to `accounts.oid`. Filtering on the joined account is what picks one side of a double-entry transaction. |
| `offset_account_oid` | text | The account on the other side of this posting; joins to `accounts.oid`. |
| `offset_account_fqn` | text | The offsetting account's full name, so the other side reads without a join. |
| `class_oid` | text | The class this line is tagged with, if any; joins to `classes.oid`. |
| `customer_oid` | text | The customer this line is attributed to, if any; joins to `customers.oid`. |
| `department_oid` | text | The department this line is tagged with, if any; joins to `departments.oid`. |
| `vendor_oid` | text | The vendor this line is attributed to, if any; joins to `vendors.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.

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id; what the `account_oid` columns refer to. |
| `name` | text | The account's own name, without its parents. |
| `num` | text | The account number, as text, because leading zeroes are meaningful. |
| `fqn` | text | The full path joined with colons, as in `Expenses:Travel`. A subtree is a `LIKE` prefix match. |
| `type` | text | The account's type, as QuickBooks names it. |
| `subtype` | text | The narrower QuickBooks classification under type. |
| `class` | text | Which side of the books this account falls on. Unrelated to the `classes` table. |
| `currency` | text | The currency this account is denominated in. |
| `description` | text | The description entered on the account in QuickBooks. |
| `parent_oid` | text | The account this one sits under, or null at the top of the tree. |
| `active` | bool | Whether the account is still in use. |

### 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`.

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id; what `transaction_lines.class_oid` refers to. |
| `name` | text | The class's own name, without its parents. |
| `fqn` | text | The full path from the top of the tree, joined with colons. |
| `parent_oid` | text | The class this one sits under, or null at the top of the tree. |
| `active` | bool | Whether the class is still in use. |

### 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`.

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id; what `transaction_lines.department_oid` refers to. |
| `name` | text | The department's own name, without its parents. |
| `fqn` | text | The full path from the top of the tree, joined with colons. |
| `parent_oid` | text | The department this one sits under, or null at the top of the tree. |
| `active` | bool | Whether the department is still in use. |

### customers

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

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id; what `transaction_lines.customer_oid` refers to. |
| `display_name` | text | The name shown for this customer in QuickBooks. |
| `active` | bool | Whether the customer is still in use. |

### vendors

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

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id; what `transaction_lines.vendor_oid` refers to. |
| `display_name` | text | The name shown for this vendor in QuickBooks. |
| `company_name` | text | The vendor's company name, where it differs from the display name. |
| `active` | bool | Whether the vendor is still in use. |

### 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:

```sql
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

```sql
-- 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.

```sql
select table_name from information_schema.tables;

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

Next: [Troubleshooting](/docs/db/troubleshooting.md), or back to the [Overview](/docs/db.md).