Something went wrong. Try again.
atproto Thingiverse but good
Something went wrong. Try again.
3.8 kB · 95 lines
SQL
at commit bb68bf8c
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596-- PM-76: durable LDraw resource projection, verification, and opaque routes.-- All dependent cleanup is performed by application transactions; SQLite foreign-key-- enforcement is intentionally not required by this schema.
CREATE TABLE ldraw_resources ( resource_uri TEXT PRIMARY KEY, owner_did TEXT NOT NULL, project_uri TEXT NOT NULL, record_cid TEXT NOT NULL, rkey TEXT NOT NULL, root TEXT NOT NULL, canonical_path TEXT NOT NULL, mime_type TEXT NOT NULL, byte_length INTEGER NOT NULL CHECK (byte_length >= 0), sha256 BLOB NOT NULL CHECK (length(sha256) = 32), ordered_blob_cids TEXT NOT NULL, provenance TEXT NOT NULL, licence TEXT NOT NULL, notices_json TEXT NOT NULL, snapshot_id TEXT, legacy INTEGER NOT NULL DEFAULT 0 CHECK (legacy IN (0, 1)), revoked_at INTEGER, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, UNIQUE (owner_did, project_uri, root, canonical_path), UNIQUE (resource_uri, sha256, byte_length, ordered_blob_cids));CREATE INDEX idx_ldraw_resources_owner ON ldraw_resources(owner_did);CREATE INDEX idx_ldraw_resources_project_path ON ldraw_resources(project_uri, root, canonical_path);CREATE INDEX idx_ldraw_resources_source ON ldraw_resources(resource_uri, sha256, byte_length);
CREATE TABLE ldraw_manifest_files ( resource_uri TEXT NOT NULL, root TEXT NOT NULL, canonical_path TEXT NOT NULL, target_resource_uri TEXT NOT NULL, target_cid TEXT NOT NULL, byte_length INTEGER NOT NULL CHECK (byte_length >= 0), sha256 BLOB NOT NULL CHECK (length(sha256) = 32), mime_type TEXT NOT NULL, ordinal INTEGER NOT NULL CHECK (ordinal >= 0), PRIMARY KEY (resource_uri, root, canonical_path), UNIQUE (resource_uri, ordinal));CREATE INDEX idx_ldraw_manifest_target ON ldraw_manifest_files(target_resource_uri, target_cid);
CREATE TABLE ldraw_verification ( resource_uri TEXT PRIMARY KEY, source_identity TEXT NOT NULL, state TEXT NOT NULL CHECK (state IN ('unverified', 'verifying', 'verified', 'rejected')), attempt_token TEXT UNIQUE, lease_expires_at INTEGER, expired_attempts INTEGER NOT NULL DEFAULT 0 CHECK (expired_attempts >= 0 AND expired_attempts <= 3), diagnostic TEXT, revoked_at INTEGER, updated_at INTEGER NOT NULL);CREATE INDEX idx_ldraw_verification_state ON ldraw_verification(state, lease_expires_at);CREATE INDEX idx_ldraw_verification_source ON ldraw_verification(source_identity);
CREATE TABLE ldraw_routes ( token_digest BLOB PRIMARY KEY CHECK (length(token_digest) = 32), principal_did TEXT, project_uri TEXT NOT NULL, resource_uri TEXT NOT NULL, source_identity TEXT NOT NULL, root TEXT NOT NULL, canonical_path TEXT NOT NULL, target_identity TEXT NOT NULL, expires_at INTEGER NOT NULL, revoked_at INTEGER, created_at INTEGER NOT NULL, UNIQUE (principal_did, project_uri, resource_uri, source_identity, root, canonical_path, target_identity));CREATE INDEX idx_ldraw_routes_auth ON ldraw_routes(principal_did, project_uri, expires_at, revoked_at);CREATE INDEX idx_ldraw_routes_resource ON ldraw_routes(resource_uri, source_identity);
CREATE TABLE ldraw_official_snapshots ( snapshot_id TEXT PRIMARY KEY, digest BLOB NOT NULL CHECK (length(digest) = 32), licence TEXT NOT NULL, notices_json TEXT NOT NULL, loaded_at INTEGER NOT NULL, UNIQUE (snapshot_id, digest));
-- Legacy rows are represented explicitly and remain unreadable until verified.CREATE TABLE ldraw_legacy_resources ( resource_uri TEXT PRIMARY KEY, owner_did TEXT NOT NULL, source_record_cid TEXT, reason TEXT NOT NULL, classified_at INTEGER NOT NULL);CREATE INDEX idx_ldraw_legacy_owner ON ldraw_legacy_resources(owner_did);