1-- Tribes initial schema. Times are unix epoch seconds (INTEGER).2CREATE TABLE users (3 id TEXT PRIMARY KEY,4 external_id TEXT NOT NULL UNIQUE, -- shoo pairwise_sub or 'local:<name>'5 name TEXT,6 created_at INTEGER NOT NULL7);89CREATE TABLE api_tokens (10 id TEXT PRIMARY KEY,11 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,12 token_hash TEXT NOT NULL UNIQUE, -- sha-256 hex of the full token13 name TEXT NOT NULL,14 created_at INTEGER NOT NULL15);1617-- Content-addressed script sources. Swept when unreferenced by live rows.18CREATE TABLE blobs (19 hash TEXT PRIMARY KEY, -- sha-256 hex20 source TEXT NOT NULL,21 created_at INTEGER NOT NULL22);2324CREATE TABLE scripts (25 id TEXT PRIMARY KEY, -- lg_<nanoid>26 owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,27 name TEXT NOT NULL,28 lang TEXT NOT NULL DEFAULT 'js',29 version INTEGER NOT NULL, -- latest version number30 hash TEXT NOT NULL REFERENCES blobs(hash),31 status TEXT NOT NULL CHECK (status IN ('validated', 'rejected')),32 error_report TEXT,33 created_at INTEGER NOT NULL,34 expires_at INTEGER, -- NULL only transitionally; liveness = starred OR expires_at > now35 UNIQUE (owner_id, name)36);3738CREATE TABLE script_versions (39 script_id TEXT NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,40 version INTEGER NOT NULL,41 hash TEXT NOT NULL REFERENCES blobs(hash),42 created_at INTEGER NOT NULL,43 PRIMARY KEY (script_id, version)44);4546CREATE TABLE sims (47 id TEXT PRIMARY KEY, -- sim_<nanoid>48 creator_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,49 seed TEXT NOT NULL,50 config_json TEXT NOT NULL,51 created_at INTEGER NOT NULL,52 expires_at INTEGER53);5455CREATE TABLE sim_scripts (56 sim_id TEXT NOT NULL REFERENCES sims(id) ON DELETE CASCADE,57 slot INTEGER NOT NULL,58 script_id TEXT NOT NULL, -- intentionally NOT a FK: sim outlives script via blob pin59 script_name TEXT NOT NULL,60 version INTEGER NOT NULL,61 hash TEXT NOT NULL REFERENCES blobs(hash),62 PRIMARY KEY (sim_id, slot)63);6465CREATE TABLE stars (66 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,67 kind TEXT NOT NULL CHECK (kind IN ('script', 'sim')),68 target_id TEXT NOT NULL,69 created_at INTEGER NOT NULL,70 PRIMARY KEY (user_id, kind, target_id)71);7273CREATE INDEX idx_scripts_owner ON scripts(owner_id);74CREATE INDEX idx_sims_creator ON sims(creator_id);75CREATE INDEX idx_stars_target ON stars(kind, target_id);76CREATE INDEX idx_sim_scripts_hash ON sim_scripts(hash);77CREATE INDEX idx_script_versions_hash ON script_versions(hash);