# Gipity Records - The Data Plane

Declare a Postgres table in the `records` deploy phase and the platform gives you, with no backend code: validated REST CRUD (+ bulk + aggregate + count), composite field types, full-text search, row/column RBAC, soft delete, auto-numbering, live SSE updates, `record.*` workflow triggers, an audit spine with actor provenance and English event summaries, agent CRUD tools, and auto-generated table/form UI components.

**One write path.** Every write - REST, agent tools, workflow `record` steps, bulk import - flows through the same validation + audit + event pipeline. A raw `db.query()` INSERT from a function bypasses ALL of it (no validation, no audit, no workflow trigger) - write through the Records API when any of those matter.

## Declaring tables (gipity.yaml)

```yaml
- name: records
  type: records          # AFTER the database phase - tables must exist
  tables:
    - table: deals
      auth_level: user   # public (anon read AND write) | user | member (default); anyone can write a public table, so use user/member for owner-only data
      searchable: true   # full-text search over text columns (q= param)
      fields:
        - { name: name, type: text, required: true, title: true }
        - { name: stage, type: select, options: { values: [Lead, Proposal, Won, Lost] } }
        - { name: value, type: currency, label: Deal value }
        - { name: owner_id, type: relation, label: Owner, options: { table: people, display: full_name } }
        - { name: notes, type: textarea, section: Details, help: Internal notes }
```

Field metadata is optional per column - declared fields are coerced + validated on every write with **self-correcting errors** (`'stage' must be one of: Lead, Proposal, Won, Lost (got 'Wonn')`); undeclared columns pass through and Postgres validates them.

**Types:** `text`, `textarea`, `number`, `boolean`, `date` (YYYY-MM-DD), `select`, `tags` (list of short values on a plain TEXT column - send `["party","co-op"]` or a string, stored/returned as `"party, co-op"`, so `ilike` filters, `q=` search and sort all just work; `options: {values}` restricts them), `json` (objects only), and composites stored as JSONB in your column: `currency` `{amountMicros, currencyCode}` (accepts a plain number), `name` `{firstName, lastName}` (accepts "Ada Lovelace"), `emails`/`phones`/`links` (accept a bare string), `address`. `relation` describes an existing FK column (`options: {table, display}`); the value stays the raw id and Postgres enforces integrity.

**Per-field keys:** `label`, `required` (enforced on create), `title` (names the record in audit summaries), `hide`/`section`/`help` (drive generated UIs; order = declaration order).

**Postgres VIEWs** can be declared too - they get the full read API (list/filter/sort/aggregate) and render in the UI components read-only. Make demo/public views non-updatable (e.g. `SELECT DISTINCT`): a simple single-table view is auto-updatable, and a `public` entry on one would accept anonymous writes into the base table.

## Table schema (the migration side)

The table itself is a plain `migrations/NNN-*.sql` `CREATE TABLE`; Records layers onto whatever columns it finds. Only three columns are special, all optional:

- **Primary key** - defaults to `id`. If yours is named differently (e.g. a `short_guid` PK), add `primary_key: <col>` to the table entry. `/history`, `GET /records/<t>/<id>`, and SSE all key off it.
- **`updated_at`** - if the table has an `updated_at TIMESTAMPTZ` column, **Records bumps it automatically on every write** (create/update/restore). Do **not** write a trigger for it: migrations run on a managed database that blocks `CREATE FUNCTION`/`CREATE TRIGGER`, so the textbook `updated_at` trigger fails the deploy. Pair it with `created_at TIMESTAMPTZ DEFAULT NOW()` for sortable timestamps with zero trigger code. (For any other derived column, use a Postgres `GENERATED` column or compute it in a function - never a trigger.)
- **Soft delete** - add a nullable `deleted_at TIMESTAMPTZ` column and declare `soft_delete_column: deleted_at`; `DELETE` then stamps it instead of removing the row, and the recycle bin (`?only_deleted=1`, `restore`) works. Omit both to hard-delete.

```sql
CREATE TABLE IF NOT EXISTS deals (
  id          BIGSERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  stage       TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ,              -- auto-bumped by Records; no trigger
  deleted_at  TIMESTAMPTZ               -- soft_delete_column: deleted_at
);
```

## REST API

Base: `https://a.gipity.ai/api/<PROJECT_GUID>` - auth via `X-App-Token` (+ session cookie for user/member), or `X-Api-Key`.

**From the browser, don't call these by hand** - `gipity.js` mints the token for you and `Gipity.records.*` mirrors the endpoints below one-for-one, returning the unwrapped `data`: `list(t, params) → {rows, total}` · `get(t, id)` · `create(t, data)` · `update(t, id, data)` · `remove(t, id, {purge})` · `restore(t, id)` · `count(t, filter)` · `aggregate(t, params)` · `schema(t)` · `history(t, id)` · `tableHistory(t)` · `createMany(t, rows)` · `stream(t, {onChange, filter}) → {stop()}` · `views.list/save/update/remove`. `params` are the query params below (`filter`, `any`, `sort`, `limit`, `offset`, `fields`, `q`, `include_deleted`, `only_deleted`).

