Something went wrong. Try again.
atproto Thingiverse but good
Something went wrong. Try again.
2.9 kB · 74 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475-- PM-76: durable LDraw resource projection and verification metadata.-- Public compound routes are stateless; this schema intentionally contains no-- route rows, bearer capabilities, expiry, replay, revocation, or CAS state.-- 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)), 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', 'verified', 'rejected')), diagnostic TEXT, updated_at INTEGER NOT NULL);CREATE INDEX idx_ldraw_verification_state ON ldraw_verification(state);CREATE INDEX idx_ldraw_verification_source ON ldraw_verification(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);