A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- +goose StatementBegin
3
4-- Repositories table stores metadata about each user's repository
5CREATE TABLE repositories (
6 did VARCHAR(256) PRIMARY KEY,
7 head_cid VARCHAR(256) NOT NULL,
8 revision VARCHAR(64) NOT NULL,
9 record_count INTEGER NOT NULL DEFAULT 0,
10 storage_size BIGINT NOT NULL DEFAULT 0,
11 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
12 updated_at TIMESTAMP NOT NULL DEFAULT NOW()
13);
14
15CREATE INDEX idx_repositories_updated_at ON repositories(updated_at);
16
17-- Commits table stores the commit history
18CREATE TABLE commits (
19 cid VARCHAR(256) PRIMARY KEY,
20 did VARCHAR(256) NOT NULL,
21 version INTEGER NOT NULL,
22 prev_cid VARCHAR(256),
23 data_cid VARCHAR(256) NOT NULL,
24 revision VARCHAR(64) NOT NULL,
25 signature BYTEA NOT NULL,
26 signing_key_id VARCHAR(256) NOT NULL,
27 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
28 FOREIGN KEY (did) REFERENCES repositories(did) ON DELETE CASCADE
29);
30
31CREATE INDEX idx_commits_did ON commits(did);
32CREATE INDEX idx_commits_created_at ON commits(created_at);
33
34-- Records table stores record metadata (actual data is in MST)
35CREATE TABLE records (
36 id SERIAL PRIMARY KEY,
37 did VARCHAR(256) NOT NULL,
38 uri VARCHAR(512) NOT NULL,
39 cid VARCHAR(256) NOT NULL,
40 collection VARCHAR(256) NOT NULL,
41 record_key VARCHAR(256) NOT NULL,
42 value BYTEA NOT NULL, -- CBOR-encoded record data
43 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
44 updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
45 UNIQUE(did, collection, record_key),
46 FOREIGN KEY (did) REFERENCES repositories(did) ON DELETE CASCADE
47);
48
49CREATE INDEX idx_records_did_collection ON records(did, collection);
50CREATE INDEX idx_records_uri ON records(uri);
51CREATE INDEX idx_records_updated_at ON records(updated_at);
52
53-- Blobs table stores binary large objects
54CREATE TABLE blobs (
55 cid VARCHAR(256) PRIMARY KEY,
56 mime_type VARCHAR(256) NOT NULL,
57 size BIGINT NOT NULL,
58 ref_count INTEGER NOT NULL DEFAULT 0,
59 data BYTEA NOT NULL,
60 created_at TIMESTAMP NOT NULL DEFAULT NOW()
61);
62
63CREATE INDEX idx_blobs_ref_count ON blobs(ref_count);
64CREATE INDEX idx_blobs_created_at ON blobs(created_at);
65
66-- Blob references table tracks which records reference which blobs
67CREATE TABLE blob_refs (
68 id SERIAL PRIMARY KEY,
69 record_id INTEGER NOT NULL,
70 blob_cid VARCHAR(256) NOT NULL,
71 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
72 FOREIGN KEY (record_id) REFERENCES records(id) ON DELETE CASCADE,
73 FOREIGN KEY (blob_cid) REFERENCES blobs(cid) ON DELETE RESTRICT,
74 UNIQUE(record_id, blob_cid)
75);
76
77CREATE INDEX idx_blob_refs_blob_cid ON blob_refs(blob_cid);
78
79-- +goose StatementEnd
80
81-- +goose Down
82-- +goose StatementBegin
83DROP TABLE IF EXISTS blob_refs;
84DROP TABLE IF EXISTS blobs;
85DROP TABLE IF EXISTS records;
86DROP TABLE IF EXISTS commits;
87DROP TABLE IF EXISTS repositories;
88-- +goose StatementEnd