The SQL projection

The .agi tree is the system of record. It is also a terrible query engine. "Show me all unpaid invoices over $1,000 from the last 30 days" against a tree of text files is grep -r followed by hand-parsing, and it scales like grep -r scales — that is, badly.

The projection exists exactly for this. It is a SQL read-model rebuildable from the tree at any time.

The split

WRITE PATH:
  agent → tool → GitStore.create() → backend.write() → backend.commit()
                                  └────→ projection.upsert()

READ PATH:
  query → projection.query() → SQL → indexed rows

Every write writes twice: once to the canonical tree (the slow but durable record), once to the projection (the fast but derivable read-model). If the projection ever falls out of sync, you rebuild it from the tree.

Rebuilds are first-class

await projection.rebuild(INVOICE_ENTITY, store, ["acme", "globex"]);

That call truncates the Invoice table and walks the tree, upserting each .agi record back into D1. There is no migration script. The .agi is the migration script. If the projection schema changes, you rebuild.

The Worker exposes this directly:

POST /admin/rebuild  { "tenants": ["acme"] }

For a tenant with 10,000 invoices this takes seconds. For a tenant with 10 million it takes longer — at which point you split the rebuild by entity, run during a maintenance window, and the audit log records exactly what happened.

The schema is the .agi too

The projection's DDL is derived from the EntitySpec:

async ensureTable(spec: EntitySpec): Promise<void> {
  const cols = [
    "id TEXT PRIMARY KEY",
    ...spec.fields.map((f) => `${f.name} ${SQL_TYPE[f.type]}${f.required ? " NOT NULL" : ""}`),
  ];
  await this.backend.exec(`CREATE TABLE IF NOT EXISTS "${spec.name}" (${cols.join(", ")})`);
  await this.backend.exec(`CREATE INDEX IF NOT EXISTS idx_${spec.name}_tenant ON "${spec.name}"(tenant_id)`);
}

Adding a field to an ENTITY block in schema.ts automatically becomes a column the next time ensureTable runs. Removing one... well, that needs a real migration. See the roadmap.

The backend pair

Same interface, two implementations:

| Concern | Local dev (Node) | Production (Workers) | | ---------- | ----------------------------- | ----------------------- | | Storage | NodeFsBackend (fs+isogit) | R2Backend (R2 bucket) | | Projection | SqliteBackend (WAL) | D1Backend (D1) |

SqliteBackend wraps better-sqlite3 with a thin promise shim. D1Backend wraps the Cloudflare D1 binding. Both speak the same ProjectionBackend interface, which is just three methods:

interface ProjectionBackend {
  exec(sql: string): Promise<void>;
  run(sql: string, params: readonly unknown[]): Promise<void>;
  all<T>(sql: string, params: readonly unknown[]): Promise<T[]>;
}

That's the entire surface area between the projection layer and the database. Add a fourth runtime by writing a thin shim against this interface.

Joins for legibility

list_invoices doesn't just SELECT * FROM Invoice. It LEFT JOIN Customer and surfaces customer_name, so the agent doesn't have to call list_customers separately just to humanize a list. The LEFT JOIN survives a deleted customer with customer_name = null — the UI falls back to a short id.

Same treatment for list_tickets. list_line_items is left alone — the caller already supplies invoice_id so the customer context is implicit.

Aggregations live here too

The three "what does my business look like right now" tools — total_outstanding, revenue_by_month, tickets_by_priority — are pure SQL against the projection. SQLite's strftime, CASE in ORDER BY, and GROUP BY are all that's needed.

SELECT
  strftime('%Y-%m', paid_date) AS month,
  COUNT(*) AS invoice_count,
  COALESCE(SUM(total), 0) AS revenue
FROM Invoice
WHERE tenant_id = ? AND status = 'paid' AND paid_date IS NOT NULL
GROUP BY month
ORDER BY month DESC
LIMIT ?

The agent calls revenue_by_month. The Worker runs that query. The model gets back a tight array of { month, invoice_count, revenue } objects to summarize. No record-by-record loop. No model arithmetic.