# 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
- `db_manage` - create or drop databases
- `db_list` - list existing databases
- `db_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 `credits_products` to see), 500 rows / 128 KB per query, 50,000 chars per statement.

Destructive operations (DROP, TRUNCATE) are auto-confirmed by the platform - just call db_sql directly.

**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 `db_sql` / `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 two isolation facts that bite

`gipity test` runs your functions against an **isolated, throwaway copy of your database** - see [app-testing](app-testing.md) for the harness. Two lifetimes trip up naturally-written tests, so know them before writing your first assertion:

- **The test database resets once per RUN, not per test.** Rows inserted by earlier tests in the same run are still there when your test executes - an "insert three rows, assert the list returns exactly three" test fails on its first run with someone else's rows in the diff. Assert on rows you can identify (filter or count by your own marker), not on the table's total contents.
- **`ctx.testId` is stable per FILE, not per test.** It exists to keep *files* from colliding on unique names; every test in one file shares the same id, so it cannot isolate tests from their same-file siblings. Namespace per-test data with your own suffix on top of it when a test needs exclusivity.