A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- +goose StatementBegin
3
4-- WARNING: This migration removes blob storage tables.
5-- Ensure all blob data has been migrated to carstore before running this migration.
6-- This migration is NOT reversible if blob data exists!
7
8-- Remove the value column from records table since blocks are now stored in filesystem
9ALTER TABLE records DROP COLUMN IF EXISTS value;
10
11-- Drop blob-related tables since FileCarStore handles block storage
12-- WARNING: This will permanently delete all blob data!
13DROP TABLE IF EXISTS blob_refs;
14DROP TABLE IF EXISTS blobs;
15
16-- Create block_refs table for garbage collection tracking
17CREATE TABLE block_refs (
18 cid VARCHAR(256) NOT NULL,
19 did VARCHAR(256) NOT NULL,
20 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
21 PRIMARY KEY (cid, did),
22 FOREIGN KEY (did) REFERENCES repositories(did) ON DELETE CASCADE
23);
24
25CREATE INDEX idx_block_refs_did ON block_refs(did);
26CREATE INDEX idx_block_refs_created_at ON block_refs(created_at);
27
28-- +goose StatementEnd
29
30-- +goose Down
31-- +goose StatementBegin
32
33-- Recreate the original schema for rollback
34DROP TABLE IF EXISTS block_refs;
35
36-- Add back the value column to records table
37ALTER TABLE records ADD COLUMN value BYTEA;
38
39-- Recreate blobs table
40CREATE TABLE blobs (
41 cid VARCHAR(256) PRIMARY KEY,
42 mime_type VARCHAR(256) NOT NULL,
43 size BIGINT NOT NULL,
44 ref_count INTEGER NOT NULL DEFAULT 0,
45 data BYTEA NOT NULL,
46 created_at TIMESTAMP NOT NULL DEFAULT NOW()
47);
48
49CREATE INDEX idx_blobs_ref_count ON blobs(ref_count);
50CREATE INDEX idx_blobs_created_at ON blobs(created_at);
51
52-- Recreate blob_refs table
53CREATE TABLE blob_refs (
54 id SERIAL PRIMARY KEY,
55 record_id INTEGER NOT NULL,
56 blob_cid VARCHAR(256) NOT NULL,
57 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
58 FOREIGN KEY (record_id) REFERENCES records(id) ON DELETE CASCADE,
59 FOREIGN KEY (blob_cid) REFERENCES blobs(cid) ON DELETE RESTRICT,
60 UNIQUE(record_id, blob_cid)
61);
62
63CREATE INDEX idx_blob_refs_blob_cid ON blob_refs(blob_cid);
64
65-- +goose StatementEnd