- `GET /records/<t>?filter=&any=&sort=&limit=&offset=&fields=&q=` → `{data, meta:{total}}`. Filter ops: `eq,neq,gt,gte,lt,lte,like,ilike,in,is_null,not_null`; `in` is pipe-separated (`id:in:1|2|3`); filters comma-join as AND. `like`/`ilike` take a raw SQL pattern - YOU supply the `%` (`name:ilike:%ada%`; no `%` = exact match); for substring/word search use `q=` instead. `any=` is an OR group with the same grammar, ANDed with `filter` (`any=owner:eq:me,shared:eq:true` = at least one matches).
- `GET /records/<t>/<id>` · `POST /records/<t>` · `PUT /records/<t>/<id>` · `DELETE /records/<t>/<id>` (soft delete when the table declares `soft_delete_column`; `?purge=1` hard-deletes the row AND erases its history - owner/editor, use it to scrub probe rows)
- **Linked fields** (hop one relation deep, from the same metadata): project parent columns (`fields=name,company.name` → flat dotted keys in the JSON), filter (`filter=company.industry:eq:software`, works in `any=` too), sort (`sort=company.name:asc`), and aggregate (`group_by=company.industry` - instant reports). The prefix is the relation field minus `_id` (or the field name itself). Missing and soft-deleted parents come back NULL; the target table's auth level and RBAC still apply (row policies on the target block the hop with a clear error); errors name the available relations and columns so callers self-correct. Not available on SSE streams (linked-field conditions are ignored there).
- Saved views (platform data, sign-in required): `GET|POST /records/<t>/views`, `PUT|DELETE /records/<t>/views/<id>` - named, shareable view configs (`{name, view_type, config, shared}`; the config blob is the UI's view state). Everyone saves private views; sharing needs owner/editor. SDK: `Gipity.records.views.list/save/update/remove`.
- Recycle bin (owner/editor only): `?only_deleted=1` lists deleted rows, `?include_deleted=1` includes them (list/count/get), `POST /records/<t>/<id>/restore` un-deletes with a `restore` audit event.
- `POST|PUT|DELETE /records/<t>/bulk` (arrays; `?source=import` marks writes IMPORT for CSV import tooling)
- `GET /records/<t>/count?filter=` · `GET /records/<t>/aggregate?group_by=&aggregate=count,sum&column=_,value`
- `GET /records/<t>/<id>/history` - audit events: `source` + `detail.summary` (below) · `.../comments`
- `GET /records/<t>/stream` - SSE (`record.created/updated/deleted`)
- `GET /schema/<t>` - live columns merged with the records config + field metadata: the one contract UI generators consume

## Provenance + English audit history

Every write records WHO (user), and HOW: `source` = `MANUAL` (signed-in user) / `API` (API key, attributed by key name) / `AGENT` (agent tools, attributed by agent name) / `WORKFLOW` (record steps, attributed by workflow name) / `FORM` (anonymous public-table write) / `IMPORT` (bulk with `?source=import`). Each event carries a one-line English summary derived from field labels - `deal-won-followup created deal task "Follow up: Acme won"` - so `/history` is prompt-ready: an AI brief over a record (or a whole table) is one LLM call over the event stream, not a RAG project. CLI: `gipity records history <table> [id]` (omit the id for the table-wide feed). History payloads are de-identified (a display-name `actor`, never DB ids); the whole-table feed is owner/editor only (per-record history follows the table's read auth).

## Workflows

`record.after_insert/update/delete` triggers fire on Records API writes only. A workflow `record` step writes through the same path, so its writes are validated, audited (WORKFLOW provenance), and can chain further triggers (depth-capped). See [workflow](workflow.md).

## From the CLI

`gipity records list` / `query <table>` / `get <table> <id>` / `create <table>` / `update <table> <id>` / `delete <table> <id>` / `restore <table> <id>` / `history <table> [id]` - same write path as the REST API, with provenance and self-correcting errors. `gipity records config <table> --auth <level>` changes a table's exposure without a redeploy.

## Auto-generated UI (Gipity Views)

Load after the client SDK and get ServiceNow-style list + form for free:

```html
<script defer src="https://media.gipity.ai/client/v1/gipity.js" data-app="{{PROJECT_GUID}}"></script>
<script defer src="https://media.gipity.ai/views/v1/gipity-views.js"></script>

<gipity-records table="deals" live></gipity-records>
```

`<gipity-records>` = table + "New" button + slide-over form. Or compose `<gipity-table>` (sort, per-column filter chips, search, pagination, CSV export, SSE live updates, composite-aware cells, relation display + typeahead) and `<gipity-record-form>` (type-appropriate widgets, sections, inline self-correcting validation errors, history pane with provenance) yourself. Columns and widgets come from the field metadata, with live-introspection fallback for undeclared tables. Theme with `--gv-*` CSS variables.

**Drag-and-drop board:** `<gipity-kanban table="deals" group-by="stage" metric="amount" live>` - one column per value of a declared `select` field, cards drag between them, and the drop is a normal validated + audited write that fires `record.after_update`. Same `filter`/`sort` attributes as the table, so "flip this list into a board" is a second element, never hand-written drag code. Add an integer `position` column to persist manual card order. `<gipity-saved-views table="deals" target="deals-list">` saves and re-applies any of these components' filter+sort state as platform data (no app table).

## Common mistakes

- Writing with `db.query()` and expecting validation/audit/triggers - only Records API writes get them.
- Declaring the `records` phase before the `database` phase (table doesn't exist yet → skipped with a warning).
- Composite columns not JSONB - `currency`/`name`/`emails`/... need a JSONB column.
- `id:in:1,2,3` - commas separate filters; `in` values are pipe-separated: `id:in:1|2|3`.
- Expecting record triggers to fire from `gipity test` writes - test-schema writes are deliberately skipped.