A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Change content_labels from TEXT[] to JSONB to preserve full com.atproto.label.defs#selfLabels structure
3-- This allows storing the optional 'neg' field and future extensions
4
5-- Create temporary function to convert TEXT[] to selfLabels JSONB
6-- +goose StatementBegin
7CREATE OR REPLACE FUNCTION convert_labels_to_jsonb(labels TEXT[])
8RETURNS JSONB AS $$
9BEGIN
10 IF labels IS NULL OR array_length(labels, 1) = 0 THEN
11 RETURN NULL;
12 END IF;
13
14 RETURN jsonb_build_object(
15 'values',
16 (SELECT jsonb_agg(jsonb_build_object('val', label))
17 FROM unnest(labels) AS label)
18 );
19END;
20$$ LANGUAGE plpgsql IMMUTABLE;
21-- +goose StatementEnd
22
23-- Convert column type using the function
24ALTER TABLE posts
25 ALTER COLUMN content_labels TYPE JSONB
26 USING convert_labels_to_jsonb(content_labels);
27
28-- Drop the temporary function
29DROP FUNCTION convert_labels_to_jsonb(TEXT[]);
30
31-- Update column comment
32COMMENT ON COLUMN posts.content_labels IS 'Self-applied labels per com.atproto.label.defs#selfLabels (JSONB: {"values":[{"val":"nsfw","neg":false}]})';
33
34-- +goose Down
35-- Revert JSONB back to TEXT[] (lossy - drops 'neg' field)
36ALTER TABLE posts
37 ALTER COLUMN content_labels TYPE TEXT[]
38 USING CASE
39 WHEN content_labels IS NULL THEN NULL
40 ELSE ARRAY(
41 SELECT value->>'val'
42 FROM jsonb_array_elements(content_labels->'values') AS value
43 )
44 END;
45
46-- Restore original comment
47COMMENT ON COLUMN posts.content_labels IS 'Self-applied labels (nsfw, spoiler, violence)';