slices.network
Auto-indexing service and GraphQL API for AT Protocol Records
quickslice.slices.network/
atproto
gleam
graphql
chadtmiller.com
1-- migrate:up
2
3-- =============================================================================
4-- Label Definition Table
5-- =============================================================================
6
7-- Defines available label values for this instance
8CREATE TABLE IF NOT EXISTS label_definition (
9 val TEXT PRIMARY KEY NOT NULL,
10 description TEXT NOT NULL,
11 severity TEXT NOT NULL CHECK (severity IN ('inform', 'alert', 'takedown')),
12 created_at TEXT NOT NULL DEFAULT (datetime('now'))
13);
14
15-- Seed default label definitions (Bluesky-compatible)
16INSERT INTO label_definition (val, description, severity) VALUES
17 ('!takedown', 'Content removed by moderators', 'takedown'),
18 ('!suspend', 'Account suspended', 'takedown'),
19 ('!warn', 'Show warning before displaying', 'alert'),
20 ('!hide', 'Hide from feeds (still accessible via direct link)', 'alert'),
21 ('porn', 'Pornographic content', 'alert'),
22 ('sexual', 'Sexually suggestive content', 'alert'),
23 ('nudity', 'Non-sexual nudity', 'alert'),
24 ('gore', 'Graphic violence or gore', 'alert'),
25 ('graphic-media', 'Disturbing or graphic media', 'alert'),
26 ('impersonation', 'Account impersonating someone', 'inform'),
27 ('spam', 'Spam or unwanted content', 'inform');
28
29-- =============================================================================
30-- Label Table
31-- =============================================================================
32
33-- Applied labels on records/accounts
34CREATE TABLE IF NOT EXISTS label (
35 id INTEGER PRIMARY KEY AUTOINCREMENT,
36 src TEXT NOT NULL,
37 uri TEXT NOT NULL,
38 cid TEXT,
39 val TEXT NOT NULL,
40 neg INTEGER NOT NULL DEFAULT 0,
41 cts TEXT NOT NULL DEFAULT (datetime('now')),
42 exp TEXT,
43 FOREIGN KEY (val) REFERENCES label_definition(val)
44);
45
46CREATE INDEX IF NOT EXISTS idx_label_uri ON label(uri);
47CREATE INDEX IF NOT EXISTS idx_label_val ON label(val);
48CREATE INDEX IF NOT EXISTS idx_label_src ON label(src);
49CREATE INDEX IF NOT EXISTS idx_label_cts ON label(cts DESC);
50-- Composite index for takedown queries (uri + val + neg)
51CREATE INDEX IF NOT EXISTS idx_label_takedown ON label(uri, val, neg);
52
53-- =============================================================================
54-- Report Table
55-- =============================================================================
56
57-- User-submitted reports awaiting review
58CREATE TABLE IF NOT EXISTS report (
59 id INTEGER PRIMARY KEY AUTOINCREMENT,
60 reporter_did TEXT NOT NULL,
61 subject_uri TEXT NOT NULL,
62 reason_type TEXT NOT NULL CHECK (reason_type IN ('spam', 'violation', 'misleading', 'sexual', 'rude', 'other')),
63 reason TEXT,
64 status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'resolved', 'dismissed')),
65 resolved_by TEXT,
66 resolved_at TEXT,
67 created_at TEXT NOT NULL DEFAULT (datetime('now')),
68 -- Prevent duplicate reports from same user for same content
69 UNIQUE(reporter_did, subject_uri)
70);
71
72CREATE INDEX IF NOT EXISTS idx_report_status ON report(status);
73CREATE INDEX IF NOT EXISTS idx_report_subject_uri ON report(subject_uri);
74CREATE INDEX IF NOT EXISTS idx_report_reporter_did ON report(reporter_did);
75CREATE INDEX IF NOT EXISTS idx_report_created_at ON report(created_at DESC);
76
77-- migrate:down
78
79DROP TABLE IF EXISTS report;
80DROP TABLE IF EXISTS label;
81DROP TABLE IF EXISTS label_definition;