Choose a store¶

One SQLite file per workspace, at workspaces/<task>/data/dataset.db. It holds pointers and JSON, never bytes: an image stays where it is, a mask is a PNG under data/interim/ and the row records its URI. A database that swallows media stops being copyable, diffable, or cheap to open, and the media is already on disk.

Five tables carry every task family:

Table	One row per	Notes
`meta`	setting	task name, `schema_version`, `coordinate_convention`
`items`	source file	`uri` is unique; local path or fsspec URL
`runs`	pipeline execution	model, `prompt_sha256`, config, timestamps
`annotations`	label unit	`kind` ∈ bbox, polygon, keypoints, rbox, caption, tag, mask, segment
`reviews`	second-pass verdict	accept / reject / fix

One annotations table keyed by kind rather than a table per kind is what makes the rest cheap: one write contract, one export path, and one uniqueness rule — (item_id, run_id, kind, key) — that holds for a caption variant and a box index alike. payload_json carries the kind-specific shape ({"bbox": [x0, y0, x1, y1]}, {"text": …, "variant": …}, {"points": …}), always in normalized 0–1 coordinates relative to the uncropped source.

Runs, not overwrites¶

Every annotation carries a run_id, so a second pass never destroys the first. The final_annotations view resolves this per item and kind: for each pair it takes the rows of the latest run that produced a final row of that kind. A caption written in run 1 survives a boxes-only run 2. items_pending is its complement — items with nothing final yet — and is what a pipeline iterates, which makes re-running after a partial failure the same command as the first run.

Nothing is deleted. A bad annotation is marked rejected; a doubtful one needs_review, which keeps it out of final_annotations and therefore keeps its item pending.

What the execution agent gets¶

Three tools, no SQL: record_annotation (upsert on the uniqueness key, payload shape validated per kind, run_id fixed by the pipeline and never chosen by the agent), update_annotation (current run only; final → draft is refused), and mark_reviewed (writes a reviews row; accept promotes a needs_review row back to final). The SDK wiring for them is step 5; the full contract is in the skill's references/tool-contract.md.

Two operational details bite everyone once: PRAGMA foreign_keys = ON is connection-scoped, so every connection has to set it, and INSERT OR REPLACE changes rowids, which silently breaks the reviews references — use ON CONFLICT DO UPDATE. The shipped scripts do both.

The schema¶

python scripts/init_db.py --db workspaces/<task>/data/dataset.db --task detection applies it, sets WAL mode, records the conventions in meta, and adds a task_annotations view for the kinds that belong to the chosen task. It is idempotent.

skills/sqlite-annotation-store/assets/schema.sql

-- annotools annotation store, schema version 1. Conventions: normalized 0-1 coordinates relative to the
-- uncropped source; file pointers only (never binary data).
PRAGMA journal_mode = WAL;
-- foreign_keys is connection-scoped: every client must run `PRAGMA foreign_keys = ON` after connecting.

CREATE TABLE IF NOT EXISTS meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS items (
    id         INTEGER PRIMARY KEY,
    uri        TEXT NOT NULL UNIQUE,          -- local path or fsspec URL
    media_type TEXT NOT NULL CHECK (media_type IN ('image','video','audio')),
    width      INTEGER,
    height     INTEGER,
    duration   REAL,                          -- seconds, for video/audio
    split      TEXT NOT NULL DEFAULT 'train' CHECK (split IN ('train','val','test','unsplit')),
    meta_json  TEXT DEFAULT '{}',
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TABLE IF NOT EXISTS runs (
    id            INTEGER PRIMARY KEY,
    model         TEXT NOT NULL,
    prompt_sha256 TEXT NOT NULL,
    config_json   TEXT DEFAULT '{}',
    started_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    finished_at   TEXT
);

CREATE TABLE IF NOT EXISTS annotations (
    id           INTEGER PRIMARY KEY,
    item_id      INTEGER NOT NULL REFERENCES items(id),
    run_id       INTEGER NOT NULL REFERENCES runs(id),
    kind         TEXT NOT NULL CHECK (kind IN ('bbox','polygon','keypoints','rbox','caption','tag','mask','segment')),
    key          TEXT NOT NULL DEFAULT '',    -- disambiguates several annotations of one kind (index, variant)
    label        TEXT,
    payload_json TEXT NOT NULL,               -- shape depends on kind; see SKILL.md
    confidence   REAL,
    rounds       INTEGER DEFAULT 0,           -- correction rounds used before commit
    status       TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','final','needs_review','rejected')),
    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    UNIQUE (item_id, run_id, kind, key)
);
CREATE INDEX IF NOT EXISTS idx_annotations_item_run_kind ON annotations(item_id, run_id, kind);

CREATE TABLE IF NOT EXISTS reviews (
    id            INTEGER PRIMARY KEY,
    annotation_id INTEGER NOT NULL REFERENCES annotations(id),
    reviewer      TEXT NOT NULL,
    verdict       TEXT NOT NULL CHECK (verdict IN ('accept','reject','fix')),
    note          TEXT,
    created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TRIGGER IF NOT EXISTS annotations_touch AFTER UPDATE ON annotations
BEGIN
    UPDATE annotations SET updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now') WHERE id = NEW.id;
END;

-- Final annotations: for each (item, kind) the rows of the latest run that produced a final row of that
-- kind, so a caption from run 1 survives a bbox-only run 2.
CREATE VIEW IF NOT EXISTS final_annotations AS
SELECT a.*
FROM annotations a
JOIN (
    SELECT item_id, kind, MAX(run_id) AS run_id
    FROM annotations
    WHERE status = 'final'
    GROUP BY item_id, kind
) latest ON latest.item_id = a.item_id AND latest.kind = a.kind AND latest.run_id = a.run_id
WHERE a.status = 'final';

CREATE VIEW IF NOT EXISTS items_pending AS
SELECT i.*
FROM items i
LEFT JOIN final_annotations f ON f.item_id = i.id
WHERE f.id IS NULL;

The schema is plain SQL with JSON text columns, so PostgreSQL or DuckDB run it with small edits if a project refuses SQLite; keep the tables, the uniqueness key, and the final_annotations semantics and the tool contract and export still apply.

Next: fit the model's token budget before the first item is sent anywhere.

Source: skills/sqlite-annotation-store