A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Add deletion reason tracking to preserve thread structure while respecting privacy
3-- When comments are deleted, we blank content but keep the record for threading
4
5-- Create enum type for deletion reasons
6CREATE TYPE deletion_reason AS ENUM ('author', 'moderator');
7
8-- Add new columns to comments table
9ALTER TABLE comments ADD COLUMN deletion_reason deletion_reason;
10ALTER TABLE comments ADD COLUMN deleted_by TEXT;
11
12-- Add comments for new columns
13COMMENT ON COLUMN comments.deletion_reason IS 'Reason for deletion: author (user deleted), moderator (community mod removed)';
14COMMENT ON COLUMN comments.deleted_by IS 'DID of the actor who performed the deletion';
15
16-- Backfill existing deleted comments as author-deleted
17-- This handles existing soft-deleted comments gracefully
18UPDATE comments
19SET deletion_reason = 'author',
20 deleted_by = commenter_did
21WHERE deleted_at IS NOT NULL AND deletion_reason IS NULL;
22
23-- Modify existing indexes to NOT filter deleted_at IS NULL
24-- This allows deleted comments to appear in thread queries for structure preservation
25-- Note: We drop and recreate to change the partial index condition
26
27-- Drop old partial indexes that exclude deleted comments
28DROP INDEX IF EXISTS idx_comments_root;
29DROP INDEX IF EXISTS idx_comments_parent;
30DROP INDEX IF EXISTS idx_comments_parent_score;
31DROP INDEX IF EXISTS idx_comments_uri_active;
32
33-- Recreate indexes without the deleted_at filter (include all comments for threading)
34CREATE INDEX idx_comments_root ON comments(root_uri, created_at DESC);
35CREATE INDEX idx_comments_parent ON comments(parent_uri, created_at DESC);
36CREATE INDEX idx_comments_parent_score ON comments(parent_uri, score DESC, created_at DESC);
37CREATE INDEX idx_comments_uri_lookup ON comments(uri);
38
39-- Add index for querying by deletion_reason (for moderation dashboard)
40CREATE INDEX idx_comments_deleted_reason ON comments(deletion_reason, deleted_at DESC)
41WHERE deleted_at IS NOT NULL;
42
43-- Add index for querying by deleted_by (for moderation audit/filtering)
44CREATE INDEX idx_comments_deleted_by ON comments(deleted_by, deleted_at DESC)
45WHERE deleted_at IS NOT NULL;
46
47-- +goose Down
48-- Remove deletion metadata columns and restore original indexes
49
50DROP INDEX IF EXISTS idx_comments_deleted_by;
51DROP INDEX IF EXISTS idx_comments_deleted_reason;
52DROP INDEX IF EXISTS idx_comments_uri_lookup;
53DROP INDEX IF EXISTS idx_comments_parent_score;
54DROP INDEX IF EXISTS idx_comments_parent;
55DROP INDEX IF EXISTS idx_comments_root;
56
57-- Restore original partial indexes (excluding deleted comments)
58CREATE INDEX idx_comments_root ON comments(root_uri, created_at DESC) WHERE deleted_at IS NULL;
59CREATE INDEX idx_comments_parent ON comments(parent_uri, created_at DESC) WHERE deleted_at IS NULL;
60CREATE INDEX idx_comments_parent_score ON comments(parent_uri, score DESC, created_at DESC) WHERE deleted_at IS NULL;
61CREATE INDEX idx_comments_uri_active ON comments(uri) WHERE deleted_at IS NULL;
62
63ALTER TABLE comments DROP COLUMN IF EXISTS deleted_by;
64ALTER TABLE comments DROP COLUMN IF EXISTS deletion_reason;
65
66DROP TYPE IF EXISTS deletion_reason;