Something went wrong. Try again.
atproto Thingiverse but good
Something went wrong. Try again.
8.1 kB · 252 lines
SQL
at commit 33cc87be
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253-- Polymodel initial SQLite projection schema (consolidated baseline).---- Single non-destructive baseline combining the former 001–005 migrations into-- their final form: record_json on content tables, persisted CIDs on social-- records, owner-scoped upload staging, and read-your-writes pending-ops. Fresh-- databases get the complete schema in one step, and future schema changes add-- new migration files rather than rewriting tables, so projected data is never-- destroyed by a migration.---- WAL mode is intentionally NOT set here: sqlx runs migrations inside a-- transaction and SQLite silently ignores journal_mode changes within one. WAL-- is enabled programmatically on the connection pool (src/indexing/db.rs).
-- Projection state (durable cursor for crash recovery)CREATE TABLE projection_state ( key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at INTEGER NOT NULL);
-- Content records (space.polymodel.library.*). record_json holds the raw ATProto-- record so read endpoints and eager read-your-own-writes resolve from SQLite.CREATE TABLE things ( did TEXT NOT NULL, rkey TEXT NOT NULL, uri TEXT NOT NULL UNIQUE, cid TEXT NOT NULL, name TEXT NOT NULL, summary TEXT, license TEXT NOT NULL, tags_json TEXT, tags_text TEXT, instructions_text TEXT, cover_json TEXT, derived_from_uri TEXT, record_json TEXT, created_at INTEGER NOT NULL, indexed_at INTEGER NOT NULL, PRIMARY KEY (did, rkey));CREATE INDEX idx_things_did ON things(did, created_at DESC);CREATE INDEX idx_things_created ON things(created_at DESC);
CREATE TABLE models ( did TEXT NOT NULL, rkey TEXT NOT NULL, uri TEXT NOT NULL UNIQUE, cid TEXT NOT NULL, name TEXT NOT NULL, summary TEXT, record_json TEXT, created_at INTEGER NOT NULL, indexed_at INTEGER NOT NULL, PRIMARY KEY (did, rkey));
CREATE TABLE parts ( did TEXT NOT NULL, rkey TEXT NOT NULL, uri TEXT NOT NULL UNIQUE, cid TEXT NOT NULL, name TEXT NOT NULL, format TEXT, file_json TEXT NOT NULL, record_json TEXT, created_at INTEGER NOT NULL, indexed_at INTEGER NOT NULL, PRIMARY KEY (did, rkey));
-- Junction tables (relationship lookups + ordering)CREATE TABLE thing_models ( thing_uri TEXT NOT NULL, model_uri TEXT NOT NULL, position INTEGER NOT NULL, PRIMARY KEY (thing_uri, model_uri));CREATE INDEX idx_thing_models_model ON thing_models(model_uri);CREATE INDEX idx_thing_models_thing_pos ON thing_models(thing_uri, position);
CREATE TABLE model_parts ( model_uri TEXT NOT NULL, part_uri TEXT NOT NULL, position INTEGER NOT NULL, PRIMARY KEY (model_uri, part_uri));CREATE INDEX idx_model_parts_part ON model_parts(part_uri);CREATE INDEX idx_model_parts_model_pos ON model_parts(model_uri, position);
-- Social record projections (space.polymodel.graph.*), with persisted record CIDs.CREATE TABLE likes ( did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, subject_uri TEXT NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (did, rkey), UNIQUE (did, subject_uri));CREATE INDEX idx_likes_subject ON likes(subject_uri);
CREATE TABLE saves ( did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, subject_uri TEXT NOT NULL, note TEXT, created_at INTEGER NOT NULL, PRIMARY KEY (did, rkey), UNIQUE (did, subject_uri));CREATE INDEX idx_saves_subject ON saves(subject_uri);
CREATE TABLE tags ( did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, subject_uri TEXT NOT NULL, tag TEXT NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (did, rkey));CREATE INDEX idx_tags_subject ON tags(subject_uri);CREATE INDEX idx_tags_tag ON tags(tag);
CREATE TABLE listitems ( did TEXT NOT NULL, rkey TEXT NOT NULL, cid TEXT NOT NULL, list_uri TEXT NOT NULL, subject_uri TEXT NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (did, rkey));CREATE INDEX idx_listitems_list ON listitems(list_uri, created_at);
-- Denormalized engagement countsCREATE TABLE content_stats ( uri TEXT PRIMARY KEY, like_count INTEGER NOT NULL DEFAULT 0, save_count INTEGER NOT NULL DEFAULT 0, tag_count INTEGER NOT NULL DEFAULT 0);
-- Full-text search (FTS5, external content) over thingsCREATE VIRTUAL TABLE things_fts USING fts5( name, summary, tags_text, instructions_text, content=things, content_rowid=rowid, tokenize='porter unicode61');
CREATE TRIGGER things_ai AFTER INSERT ON things BEGIN INSERT INTO things_fts(rowid, name, summary, tags_text, instructions_text) VALUES (new.rowid, new.name, new.summary, new.tags_text, new.instructions_text);END;CREATE TRIGGER things_ad AFTER DELETE ON things BEGIN INSERT INTO things_fts(things_fts, rowid, name, summary, tags_text, instructions_text) VALUES ('delete', old.rowid, old.name, old.summary, old.tags_text, old.instructions_text);END;CREATE TRIGGER things_au AFTER UPDATE ON things BEGIN INSERT INTO things_fts(things_fts, rowid, name, summary, tags_text, instructions_text) VALUES ('delete', old.rowid, old.name, old.summary, old.tags_text, old.instructions_text); INSERT INTO things_fts(rowid, name, summary, tags_text, instructions_text) VALUES (new.rowid, new.name, new.summary, new.tags_text, new.instructions_text);END;
-- Polymodel actor profile (singleton record, rkey = "self"). Handle is not stored-- here: it lives in `identities` (identity-level) so it stays current across-- handle changes.CREATE TABLE profiles ( did TEXT PRIMARY KEY, display_name TEXT, description TEXT, avatar_json TEXT, default_license TEXT, pronouns TEXT, printers_json TEXT, links_json TEXT, record_json TEXT NOT NULL, indexed_at INTEGER NOT NULL);
-- DID -> handle, refreshed by identity events. Joined by profile/actor hydration.CREATE TABLE identities ( did TEXT PRIMARY KEY, handle TEXT, updated_at INTEGER NOT NULL);
-- OAuth session persistence (DB-backed ClientAuthStore for Jacquard 0.12 OAuth).-- Structured columns power key enumeration / by-DID selection / deletes without-- deserializing payload; payload holds the canonical ClientSessionData JSON.CREATE TABLE oauth_sessions ( account_did TEXT NOT NULL, session_id TEXT NOT NULL, host_url TEXT NOT NULL, authserver_url TEXT NOT NULL, token_endpoint TEXT NOT NULL, revocation_endpoint TEXT, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, payload TEXT NOT NULL, PRIMARY KEY (account_did, session_id));CREATE INDEX idx_oauth_sessions_did ON oauth_sessions(account_did);
-- Pending OAuth authorization requests (PAR state round-trip).CREATE TABLE oauth_auth_requests ( state TEXT NOT NULL, authserver_url TEXT NOT NULL, account_did TEXT, request_uri TEXT NOT NULL, token_endpoint TEXT NOT NULL, revocation_endpoint TEXT, created_at INTEGER NOT NULL, payload TEXT NOT NULL, PRIMARY KEY (state));CREATE INDEX idx_oauth_auth_req_did ON oauth_auth_requests(account_did);
-- Owner-scoped upload staging for raw geometry bytes (publish flow).CREATE TABLE upload_staging ( owner_did TEXT NOT NULL, upload_id TEXT NOT NULL, sha256 BLOB NOT NULL, mime_type TEXT NOT NULL, size INTEGER NOT NULL, filename TEXT, status TEXT NOT NULL, file_json TEXT NOT NULL, chunks_json TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, PRIMARY KEY (owner_did, upload_id));CREATE INDEX idx_upload_staging_owner_updated ON upload_staging(owner_did, updated_at DESC);
-- Read-your-writes pending-ops tracking. Collection-agnostic; keyed by record-- URI. Cleared by the firehose record-event projection once the Hydrant #commit-- redelivers the event.CREATE TABLE pending_writes ( uri TEXT PRIMARY KEY, cid TEXT NOT NULL, value TEXT NOT NULL, written_at INTEGER NOT NULL);
CREATE TABLE pending_deletes ( uri TEXT PRIMARY KEY, deleted_at INTEGER NOT NULL);