quilling.dev
1-- Insert list_item with natural keys (no synthetic list_id)
2-- This prevents foreign key violations when indexing list items
3-- Parameters: $1=actor_id(INT4), $2=subject_actor_id(i32), $3=cid(bytea), $4=rkey,
4-- $5=list_owner_actor_id(INT4), $6=list_rkey(TEXT)
5-- Note: created_at is derived from TID rkey
6-- Note: actor_id is provided by caller after calling get_actor_id (ensures zero sequence waste)
7INSERT INTO list_items (actor_id, rkey, cid, list_owner_actor_id, list_rkey, subject_actor_id)
8SELECT
9 $1, -- actor_id (provided by caller)
10 $4, -- rkey (INT8)
11 $3, -- cid (embedded, already bytea)
12 $5, -- list_owner_actor_id (already resolved, may be NULL)
13 $6, -- list_rkey (already resolved, may be NULL)
14 $2::int4 -- subject_actor_id (already resolved, cast to int4)
15WHERE $1::int4 IS NOT NULL -- Only insert if owner exists
16 AND $2::int4 IS NOT NULL -- Only insert if subject exists
17ON CONFLICT (actor_id, rkey) DO UPDATE SET
18 cid=EXCLUDED.cid,
19 list_owner_actor_id=EXCLUDED.list_owner_actor_id,
20 list_rkey=EXCLUDED.list_rkey,
21 subject_actor_id=EXCLUDED.subject_actor_id