1-- Social layer: public sharing, hearts, ratings/leaderboard, model tags,2-- soft delete, dedup soft-links, server-verified sim results.3-- "Listed" (publicly browsable) = owner has public_sharing=1 AND the owner4-- starred the script AND status='validated' AND deleted_at IS NULL AND5-- linked_script_id IS NULL. Times are unix epoch seconds (INTEGER).67ALTER TABLE users ADD COLUMN public_sharing INTEGER NOT NULL DEFAULT 0;89ALTER TABLE scripts ADD COLUMN title TEXT; -- public title (fallback: name)10ALTER TABLE scripts ADD COLUMN description TEXT; -- public description11ALTER TABLE scripts ADD COLUMN source_shared INTEGER NOT NULL DEFAULT 0;12ALTER TABLE scripts ADD COLUMN model_id TEXT; -- models.dev id (optional attribution)13ALTER TABLE scripts ADD COLUMN deleted_at INTEGER; -- soft delete; purged after 14 d14ALTER TABLE scripts ADD COLUMN linked_script_id TEXT; -- dedup soft-link to canonical listed script1516ALTER TABLE blobs ADD COLUMN norm_hash TEXT; -- normalized-source hash (dedup identity)17CREATE INDEX idx_blobs_norm ON blobs(norm_hash);1819-- Bookmarks of others' listed scripts (the "liked pool" usable in Simulate).20-- script_id is intentionally NOT a FK: canonical scripts survive owner21-- deletion via anonymization, and a vanished target simply stops resolving.22CREATE TABLE hearts (23 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,24 script_id TEXT NOT NULL,25 created_at INTEGER NOT NULL,26 PRIMARY KEY (user_id, script_id)27);28CREATE INDEX idx_hearts_script ON hearts(script_id);2930-- Model catalog synced daily from https://models.dev/api.json (never deleted:31-- an attribution tag must stay resolvable even if delisted upstream).32CREATE TABLE models (33 id TEXT PRIMARY KEY, -- e.g. 'anthropic/claude-fable-5'34 name TEXT NOT NULL,35 lab TEXT NOT NULL, -- id prefix before '/', else source provider36 open_weights INTEGER NOT NULL,37 release_date TEXT, -- ISO date or NULL38 reasoning INTEGER,39 last_updated TEXT,40 synced_at INTEGER NOT NULL41);42CREATE INDEX idx_models_lab ON models(lab);43CREATE INDEX idx_models_release ON models(release_date);4445-- One row per rated script (canonical listed scripts only). All-time board.46CREATE TABLE ratings (47 script_id TEXT PRIMARY KEY,48 rating INTEGER NOT NULL DEFAULT 1000,49 games INTEGER NOT NULL DEFAULT 0,50 wins INTEGER NOT NULL DEFAULT 0,51 battles INTEGER NOT NULL DEFAULT 0, -- summed battlesWon+battlesLost across rated sims52 updated_at INTEGER NOT NULL53);5455-- Append-only rating deltas; windowed leaderboards = SUM(delta) per window.56-- PK (sim_id, script_id) makes rating application idempotent per sim.57CREATE TABLE rating_events (58 sim_id TEXT NOT NULL,59 script_id TEXT NOT NULL,60 delta INTEGER NOT NULL,61 rating_after INTEGER NOT NULL,62 created_at INTEGER NOT NULL,63 PRIMARY KEY (sim_id, script_id)64);65CREATE INDEX idx_rating_events_script ON rating_events(script_id, created_at);66CREATE INDEX idx_rating_events_time ON rating_events(created_at);6768-- 'public' = every slot's script was listed at creation; rated by the server69-- replay only. 'ladder' sims are created by the nightly auto-matcher.70ALTER TABLE sims ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private';71ALTER TABLE sims ADD COLUMN kind TEXT NOT NULL DEFAULT 'user';72CREATE INDEX idx_sims_visibility ON sims(visibility);7374-- Server-verified outcome per slot (proof for Browse + the Elo input).75CREATE TABLE sim_results (76 sim_id TEXT NOT NULL REFERENCES sims(id) ON DELETE CASCADE,77 slot INTEGER NOT NULL,78 script_id TEXT NOT NULL,79 score INTEGER NOT NULL,80 place INTEGER NOT NULL, -- 1-based; ties share the better place81 battles INTEGER NOT NULL,82 PRIMARY KEY (sim_id, slot)83);84CREATE INDEX idx_sim_results_script ON sim_results(script_id);