Database Schema & Provisioning API

[jrf0110]

Part of #271 — Gastown Cloud Proposal A (Sandbox-per-Town)

Goal

Create the Postgres schema for tracking gastown towns/rigs and the tRPC API for provisioning Fly.io sandboxes. Follows KiloClaw's instance-registry.ts patterns.

Requirements

Database Schema

gastown_towns table:

Column	Type	Notes
id	UUID PK	gen_random_uuid()
user_id	TEXT NOT NULL	FK → kilocode_users(id)
town_name	TEXT NOT NULL	User-chosen name
sandbox_id	TEXT NOT NULL UNIQUE	Deterministic from userId + townName
fly_machine_id	TEXT	Set after Fly provisioning
fly_volume_id	TEXT	Set after Fly provisioning
fly_region	TEXT DEFAULT 'iad'
status	TEXT NOT NULL DEFAULT 'provisioning'	provisioning | running | stopped | destroyed
last_r2_sync_at	TIMESTAMPTZ	Updated by sandbox heartbeat
last_heartbeat_at	TIMESTAMPTZ	Updated by health monitor
config	JSONB DEFAULT '{}'	{ rigs, models, max_polecats }
created_at	TIMESTAMPTZ NOT NULL
destroyed_at	TIMESTAMPTZ	Soft-delete

• Partial unique index: one active town per (user_id, town_name) where destroyed_at IS NULL

gastown_rigs table:

Column	Type	Notes
id	UUID PK
town_id	UUID NOT NULL	FK → gastown_towns(id)
rig_name	TEXT NOT NULL
repo_url	TEXT NOT NULL
branch	TEXT DEFAULT 'main'
status	TEXT NOT NULL DEFAULT 'active'
config	JSONB DEFAULT '{}'
created_at	TIMESTAMPTZ NOT NULL

• Unique index on (town_id, rig_name)

tRPC Router (gastown.ts)

Route	Type	Description
gastown.createTown	mutation	Generate sandbox ID → insert DB row → mint gateway JWT → create Fly machine → update DB with Fly IDs
gastown.destroyTown	mutation	Final R2 sync → soft-delete DB row → destroy Fly machine + volume
gastown.getTownStatus	query	Town status, last heartbeat, last R2 sync
gastown.listTowns	query	User's active towns
gastown.addRig	mutation	Insert into gastown_rigs, call sandbox internal API
gastown.removeRig	mutation	Remove rig from DB + sandbox
gastown.stopTown	mutation	Trigger R2 sync → stop Fly machine (volume persists)
gastown.startTown	mutation	Start Fly machine → volume data intact → gt up

Provisioning Flow (createTown)

1. Generate sandboxId from userId + townName (deterministic, like kiloclaw/sandbox-id.ts)
2. Insert row into gastown_towns with status provisioning
3. Mint a long-lived gateway JWT (see PR 5 for full implementation; stub here)
4. Call Fly.io API to create machine:
   • Image: gastown sandbox image (from PR 1)
   • Volume: 50GB persistent
   • Env vars: KILO_API_URL, KILO_JWT, TOWN_ID, R2_BUCKET, R2_ACCESS_KEY, INTERNAL_API_KEY
   • Secrets: encrypted via NaCl box (reuse kiloclaw/encryption.ts)
5. Update DB row with fly_machine_id, fly_volume_id, fly_region, status → running
6. Return town ID + status

Rollback

If Fly machine creation fails after DB insert, soft-delete the row. Same pattern as kiloclaw/instance-registry.ts:restoreDestroyedInstance.

Files

• cloud/src/db/migrations/NNNN_gastown.sql (new migration)
• cloud/src/server/api/routers/gastown.ts (new tRPC router)
• cloud/src/lib/gastown/sandbox-id.ts (new — deterministic ID generation)
• cloud/src/lib/gastown/fly-client.ts (new — Fly.io API calls, modeled on KiloClaw internal client)

Reference

• cloud/src/lib/kiloclaw/instance-registry.ts — DB row lifecycle pattern
• cloud/src/lib/kiloclaw/sandbox-id.ts — deterministic sandbox ID
• cloud/src/lib/kiloclaw/encryption.ts — NaCl box encryption
• cloud/src/lib/kiloclaw/kiloclaw-internal-client.ts — Fly.io API patterns

Acceptance Criteria

• Migration applies cleanly and creates both tables with correct indices
• createTown inserts DB row, calls Fly API (can be mocked for tests), updates status
• createTown rolls back DB row on Fly API failure
• destroyTown soft-deletes row and calls Fly API to destroy machine
• listTowns returns only active towns (not soft-deleted)
• stopTown / startTown update status and call Fly API
• addRig / removeRig manage the gastown_rigs table
• Partial unique index prevents duplicate active towns per user+name
• Sandbox ID generation is deterministic and matches KiloClaw pattern
• All routes are protected (protectedProcedure)
• Drizzle schema types are generated

Dependencies

• PR 1 (Sandbox Docker Image) — needed for the Fly machine image reference