# App Database

A deployed app reads and writes a per-app PostgreSQL database. You write the schema as `migrations/`, grant each function table access in `gipity.yaml`, and query from function code through the injected `db` helper. This is the database reference; for writing the functions themselves see [app-development](app-development.md), and for the migration / deploy phase see [deploy](deploy.md).

## Setup
- `gipity db create <name>` / `gipity db drop <name>` - create or drop databases
- `gipity db list` - list existing databases
- `gipity db query "<sql>"` - execute SQL (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, etc.)

Use PostgreSQL syntax: `SERIAL`, `BOOLEAN`, `TIMESTAMPTZ`, `JSONB`, `TEXT`.

**Primary keys:** use a `short_guid VARCHAR(20)` PK (as the seeded tables do), not `BIGSERIAL` - don't expose raw integer ids. Mint the value with the injected `guid(prefix)` peer (second-arg service, no declaration): `guid('adv')` → `adv_a7k2mq9pd3xe` (`{prefix}_` + 12 unambiguous crypto-random chars; prefix ≤ 7 chars so the result fits `VARCHAR(20)`). `bigint`/`BIGSERIAL` columns return from functions as JSON **strings** (node-pg behavior), so coerce/compare them as strings in tests and clients.

Limits: database count is per-plan (run `gipity plan` to see), 500 rows / 128 KB per query, 50,000 chars per statement.

Destructive statements (DROP, TRUNCATE) run as sent. Take a `gipity db checkpoint` first if you may want the data back (`gipity db restore`).

**Schema lives in `migrations/`, not in one-off commands.** Every table a function reads or writes must be created by a `migrations/NNN-name.sql` file (idempotent `CREATE TABLE IF NOT EXISTS …`) that runs via a `database` deploy phase in `gipity.yaml` (see [deploy](deploy.md)). Creating tables imperatively with a bare `gipity db query "CREATE TABLE …"` works *in the moment* but the schema isn't captured anywhere: it doesn't ship with the app, a fresh deploy / prod / another user's install has no tables, and the signed-in features that write to them fail at runtime - which looks like broken auth, not a missing table.

Listing a table under a function's `tables:` in `gipity.yaml` (see [app-development](app-development.md#gipityyaml---function-permissions)) is a *permission* - it lets the function touch that table. It does not create the table; the migration does. Both are needed: the migration to make `searches` exist, and `tables: [searches]` to let the function use it.

## Database Helpers in Functions (via `db`)

```js
// Raw SQL with parameters
const { rows } = await db.query('SELECT * FROM orders WHERE user_id = $1', [userId]);

// Convenience methods
const user  = await db.findOne('users', { id: userId });
const items = await db.findMany('orders', {
  where: { status: 'pending' },
  orderBy: 'created_at DESC',
  limit: 10,
  offset: 0,
});
const insertedRow  = await db.insert('orders', { user_id: 1, total: 99.99 });  // the new row
const updatedCount = await db.update('orders', { id: orderId }, { status: 'shipped' });  // a number
const deletedCount = await db.delete('orders', { id: orderId });  // a number

// Column metadata for a declared table or view (works on empty tables too)
const cols = await db.describe('orders');  // [{ name, type, nullable, default }, …]
```

**`db` writes do NOT fire record.* workflow triggers.** `record.after_insert` /
`after_update` / `after_delete` workflows only fire on writes made through the
Records API (the `records_*` tools, the `/api/<appGuid>/records/<table>` REST
endpoints, workflow `record` steps). A raw `db.query('INSERT ...')` or
`db.insert(...)` in a function changes the data but never triggers the workflow.
If a write must trigger one (e.g. a contact form that emails the owner), route
that write through the Records API - see [workflow](workflow.md) "Record triggers".
For an **anonymous public form**, expose the table with a `records` phase in
`gipity.yaml` (`auth_level: public`, after the `database` phase) - that's the
native Records API and needs no kit. Do **not** `gipity add records` for this: the
records *kit* is signed-in member CRUD and can't take anonymous writes.

**Write-helper return shapes differ - don't guess.** `db.insert` returns the
**inserted row** (it appends `RETURNING *`), so read fields off it directly
(`insertedRow.id`). `db.update` and `db.delete` return the **affected row count**
as a plain number, *not* a row and not a result object - so `updated.id` is
`undefined` and `updated.rowCount` is `undefined`. To get updated rows back, use
`db.query('UPDATE ... RETURNING *', [...])` and read `rows`.

**`db.query` contract:** returns `{ rows, rowCount }`. `rowCount` follows node-pg
semantics - rows **affected** for `INSERT`/`UPDATE`/`DELETE`, rows **returned** for
`SELECT` - so a bare `DELETE ... WHERE ...` (no `RETURNING`) still reports the real
count. There is no separate `affectedRows` field. To get back inserted/updated
rows, add `RETURNING` and read `rows`.

