A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- +goose StatementBegin
3
4-- Migration: Rename community handles from .communities. to .community.
5-- This aligns with AT Protocol lexicon naming conventions (singular form)
6--
7-- Background:
8-- All atProto record types use singular form (app.bsky.feed.post, app.bsky.graph.follow)
9-- This migration updates existing community handles to follow the same pattern
10--
11-- Examples:
12-- gardening.communities.coves.social -> gardening.community.coves.social
13-- gaming.communities.coves.social -> gaming.community.coves.social
14--
15-- Safety: Uses REPLACE which only affects handles containing '.communities.'
16-- Rollback: Available via down migration below
17
18-- Update community handles in the communities table
19UPDATE communities
20SET handle = REPLACE(handle, '.communities.', '.community.')
21WHERE handle LIKE '%.communities.%';
22
23-- Verify the migration (optional - can be commented out in production)
24-- This will fail if any .communities. handles remain
25DO $$
26DECLARE
27 old_format_count INTEGER;
28BEGIN
29 SELECT COUNT(*) INTO old_format_count
30 FROM communities
31 WHERE handle LIKE '%.communities.%';
32
33 IF old_format_count > 0 THEN
34 RAISE EXCEPTION 'Migration incomplete: % communities still have .communities. format', old_format_count;
35 END IF;
36
37 RAISE NOTICE 'Migration successful: All community handles updated to .community. format';
38END $$;
39
40-- +goose StatementEnd
41
42-- +goose Down
43-- +goose StatementBegin
44
45-- Rollback: Revert handles from .community. back to .communities.
46-- Only use this if you need to rollback the naming convention change
47
48UPDATE communities
49SET handle = REPLACE(handle, '.community.', '.communities.')
50WHERE handle LIKE '%.community.%';
51
52-- Verify the rollback
53DO $$
54DECLARE
55 new_format_count INTEGER;
56BEGIN
57 SELECT COUNT(*) INTO new_format_count
58 FROM communities
59 WHERE handle LIKE '%.community.%';
60
61 IF new_format_count > 0 THEN
62 RAISE EXCEPTION 'Rollback incomplete: % communities still have .community. format', new_format_count;
63 END IF;
64
65 RAISE NOTICE 'Rollback successful: All community handles reverted to .communities. format';
66END $$;
67
68-- +goose StatementEnd