teal.fm
1-- CAR import functionality tables
2-- For handling AT Protocol CAR file imports and processing
3
4-- Tracks uploaded CAR files that are queued for processing
5CREATE TABLE IF NOT EXISTS car_import_requests (
6 import_id TEXT PRIMARY KEY,
7 car_data_base64 TEXT NOT NULL,
8 status TEXT NOT NULL DEFAULT 'pending', -- pending, processing, completed, failed
9 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
10 processed_at TIMESTAMP WITH TIME ZONE,
11 error_message TEXT,
12 file_size_bytes INTEGER,
13 block_count INTEGER,
14 extracted_records_count INTEGER DEFAULT 0
15);
16
17CREATE INDEX idx_car_import_requests_status ON car_import_requests (status);
18CREATE INDEX idx_car_import_requests_created_at ON car_import_requests (created_at);
19
20-- Tracks raw IPLD blocks extracted from CAR files
21CREATE TABLE IF NOT EXISTS car_blocks (
22 cid TEXT PRIMARY KEY,
23 import_id TEXT NOT NULL REFERENCES car_import_requests(import_id),
24 block_data BYTEA NOT NULL,
25 decoded_successfully BOOLEAN DEFAULT FALSE,
26 collection_type TEXT, -- e.g., 'fm.teal.alpha.feed.play', 'commit', etc.
27 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
28);
29
30CREATE INDEX idx_car_blocks_import_id ON car_blocks (import_id);
31CREATE INDEX idx_car_blocks_collection_type ON car_blocks (collection_type);
32
33-- Tracks records extracted from CAR imports that were successfully processed
34CREATE TABLE IF NOT EXISTS car_extracted_records (
35 id SERIAL PRIMARY KEY,
36 import_id TEXT NOT NULL REFERENCES car_import_requests(import_id),
37 cid TEXT NOT NULL REFERENCES car_blocks(cid),
38 collection_type TEXT NOT NULL,
39 record_uri TEXT, -- AT URI if applicable (e.g., for play records)
40 synthetic_did TEXT, -- DID assigned for CAR imports (e.g., 'car-import:123')
41 rkey TEXT,
42 extracted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
43 processing_notes TEXT
44);
45
46CREATE INDEX idx_car_extracted_records_import_id ON car_extracted_records (import_id);
47CREATE INDEX idx_car_extracted_records_collection_type ON car_extracted_records (collection_type);
48CREATE INDEX idx_car_extracted_records_record_uri ON car_extracted_records (record_uri);
49
50-- Tracks import metadata and commit information
51CREATE TABLE IF NOT EXISTS car_import_metadata (
52 import_id TEXT NOT NULL REFERENCES car_import_requests(import_id),
53 metadata_key TEXT NOT NULL,
54 metadata_value JSONB NOT NULL,
55 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
56 PRIMARY KEY (import_id, metadata_key)
57);
58
59CREATE INDEX idx_car_import_metadata_key ON car_import_metadata (metadata_key);