-- Stage 0/1/2 schema: worlds, accounts, tokens, operators, towns, buildings, the event queue. -- Every game row carries world_id. Times are integer ms since the epoch (UTC). CREATE TABLE worlds ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, speed REAL NOT NULL DEFAULT 1, map_radius INTEGER NOT NULL, seed INTEGER NOT NULL, start_at INTEGER NOT NULL, end_at INTEGER, status TEXT NOT NULL DEFAULT 'running', -- running | ended preset TEXT NOT NULL DEFAULT 'standard', config TEXT NOT NULL DEFAULT '{}', created_at INTEGER NOT NULL ); CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE COLLATE NOCASE, pass_hash TEXT NOT NULL, banned INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL ); -- Opaque bearer tokens, one per device. Only the sha256 of the token is stored. CREATE TABLE tokens ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), token_hash TEXT NOT NULL UNIQUE, device TEXT NOT NULL DEFAULT '', created_at INTEGER NOT NULL, last_used_at INTEGER NOT NULL, revoked_at INTEGER ); CREATE INDEX tokens_user ON tokens(user_id); -- A user's presence in one world. CREATE TABLE operators ( id INTEGER PRIMARY KEY, world_id INTEGER NOT NULL REFERENCES worlds(id), user_id INTEGER NOT NULL REFERENCES users(id), name TEXT NOT NULL, as_number INTEGER NOT NULL, alliance_id INTEGER, protection_until INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL, UNIQUE (world_id, user_id), UNIQUE (world_id, as_number) ); -- A POP. Growing quantities are (amount, res_at): current value = rules.resourcesAt(town, now). CREATE TABLE towns ( id INTEGER PRIMARY KEY, world_id INTEGER NOT NULL REFERENCES worlds(id), owner_id INTEGER NOT NULL REFERENCES operators(id), name TEXT NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, is_capital INTEGER NOT NULL DEFAULT 0, loyalty REAL NOT NULL DEFAULT 100, loyalty_at INTEGER NOT NULL, fiber REAL NOT NULL, hardware REAL NOT NULL, capital REAL NOT NULL, battery REAL NOT NULL, footprint REAL NOT NULL DEFAULT 0, res_at INTEGER NOT NULL, created_at INTEGER NOT NULL, UNIQUE (world_id, x, y) ); CREATE INDEX towns_owner ON towns(owner_id); -- key: 'f1'..'f18' for resource fields, the building type for infrastructure. CREATE TABLE buildings ( town_id INTEGER NOT NULL REFERENCES towns(id), key TEXT NOT NULL, type TEXT NOT NULL, level INTEGER NOT NULL, PRIMARY KEY (town_id, key) ); -- Every future thing is a row here; scheduler.js processes them in (due_at, id) order. CREATE TABLE events ( id INTEGER PRIMARY KEY AUTOINCREMENT, world_id INTEGER NOT NULL, due_at INTEGER NOT NULL, type TEXT NOT NULL, town_id INTEGER, -- the main town the event is about (queue listings) payload TEXT NOT NULL DEFAULT '{}', status TEXT NOT NULL DEFAULT 'pending', -- pending | done | failed | cancelled created_at INTEGER NOT NULL, done_at INTEGER, error TEXT ); CREATE INDEX events_due ON events(status, due_at, id); CREATE INDEX events_town ON events(town_id, status); -- Written by event handlers so APNs/FCM push can be added later without touching game logic. CREATE TABLE notifications ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), world_id INTEGER, type TEXT NOT NULL, payload TEXT NOT NULL DEFAULT '{}', created_at INTEGER NOT NULL, read_at INTEGER ); CREATE INDEX notifications_user ON notifications(user_id, id); -- Idempotency-Key replay store for mutating POSTs. CREATE TABLE idempotency ( user_id INTEGER NOT NULL, key TEXT NOT NULL, request TEXT NOT NULL, status INTEGER NOT NULL, body TEXT NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (user_id, key) );