jbergner
1a56dec079
All checks were successful
release-tag / release-image (push) Successful in 3m57s
287 lines
12 KiB
SQL
287 lines
12 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id TEXT PRIMARY KEY,
|
|
public_jwk TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
last_seen INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id TEXT PRIMARY KEY,
|
|
secret TEXT NOT NULL,
|
|
public_seed TEXT NOT NULL,
|
|
range_bits INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','completed','closed')),
|
|
paused INTEGER NOT NULL DEFAULT 0,
|
|
guess_min_interval_sec INTEGER,
|
|
client_submit_interval_sec INTEGER,
|
|
revision INTEGER NOT NULL DEFAULT 0,
|
|
parent_task_id TEXT REFERENCES tasks(id),
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
nft_prompt_instructions TEXT NOT NULL DEFAULT '',
|
|
nft_negative_prompt TEXT NOT NULL DEFAULT '',
|
|
nft_style_reference TEXT NOT NULL DEFAULT '',
|
|
retire_after_completion INTEGER NOT NULL DEFAULT 0,
|
|
artifact_owner_client_id TEXT REFERENCES clients(id),
|
|
artifact_origin TEXT NOT NULL DEFAULT 'win',
|
|
artifact_rarity_override TEXT NOT NULL DEFAULT '',
|
|
artifact_rarity TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
completed_at INTEGER,
|
|
winner_client_id TEXT REFERENCES clients(id),
|
|
winner_worker_client_id TEXT REFERENCES clients(id),
|
|
winner_beacon_path TEXT NOT NULL DEFAULT '',
|
|
winner_beacon_boosted_path TEXT NOT NULL DEFAULT '',
|
|
winner_beacon_round INTEGER NOT NULL DEFAULT 0,
|
|
winner_signature TEXT,
|
|
winning_guess TEXT,
|
|
artifact_status TEXT NOT NULL DEFAULT 'none' CHECK (artifact_status IN ('none','pending','generating','ready','error')),
|
|
artifact_uri TEXT,
|
|
artifact_manifest_uri TEXT,
|
|
artifact_error TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS tasks_status_created_idx ON tasks(status, created_at);
|
|
|
|
-- Current collectible ownership is separate from historical winner provenance.
|
|
-- Transfers keep the original winner fields untouched and are audit logged here.
|
|
CREATE TABLE IF NOT EXISTS artifact_transfers (
|
|
id TEXT PRIMARY KEY,
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
from_client_id TEXT REFERENCES clients(id) ON DELETE SET NULL,
|
|
to_client_id TEXT NOT NULL REFERENCES clients(id),
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS artifact_transfers_task_idx ON artifact_transfers(task_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS artifact_transfers_to_idx ON artifact_transfers(to_client_id, created_at DESC);
|
|
|
|
-- False guesses are intentionally not persisted. This table only keeps the
|
|
-- aggregate state needed for the 3D map, ranking, rate limiting and next seq.
|
|
CREATE TABLE IF NOT EXISTS task_points (
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
score REAL NOT NULL DEFAULT 0,
|
|
x REAL NOT NULL DEFAULT 0,
|
|
y REAL NOT NULL DEFAULT 0,
|
|
z REAL NOT NULL DEFAULT 0,
|
|
guess_count INTEGER NOT NULL DEFAULT 0,
|
|
next_seq INTEGER NOT NULL DEFAULT 0,
|
|
last_guess_at INTEGER,
|
|
PRIMARY KEY (task_id, client_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS task_points_rank_idx ON task_points(task_id, score DESC);
|
|
|
|
|
|
|
|
-- A browser or shell identity can explicitly choose which active task it works
|
|
-- on. The selection is persisted so switching devices with the same exported
|
|
-- identity keeps the chosen task (while single-active-connection enforcement
|
|
-- still applies at runtime).
|
|
CREATE TABLE IF NOT EXISTS client_task_selection (
|
|
client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS client_task_selection_task_idx ON client_task_selection(task_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS client_unlocks (
|
|
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
unlock_key TEXT NOT NULL,
|
|
task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
|
|
created_at INTEGER NOT NULL,
|
|
PRIMARY KEY (client_id, unlock_key)
|
|
);
|
|
|
|
-- A short lease enforces one active websocket per browser identity.
|
|
CREATE TABLE IF NOT EXISTS presence_leases (
|
|
client_id TEXT PRIMARY KEY,
|
|
session_id TEXT NOT NULL,
|
|
expires_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS presence_exp_idx ON presence_leases(expires_at);
|
|
|
|
-- Admin task actions may run immediately or at a future timestamp. Payloads are
|
|
-- small JSON objects validated by the Go server before being scheduled.
|
|
CREATE TABLE IF NOT EXISTS task_actions (
|
|
id TEXT PRIMARY KEY,
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
action_type TEXT NOT NULL,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
execute_at INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','running','done','error','cancelled')),
|
|
created_at INTEGER NOT NULL,
|
|
executed_at INTEGER,
|
|
error TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS task_actions_due_idx ON task_actions(status, execute_at);
|
|
CREATE INDEX IF NOT EXISTS task_actions_task_idx ON task_actions(task_id, created_at DESC);
|
|
|
|
-- Successful external image-model calls are logged with the token usage
|
|
-- returned by the provider. Costs are local estimates based on pinned public
|
|
-- standard token rates. Billing reconciliation still belongs to the provider.
|
|
CREATE TABLE IF NOT EXISTS artifact_api_usage (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
created_at INTEGER NOT NULL,
|
|
task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
|
|
kind TEXT NOT NULL CHECK (kind IN ('character_anchor','artifact')),
|
|
provider TEXT NOT NULL,
|
|
model TEXT NOT NULL,
|
|
endpoint TEXT NOT NULL,
|
|
size TEXT NOT NULL,
|
|
quality TEXT NOT NULL,
|
|
request_id TEXT,
|
|
input_tokens INTEGER NOT NULL DEFAULT 0,
|
|
input_text_tokens INTEGER NOT NULL DEFAULT 0,
|
|
input_image_tokens INTEGER NOT NULL DEFAULT 0,
|
|
output_tokens INTEGER NOT NULL DEFAULT 0,
|
|
total_tokens INTEGER NOT NULL DEFAULT 0,
|
|
estimated_cost_usd REAL,
|
|
pricing_basis TEXT NOT NULL DEFAULT '',
|
|
meta_json TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
CREATE INDEX IF NOT EXISTS artifact_api_usage_created_idx ON artifact_api_usage(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS artifact_api_usage_kind_created_idx ON artifact_api_usage(kind, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS artifact_api_usage_task_idx ON artifact_api_usage(task_id);
|
|
|
|
-- Publicly auditable Beacon Hunt draws. Each row records the externally
|
|
-- sourced drand reveal used for the weighted path/draw decision.
|
|
CREATE TABLE IF NOT EXISTS beacon_draws (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
window_end INTEGER NOT NULL,
|
|
beacon_id TEXT NOT NULL,
|
|
beacon_round INTEGER NOT NULL,
|
|
randomness TEXT NOT NULL,
|
|
signature TEXT NOT NULL DEFAULT '',
|
|
boosted_path TEXT NOT NULL,
|
|
ticket_count INTEGER NOT NULL,
|
|
selected_count INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
UNIQUE(task_id, window_end)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS beacon_draws_task_idx ON beacon_draws(task_id, window_end DESC);
|
|
|
|
|
|
-- A hosted worker uses its own cryptographic identity/presence, while prizes can
|
|
-- be delegated to a durable customer-owned reward identity. The worker remains
|
|
-- auditable on the completed task through winner_worker_client_id.
|
|
CREATE TABLE IF NOT EXISTS identity_delegations (
|
|
worker_client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
|
|
owner_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS identity_delegations_owner_idx ON identity_delegations(owner_client_id);
|
|
|
|
-- One-shot pairing codes let a logged-in owner prove control of the reward
|
|
-- identity to Customer Service without sharing the P-256 private key. Only the
|
|
-- SHA-256 token hash is stored and codes expire quickly.
|
|
CREATE TABLE IF NOT EXISTS customer_link_tokens (
|
|
token_hash TEXT PRIMARY KEY,
|
|
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
expires_at INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS customer_link_tokens_exp_idx ON customer_link_tokens(expires_at);
|
|
|
|
-- Durable outbox for hosted-worker positive-tip rewards. A "positive tip" is
|
|
-- a signed guess that improves that worker's personal best score. Delivery to
|
|
-- Customer Service is retried and the event id is idempotent there.
|
|
CREATE TABLE IF NOT EXISTS hosted_credit_events (
|
|
event_id TEXT PRIMARY KEY,
|
|
worker_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
reward_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
seq INTEGER NOT NULL,
|
|
score REAL NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
delivered_at INTEGER,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX IF NOT EXISTS hosted_credit_events_pending_idx ON hosted_credit_events(delivered_at,created_at);
|
|
|
|
-- Neural Place: a shared, r/place-inspired canvas funded exclusively by
|
|
-- genuine Hunt score improvements. Point balances use milli-points so tiny
|
|
-- score increases accumulate instead of being rounded away.
|
|
CREATE TABLE IF NOT EXISTS place_wallets (
|
|
client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
|
|
balance_milli INTEGER NOT NULL DEFAULT 0,
|
|
earned_milli INTEGER NOT NULL DEFAULT 0,
|
|
spent_milli INTEGER NOT NULL DEFAULT 0,
|
|
placements INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS place_state (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
revision INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
INSERT OR IGNORE INTO place_state(id,revision,updated_at) VALUES(1,0,0);
|
|
|
|
CREATE TABLE IF NOT EXISTS place_pixels (
|
|
x INTEGER NOT NULL,
|
|
y INTEGER NOT NULL,
|
|
color_index INTEGER NOT NULL,
|
|
client_id TEXT NOT NULL,
|
|
placed_at INTEGER NOT NULL,
|
|
revision INTEGER NOT NULL,
|
|
PRIMARY KEY (x,y)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS place_pixels_revision_idx ON place_pixels(revision);
|
|
CREATE INDEX IF NOT EXISTS place_pixels_client_idx ON place_pixels(client_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS place_pixel_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
x INTEGER NOT NULL,
|
|
y INTEGER NOT NULL,
|
|
color_index INTEGER NOT NULL,
|
|
client_id TEXT NOT NULL,
|
|
cost_milli INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
revision INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS place_pixel_events_created_idx ON place_pixel_events(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS place_pixel_events_client_idx ON place_pixel_events(client_id,created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS place_progress_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
owner_client_id TEXT NOT NULL,
|
|
source_client_id TEXT NOT NULL,
|
|
task_id TEXT NOT NULL,
|
|
old_score REAL NOT NULL,
|
|
new_score REAL NOT NULL,
|
|
points_milli INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS place_progress_owner_idx ON place_progress_events(owner_client_id,created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS place_progress_source_idx ON place_progress_events(source_client_id,created_at DESC);
|
|
|
|
-- V4.5 Place bonus economy: lottery draw rewards, active-time rewards and
|
|
-- manual admin grants. event_key makes externally triggered rewards idempotent.
|
|
CREATE TABLE IF NOT EXISTS place_bonus_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_key TEXT NOT NULL UNIQUE,
|
|
owner_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
source_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
task_id TEXT NOT NULL DEFAULT '',
|
|
kind TEXT NOT NULL,
|
|
points_milli INTEGER NOT NULL,
|
|
multiplier REAL NOT NULL DEFAULT 1,
|
|
units INTEGER NOT NULL DEFAULT 1,
|
|
detail TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS place_bonus_owner_idx ON place_bonus_events(owner_client_id,created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS place_bonus_source_idx ON place_bonus_events(source_client_id,created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS place_bonus_kind_idx ON place_bonus_events(kind,created_at DESC);
|