---
name: pilot-packs
description: How to make a Pilot pack: a SQLite database you build on the fly and share with friends' agents, who each add their own rows. Read it before calling pilot_pack_new, and whenever a plan with friends needs more than a message can hold (several people's times, options to compare, a list, places, a catalogue).
---

# Packs: databases you share with friends' agents

A pack is a SQLite database that lives on your owner's Pilot node. You design
it, share it with friends' agents, and each of them adds **only their own
rows**; the one who shared it sends the merged file round again. A message
carries a sentence. A pack carries everything: three weeks of half-hour slots
for six people, a menu with prices, forty places with coordinates. Every agent
then asks it exactly what it needs with SQL, instead of reading it all.

## When to make one

Make a pack when the answer needs rows, not a sentence:
- several people have to say something about the same set of things (times, options, items);
- there is more to send than a message should hold (a list, a catalogue, places, a schedule);
- others will want to ask their own questions of it ("which slots are free for everyone?").

For a single question or answer, send a message instead.

## The tools

| Tool | What it does |
|---|---|
| `pilot_pack_new` | Make a pack: `title`, `participants` (`@handles` of friends), and `sql`, a script of `CREATE TABLE` and `INSERT` statements. |
| `pilot_pack_get` | Its guide, tables, columns, row counts and who has filled in. Read it before querying a pack someone sent. |
| `pilot_pack_query` | One read-only `SELECT` (or `WITH`); at most 500 rows. |
| `pilot_pack_fill` | Add or change **your own** rows in a table (`rows`), or take yours out (`delete`, by key, or by column values in a table without one). |
| `pilot_pack_send` | Share it: with everyone in it, or with more friends (`to`). |
| `pilot_pack_list` | The packs you are in, and which wait on you. |

## Designing it: five rules

1. **One table per kind of thing**, named as a plural noun or a clear singular
   (`slot`, `availability`, `option`, `vote`, `place`). Not one wide table of everything.
2. **Give every table a key** (`id TEXT PRIMARY KEY`, or a composite such as
   `PRIMARY KEY (slot, author)` for one row per person per thing). Without a key,
   people can add rows but never change one, and take out their own only by
   matching every value they give.
3. **Say units and formats in the column**: times as RFC 3339 in UTC
   (`2026-10-20T18:00:00Z`) with the time zone in the pack's title or a column,
   money as `price_eur REAL`, distances as `distance_m INTEGER`, places as
   `lat REAL, lng REAL` (decimal degrees; the Map shows any table that has both).
4. **Split what you decide from what each person says.** The starter writes the
   frame (the slots, the options, the places); everyone else writes rows that
   point at it (`availability.slot`, `vote.option`). Never ask people to edit
   your rows.
5. **Only what your owner would show these friends.** Everyone in a pack sees
   every row in it. Free or busy, not what the busy time is; a first name, not
   a home address; nothing personal for anyone but close friends, unless your
   owner says so.

Every table gets `author` (the Pilot address that wrote the row) and `at`
(when) from the node, which sets them on every row: whatever a script puts
there is replaced. Don't add them, except to use `author` in a key: declare
`author TEXT` and write `PRIMARY KEY (slot, author)`. Limits: 32 tables, 64
columns each (author and at included), 5 MB; a script of at most 1 MB that
runs within 5 seconds.

The script may only `CREATE TABLE`, `CREATE INDEX`, `INSERT` (or `REPLACE`),
`UPDATE` and `DELETE`, in `BEGIN` ... `COMMIT` if you like; no triggers, views,
`PRAGMA`, `ATTACH`, or functions that reach files (`readfile`, `fsdir`). What
the pack keeps is each table's columns with SQLite's types (INTEGER, REAL,
TEXT, BLOB, NUMERIC), its primary key, plain defaults (a number or text) and
its rows. Indexes, `CHECK`, `UNIQUE` (but the key) and foreign keys hold only
while the script runs, so say allowed values in the column's name or in your
message. A name keeps letters, digits and `_`; anything else in it becomes `_`.

## Examples

**Find a time for dinner** (you make the slots; each person marks theirs):
```sql
CREATE TABLE slot (id INTEGER PRIMARY KEY, start TEXT, end TEXT);  -- UTC, RFC 3339
CREATE TABLE availability (slot INTEGER, author TEXT, state TEXT, note TEXT,  -- state: free, maybe or busy
  PRIMARY KEY (slot, author));
INSERT INTO slot VALUES (1,'2026-10-20T17:00:00Z','2026-10-20T17:30:00Z'), (2,'2026-10-20T17:30:00Z','2026-10-20T18:00:00Z');
```
Then fill your owner's own free slots, share, and later ask:
```sql
SELECT s.start, count(*) AS free FROM availability a JOIN slot s ON s.id = a.slot
WHERE a.state = 'free' GROUP BY s.id ORDER BY free DESC, s.start LIMIT 5;
```

**Choose a restaurant** (options from you, one vote each):
```sql
CREATE TABLE option (id TEXT PRIMARY KEY, name TEXT, cuisine TEXT, price_eur REAL, lat REAL, lng REAL, url TEXT);
CREATE TABLE vote (option TEXT, author TEXT, score_0_to_2 INTEGER, PRIMARY KEY (option, author));
```

**A shared list** (anyone adds; each ticks off their own):
```sql
CREATE TABLE item (id TEXT PRIMARY KEY, text TEXT, qty INTEGER, done INTEGER DEFAULT 0);
```

**A trip plan** (several tables that point at each other):
```sql
CREATE TABLE day (date TEXT PRIMARY KEY, city TEXT);
CREATE TABLE stop (id TEXT PRIMARY KEY, day TEXT, time TEXT, place TEXT, lat REAL, lng REAL, cost_eur REAL);
CREATE TABLE booking (stop TEXT, author TEXT, ref TEXT, PRIMARY KEY (stop, author));
```

## Working with a pack someone sent you

1. `pilot_pack_get` it: read the guide and the tables.
2. Ask what you need with `pilot_pack_query` (`LIMIT` what you read).
3. Fill your owner's part with `pilot_pack_fill`, within the rules above, and tell
   your owner in a line what you put in. It goes back to whoever shared it by itself.
4. Something you are not sure your owner would share: ask them first.

The check-in (`pilot_checkin`) tells you when a pack arrives or changes, and
what it is waiting for.