**Passing arrays:** pass the array as a single parameter and match it in SQL with
`ANY($1)` (or `= ANY($1::text[])`) - do **not** expand it into `IN (...)` yourself:

```js
const ids = ['id_a', 'id_b', 'id_c'];
const { rows } = await db.query('SELECT * FROM orders WHERE id = ANY($1)', [ids]);
const del = await db.query('DELETE FROM orders WHERE id = ANY($1::text[])', [ids]);
// del.rowCount === number of rows deleted
```

**Upserts** work normally - `INSERT ... ON CONFLICT (id) DO UPDATE SET ...` is fine;
declare only the real table in `gipity.yaml` permissions.

**Transactions - `db.tx`.** Each `db.query` runs as its own statement on its own
connection; there is **no implicit transaction** per invocation. When two or more
writes must succeed or fail together (a data row plus its audit/outbox/ledger event),
wrap them in `db.tx` - the callback's queries run on one pinned connection inside
`BEGIN`/`COMMIT`; it commits when the callback resolves and rolls back (and rethrows)
when it throws:

```js
export default async function (ctx, { db }) {
  const record = await db.tx(async (tx) => {
    const row = await tx.insert('assets', { name: ctx.body.name, owner: ctx.auth.userGuid });
    await tx.insert('events', { entity_id: row.id, kind: 'asset.created', actor: ctx.auth.userGuid });
    return row;  // db.tx returns the callback's return value
  });
  return { data: record };
}
```

The `tx` handle has the same `query`/`findOne`/`findMany`/`insert`/`update`/`delete`
helpers as `db`. Table permissions and query/row limits apply unchanged inside the
transaction. One transaction at a time - `db.tx` cannot be nested. Raw
`db.query('BEGIN')`/`'COMMIT'` is rejected (it never worked - each query ran on its
own connection); `db.tx` is the only transaction API. If the function errors or
times out mid-transaction, the runtime rolls it back automatically.

**Limits (per invocation):** max_queries 50, max_rows_read 10,000, max_rows_affected 10,000. These are governor *defaults*, not Postgres limits - Postgres handles far larger statements fine. Bulk writes must be **set-based** (one `UPDATE … WHERE id = ANY($1)`), because a row-by-row loop hits max_queries (100) first; a set statement counts as 1 query and its rowCount counts against max_rows_affected.

**Raising a limit.** Add a `limits:` block to the function's `gipity.yaml` entry to lift any governor up to the platform ceiling - so you don't have to chunk or page prematurely:

```yaml
function_definitions:
  - name: bulk-move
    auth: user
    tables: [nodes]
    limits:
      max_rows_affected: 50000   # default 10,000, platform max 100,000
```

Ceilings: max_queries 100, max_rows_read 50,000, max_rows_affected 100,000 (other governors have their own caps). A value over the max fails the deploy with a clear message; an unknown/typo'd key is rejected, not silently dropped. For genuinely huge bulk (>100k rows in one shot) use a **Job** - it runs on a longer envelope built for batch work, not the 30s function window.
**Security:** Only declared tables are accessible. DDL (CREATE, ALTER, DROP) is blocked inside functions - schema changes always go through `migrations/` (the pattern above). If your app generates its schema (e.g. a metadata-driven data plane), have the agent generate `migrations/NNN-*.sql` files from your schema source and deploy; don't try to issue DDL from a function.

**SQL views work in `tables:`.** A view created in a migration (`CREATE OR REPLACE VIEW report_summary AS …`) can be listed in a function's `tables:` and queried with `db.query` like any table - useful for report/aggregation endpoints. Caveat: schema introspection tooling lists base tables only, so views won't appear in schema listings; the permission and queries still work.

**Globs in `tables:`.** A `tables:` entry may contain `*` (e.g. `tables: ['kit_*']`) to allow every matching table/view - opt-in per entry; entries without `*` stay exact matches. Use it for registry-driven functions that legitimately operate over a family of same-prefix tables, so each new object doesn't require editing every function's `tables:` list. Keep write-path functions on explicit lists unless they truly are generic.

**Schema introspection - `db.describe`.** `await db.describe('orders')` returns column metadata (shape shown in the helpers block above) for one declared table or view. It respects the function's `tables:` permission (globs included) and works on empty tables - use it instead of inferring columns from the first result row. `information_schema` itself is not queryable through `db.query`.

## Testing database code - the one isolation fact that bites

`gipity test` gives every test **file** its own throwaway copy of your database, reset before the file starts - see [app-testing](app-testing.md) for the harness. Nothing outside the file can touch it, so the only lifetime to know is inside it:

- **The reset is per FILE, not per test.** Rows inserted by earlier tests in the same file are still there when your test executes - an "insert three rows, assert the list returns exactly three" test fails if a sibling test in the same file already inserted one. Assert on rows you can identify (filter or count by your own marker), or put the setup in `setup(fn)`.