A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Remove foreign key constraint on votes.voter_did to prevent race conditions
3-- between user and vote Jetstream consumers.
4--
5-- Rationale:
6-- - Vote events can arrive before user events in Jetstream
7-- - Creating votes should not fail if user hasn't been indexed yet
8-- - Users are validated at the PDS level (votes come from user repos)
9-- - Orphaned votes (from deleted users) are harmless and can be ignored in queries
10
11ALTER TABLE votes DROP CONSTRAINT IF EXISTS fk_voter;
12
13-- Add check constraint to ensure voter_did is a valid DID format
14ALTER TABLE votes ADD CONSTRAINT chk_voter_did_format
15CHECK (voter_did ~ '^did:(plc|web|key):');
16
17-- +goose Down
18-- Restore foreign key constraint (note: this may fail if orphaned votes exist)
19ALTER TABLE votes DROP CONSTRAINT IF EXISTS chk_voter_did_format;
20
21ALTER TABLE votes ADD CONSTRAINT fk_voter
22FOREIGN KEY (voter_did) REFERENCES users(did) ON DELETE CASCADE;