quilling.dev
1-- Insert/update starterpack with self-contained schema (no records table)
2-- Parameters: $1=actor_id(INT4), $2=cid(bytea), $3=record(unused), $4=name, $5=description,
3-- $6=description_facets, $7=list_actor_id(INT4), $8=list_rkey(TEXT),
4-- $9=feed_actor_ids(INT4[]), $10=feed_rkeys(TEXT[]), $11=rkey(INT8)
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)
7-- NOTE: URI parsing and feedgen natural key resolution now done in Rust
8WITH unused_params AS (
9 SELECT $3::jsonb as record -- Type hint for unused parameter
10),
11upserted_starterpack AS (
12 INSERT INTO starterpacks (actor_id, rkey, cid, owner_actor_id, name, description, description_facets, list_actor_id, list_rkey, status)
13 SELECT
14 $1, -- actor_id (provided by caller)
15 $11, -- rkey (INT8)
16 $2::bytea, -- cid (embedded)
17 $1, -- owner_actor_id (same as actor_id for starterpacks)
18 $4,
19 $5,
20 $6,
21 $7, -- list_actor_id (already resolved, may be stub)
22 $8, -- list_rkey (already resolved, may be stub)
23 'complete'::record_status
24 ON CONFLICT (actor_id, rkey) DO UPDATE SET
25 cid=EXCLUDED.cid,
26 name=EXCLUDED.name,
27 description=EXCLUDED.description,
28 description_facets=EXCLUDED.description_facets,
29 list_actor_id=EXCLUDED.list_actor_id,
30 list_rkey=EXCLUDED.list_rkey,
31 status=EXCLUDED.status
32 RETURNING rkey, XMAX::text::int as xmax_result
33),
34-- Delete old feeds before inserting new ones (only if starterpack was actually inserted)
35deleted_feeds AS (
36 DELETE FROM starterpack_feeds
37 WHERE starterpack_id = (SELECT rkey FROM upserted_starterpack)
38 AND EXISTS (SELECT 1 FROM upserted_starterpack)
39),
40-- Insert new feeds from resolved feedgen natural keys
41-- Feeds are passed as two parallel arrays: feed_actor_ids and feed_rkeys
42inserted_feeds AS (
43 INSERT INTO starterpack_feeds (starterpack_id, feed_actor_id, feed_rkey, position)
44 SELECT
45 (SELECT rkey FROM upserted_starterpack),
46 feed_actor_ids[idx],
47 feed_rkeys[idx],
48 (idx - 1)::smallint as position
49 FROM generate_series(1, COALESCE(array_length($9::integer[], 1), 0)) AS idx,
50 LATERAL (SELECT $9::integer[] AS feed_actor_ids, $10::text[] AS feed_rkeys) AS arrays
51 WHERE $9 IS NOT NULL AND $10 IS NOT NULL
52 AND EXISTS (SELECT 1 FROM upserted_starterpack)
53 ON CONFLICT DO NOTHING
54)
55SELECT xmax_result FROM upserted_starterpack