quilling.dev
1-- Insert post with direct actor_id and parent/root/embedded post natural keys
2-- Self-contained schema: No records table, metadata embedded directly in posts
3-- Parameters: $1=actor_id(INT4), $2=cid, $3=content(BYTEA, compressed),
4-- $4=languages(text[], up to 3), $5=tags,
5-- $6=parent_post_actor_id, $7=parent_post_rkey,
6-- $8=root_post_actor_id, $9=root_post_rkey,
7-- $10=embed, $11=embed_subtype, $12=violates_threadgate, $13=rkey(INT8),
8-- $14=tokens(text[], for search)
9-- $15=ext_embed(post_ext_embed), $16=video_embed(post_video_embed),
10-- $17=embedded_post_actor_id, $18=embedded_post_rkey,
11-- $19=record_detached(BOOL),
12-- $20-$23=image_1, image_2, image_3, image_4 (post_image_embed),
13-- $24-$31=facet_1 through facet_8 (post_facet_embed),
14-- $32=mentions(int[])
15-- Note: created_at is derived from TID rkey
16-- Actor must already exist before calling this function
17-- Parent and root posts must exist before calling this function (use ensure_post_exists first)
18WITH embed_types AS (
19 SELECT
20 CASE WHEN $10::text IS NOT NULL THEN $10::text::embed_type ELSE NULL::embed_type END as embed_type,
21 CASE WHEN $11::text IS NOT NULL THEN $11::text::embed_type ELSE NULL::embed_type END as embed_subtype
22)
23INSERT INTO posts (
24 actor_id, rkey, cid, content, langs, tags,
25 parent_post_actor_id, parent_post_rkey,
26 root_post_actor_id, root_post_rkey,
27 embed_type, embed_subtype,
28 violates_threadgate, status, tokens,
29 ext_embed, video_embed,
30 embedded_post_actor_id, embedded_post_rkey,
31 record_detached,
32 image_1, image_2, image_3, image_4,
33 facet_1, facet_2, facet_3, facet_4, facet_5, facet_6, facet_7, facet_8,
34 mentions
35)
36SELECT
37 $1, -- actor_id (passed directly as INT4)
38 $13, -- rkey (INT8)
39 $2::bytea, -- cid (embedded)
40 $3, -- content (compressed BYTEA)
41 -- Convert text array to language_code array (supports up to 3 languages per AT Protocol spec)
42 -- Only include valid language codes that can be cast to the enum
43 COALESCE(
44 (SELECT array_agg(lang::language_code ORDER BY ordinality)
45 FROM unnest($4::text[]) WITH ORDINALITY AS t(lang, ordinality)
46 WHERE lang IS NOT NULL
47 AND lang::text IN (SELECT enumlabel FROM pg_enum WHERE enumtypid = 'language_code'::regtype)
48 LIMIT 3),
49 ARRAY[]::language_code[]
50 ), -- langs (array of up to 3 language codes)
51 $5, -- tags
52 $6, -- parent_post_actor_id
53 $7, -- parent_post_rkey
54 $8, -- root_post_actor_id
55 $9, -- root_post_rkey
56 (SELECT embed_type FROM embed_types), -- embed_type
57 (SELECT embed_subtype FROM embed_types), -- embed_subtype
58 $12, -- violates_threadgate
59 'complete'::post_status, -- status (this is a complete post with full content)
60 $14, -- tokens (text array, pre-tokenized in Rust)
61 $15, -- ext_embed
62 $16, -- video_embed
63 $17, -- embedded_post_actor_id
64 $18, -- embedded_post_rkey
65 $19, -- record_detached
66 $20, $21, $22, $23, -- image_1, image_2, image_3, image_4
67 $24, $25, $26, $27, $28, $29, $30, $31, -- facet_1 through facet_8
68 $32 -- mentions
69-- Posts are immutable - cannot be edited in AT Protocol
70-- However, if a stub/missing post already exists, upgrade it to complete
71-- (missing posts can become complete if we later receive the actual data)
72ON CONFLICT (actor_id, rkey) DO UPDATE
73SET cid = EXCLUDED.cid, -- Ensure CID is correct (stubs may have placeholder)
74 content = EXCLUDED.content,
75 langs = EXCLUDED.langs,
76 tags = EXCLUDED.tags,
77 parent_post_actor_id = EXCLUDED.parent_post_actor_id,
78 parent_post_rkey = EXCLUDED.parent_post_rkey,
79 root_post_actor_id = EXCLUDED.root_post_actor_id,
80 root_post_rkey = EXCLUDED.root_post_rkey,
81 embed_type = EXCLUDED.embed_type,
82 embed_subtype = EXCLUDED.embed_subtype,
83 violates_threadgate = EXCLUDED.violates_threadgate,
84 status = 'complete'::post_status,
85 tokens = EXCLUDED.tokens, -- Update tokens when upgrading from stub/missing
86 ext_embed = EXCLUDED.ext_embed,
87 video_embed = EXCLUDED.video_embed,
88 embedded_post_actor_id = EXCLUDED.embedded_post_actor_id,
89 embedded_post_rkey = EXCLUDED.embedded_post_rkey,
90 record_detached = EXCLUDED.record_detached,
91 image_1 = EXCLUDED.image_1,
92 image_2 = EXCLUDED.image_2,
93 image_3 = EXCLUDED.image_3,
94 image_4 = EXCLUDED.image_4,
95 facet_1 = EXCLUDED.facet_1,
96 facet_2 = EXCLUDED.facet_2,
97 facet_3 = EXCLUDED.facet_3,
98 facet_4 = EXCLUDED.facet_4,
99 facet_5 = EXCLUDED.facet_5,
100 facet_6 = EXCLUDED.facet_6,
101 facet_7 = EXCLUDED.facet_7,
102 facet_8 = EXCLUDED.facet_8,
103 mentions = EXCLUDED.mentions
104WHERE posts.status IN ('stub'::post_status, 'missing'::post_status)
105RETURNING actor_id, rkey