# Build guide

> The instructions pebblebase gives every AI that builds a pebble, word for word.

pebblebase sends these instructions to every AI that connects, and returns the full guide when it creates a pebble. This page is generated from the server, word for word.

```text
pebblebase keeps the user's tools as pebbles: one SQLite database each, holding data, behavior, tests and notes. Start with list_databases, then read the pebble's pebblebase_notes; if it is empty, look for an older notes table. Each execute_sql call commits all or nothing: report success only after it does, and after a lost response, read before retrying a write. Ask the user rather than guess a value they didn't give.

To build:
1. Plan: write the request as pebblebase_tests rows first, including inputs that must fail. Never weaken or delete a test to pass.
2. Build: a model is a table; its columns and CHECKs make the form show_form shows (CHECK (x IN (...)) for choices). Derived data goes in views. Reactions are few, small AFTER triggers; user messages go in pebblebase_outbox(body). Add a method (<model>_<verb> view, INSTEAD OF trigger) only when more than an insert happens. Decide and test edits, deletes and backfills: UPDATE doesn't fire insert triggers.
3. Verify: run_tests until it passes; fix its warnings or tell the user why not. Then schema changes that break a passing test roll back.

Time: ask the user's IANA zone once and store it: INSERT INTO pebblebase_settings VALUES ('time_zone', 'America/Chicago'). 'localtime' then uses it. Store UTC times, plus a local day if days matter: DEFAULT (date('now', 'localtime')).

pebblebase creates pebblebase_tests, pebblebase_settings, pebblebase_outbox, pebblebase_notes(name, instructions), pebblebase_views(name, kind, query), pebblebase_schedules(name, cron, body, form, prefill) and pebblebase_fields; write rows, not tables. query_sql only reads. show_view shows a view row. Crons are local time. Schedules and outbox rows reach the user's phone as pebblebase app pushes; reminders set elsewhere don't. pebblebase_feeds is reserved.

Keep pebblebase_notes current: purpose, how to log, read, fix and backfill, and allowed values. Move older notes in only with the user's consent. export_database gives a download link.

Feedback: when the user asks, or once on friction with pebblebase, draft it, show it, then give a mailto:support@pebblebase.dev link with the subject and body URL-encoded, plus the text to copy. pebblebase can't reply in chat; it answers by email.

Schema: forms and views are read from it.
- REFERENCES other(id) makes a picker and a record link; give linked tables a record_label.
- CHECK (x IS date(x)) makes a date picker, CHECK (x IS datetime(x)) a date-time one (UTC); a DATE type alone doesn't.
- CHECK (x IN (0, 1)) makes yes/no; CHECK (x IN (...)) makes choices.
- NOT NULL without a default is required; defaults show filled in.
- To add REFERENCES or a CHECK, rebuild the table in one call; ask first if it holds data.
- Labels are part of a table: label each table and column whose name reads poorly (guest_uuid, rate_usd, qty). Reference a single id key, not a composite one.
pebblebase_fields says what the schema can't:
- One row per table (column_name '') or column, of a table or view.
- label names it; a table row adds description and record_label, how its rows read: '{name} · {city}'.
- help: a hint under the field.
- format: 'money' (12.5) or 'cents' (1250), with currency such as 'USD'; 'duration': whole seconds. An SQL view's column takes it whatever its type.
- long_text = 1 makes a text area; hide_in_form = 1 hides an optional column.
- choice_labels, choice_tones: JSON objects keyed by the CHECK's values, or any keys on an SQL view's column, in lane order; tones neutral, accent, muted or a colour.
- kind: 'date' or 'date-time' (UTC) for a text column without a date check, such as one a trigger checks; 'yes-no' for an SQL view's 0/1 column.
- allow_edit, allow_delete = 1 on a table row add Edit and Delete to its records: test UPDATE and DELETE first, and keep update rows where history matters.
Example: INSERT INTO pebblebase_fields (table_name, column_name, format, currency) VALUES ('stays', 'rate', 'cents', 'USD').
View settings: pebblebase_views also has settings (JSON or null) and position (nav order).
- title: heading and navigation name.
- table: the table or view the rows come from, for labels, kinds and links. Set it.
- columns: ["day", "mood"], shown in order.
- sort: {"column": "day", "direction": "desc"}, the first order.
- search: up to 10 columns the search box looks in.
- filters: up to 3 columns that get a chip per value.
- group_by: a column whose values head groups (a board's lanes).
- page_size: rows before Show more, 10 to 200 (50).
- empty: {"text": "None yet.", "action": {"label": "Add", "form": "checkins"}}.
- row_actions: up to 3 per row; from_row maps form to row columns: [{"label": "Correct", "form": "corrections", "from_row": {"stay_id": "id"}}]. Map keys, not long text.
- header_actions: up to 3 by the heading. Each is {label, form, prefill}; prefill holds literal values. Actions only open a create form.
- row_link: rows open their record when the query returns the key of table, else of just one table it reads; false: off; {"table": "stays", "column": "stay_id"}: another table's.
- fields: per result column, this view only, over pebblebase_fields: label, format, currency, kind and choice_labels or choice_tones (any keys): {"Still due": {"format": "cents", "currency": "USD"}}.
- type: a chart's "line" (default) or "bar".
- date: a calendar's day column, a date or UTC date-time.
- end: a calendar's inclusive last-day column.
- span: "week" draws a calendar's week, "month" (the default) its month.
Every kind takes title, table, header_actions and fields; table, list and board take the rest; stat and record take columns; chart takes empty; calendar takes columns, empty and row_link.
Example: INSERT INTO pebblebase_views (name, kind, query, settings, position) VALUES ('week', 'table', 'SELECT * FROM checkins', '{"table": "checkins"}', 1).
Views: kind says how show_view draws the query's rows.
- table: any rows, as a table.
- list: any rows; the first column is the item, the rest details.
- stat: one row; each column is one value.
- chart: a label column, then number or null columns, as lines or bars.
- streak: one row: the current run, then optionally the best, whole numbers of 0 or more.
- record: at most one row, with the rows linking to it; show_view with name=<table> and key=<row key> shows any row so.
- board: cards in lanes by group_by (required), in its CHECK (x IN (...)) order with table set.
- calendar: a month or week grid of rows by date (required), through end.
Forms: show_form draws a field per column, by kind:
- A form adds one row per Submit; it skips automatic keys, generated columns and optional hide_in_form ones.
- text: any other column.
- long-text: text with long_text = 1.
- number: INTEGER (whole), REAL, NUMERIC or DECIMAL.
- money: a number column with a format.
- duration: an integer column with format 'duration'; whole seconds as hours and minutes.
- date: a date() check, or kind 'date' on a text column; local days, YYYY-MM-DD.
- date-time: a datetime() check, or kind 'date-time'; UTC, shown in the pebble's zone.
- yes-no: CHECK (x IN (0, 1)).
- choice: CHECK (x IN (...)), up to 50 strings or integers.
- reference: REFERENCES; picks from the newest 200 rows by record_label.

pebblebase app:
- dashboard: 1 to 99 puts a table, list, board, stat, streak or chart on its pebble's Home and the Dashboard, shared by all pebbles: the 12 lowest show; 1 is for the one view the user checks most; one per pebble unless central; at most 3 per pebble.
- nav: false keeps a view off navigation, the Dashboard and Home; links and show_view still reach it. Not with dashboard or home.
- Colour: INSERT INTO pebblebase_settings VALUES ('color', 'teal'): red, orange, green, teal, blue, purple, pink, stone; else from the name.
- Description: ('description', 'Rooms, stays and guests'): one line of at most 60 characters, shown with its name.
- choice_tones may be a colour or yellow.
- When the first pebble works, tell the user once: on a new device or if they lose their passkey, ask this chat for a sign-in link (create_sign_in_link); pebblebase on their phone (app.pebblebase.dev) is a second way in.
```