Recipes

Self-contained, runnable scenarios. Every recipe leads with the SQL and shows how to send it from psql, psycopg or the dodil data CLI.

Recipe	Key surface	When you need it
Double-entry ledger	`NUMERIC(18,2)` · `CHECK` · `REFERENCES` · `SELECT … FOR UPDATE` · retry on `40001` · `ADD IMMUTABLE`	Money or anything else that must never be created, lost or edited after the books close.
Idempotent writes & retries	PK as idempotency key · `ON CONFLICT DO NOTHING` / `DO UPDATE` · `RETURNING` · which SQLSTATEs to retry	Webhooks, queue consumers, any handler that may run twice.
Manual table	`CREATE TABLE` · `INSERT` / `MERGE` / `UPDATE` / `DELETE` · `SELECT` · `compact` · `optimize`	You know the schema and push the data yourself. The SQL-first workflow end to end.
Pipeline-bound table	`CreateTablePipeline` · ingest rules · `SELECT`	Unstructured documents land in the bucket and rows should appear on their own.
Time travel & restore	`RESTORE TABLE … TO VERSION AS OF` · `vacuum`	Recover from a bad write, and know how the retention window bounds recovery.
CTAS & materialize	`CREATE TABLE … AS SELECT` · `INSERT … SELECT` · `CREATE OR REPLACE TABLE`	Derive tables from queries — in-database ETL.

Conventions

Postgres wire first — pg.uk-lon-1.dodil.io:5432, sslmode=require, dbname = the bare bucket name. It is the only path with prepared statements, COPY FROM STDIN, cursors, savepoints and statement_timeout. Endpoints and credentials: Connect.
dodil data sql sends the same SQL over gRPC; dodil data pg over the Postgres wire. The control-plane API (api.data.dodil.io) serves no table data.
Bucket placeholder is kb-prod. Tables are implicit per bucket; there is nothing to enable.
Example schema — most recipes use an events table with composite primary key (id, user_id), partition column event_type and a JSON payload; the ledger recipe has its own.