# Tables & SQL

We support a subset of PostgreSQL syntax focused on analytical use cases. Below we describe the schema and syntax you can use in your queries.

## 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 QuickBooks transaction in your books. Note that, unlike in 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 (not unique 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 lines can also carry their own memos. Different types of transactions may put useful information in one or the other. |
| `doc_num` | text | The transaction's "document number," such as the invoice number for an invoice. This is an arbitrary user-entered string, not guaranteed to be numeric, or 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. When joining with `transactions`, join on both `transaction_type` and `transaction_oid`.

| Column | Type | Description |
| --- | --- | --- |
| `id` | text | Unique string identifier for 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. |
| `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 | For two-line transactions, the account on the other side of the transaction; joins to `accounts.oid`. |
| `offset_account_fqn` | text | The offsetting account's fully-qualified name, for convenience. |
| `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, unique among accounts; what the `account_oid` columns refer to. |
| `name` | text | The account's name. |
| `num` | text | The account number, as text, so that leading zeroes are significant. |
| `fqn` | text | The account's "fully-qualified name," which is a path joined with colons, such as `Expenses:Travel`. A subtree can be queried using a `LIKE` prefix match. |
| `type` | text | The account's type, as QuickBooks calls it. |
| `subtype` | text | The narrower QuickBooks classification under type, also known as "Detail Type" in QuickBooks. |
| `class` | text | The account's class, which is one of: `"asset"`, `"liability"`, `"equity"`, `"income"`, or `"expense"`. Unrelated to the `classes` table (transaction classes). |
| `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

Transaction classes, which are tags used to classify transactions or lines. (Whether the class can vary per line within a transaction depends on your QuickBooks settings.) Like accounts, they form a hierarchical tree, and `fqn` contains the full path.

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id, unique among classes; what `transaction_lines.class_oid` refers to. |
| `name` | text | The class's name. |
| `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

Another tag for categorizing transactions, called *locations* in some books. (It is possible for this value to differ between lines of a transaction, for example with location-tracked journal entries in QuickBooks.) Like accounts and classes, departments form a hierarchical tree.

| Column | Type | Description |
| --- | --- | --- |
| `oid` | text | The QuickBooks id, unique among departments; what `transaction_lines.department_oid` refers to. |
| `name` | text | The department's name. |
| `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.

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