quilling.dev
1-- Update profile in consolidated actors table
2-- SCHEMA CHANGE: profiles table dropped, data now in actors.profile_* columns
3-- Parameters: $1=actor_id, $2=cid(bytea), $3=avatar_cid, $4=banner_cid, $5=display_name, $6=description,
4-- $7=pinned_uri, $8=joined_sp_uri, $9=pronouns, $10=website, $11=created_at
5-- NOTE: actor_id is provided by dispatcher after ensuring actor exists
6-- Note: No JOIN to posts table - we already have the natural key from URI parsing
7WITH pinned_lookup AS (
8 SELECT tid_to_i64(SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 3)) as rkey
9 FROM actors a
10 WHERE $7::text IS NOT NULL
11 AND a.did = SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 1)
12 AND SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 2) = 'app.bsky.feed.post' -- Only parse post URIs
13),
14joined_sp_lookup AS (
15 SELECT sp.id
16 FROM actors a
17 INNER JOIN starterpacks sp ON sp.actor_id = a.id
18 WHERE $8::text IS NOT NULL
19 AND a.did = SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 1)
20 AND SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 2) = 'app.bsky.graph.starterpack' -- Only parse starterpack URIs
21 AND sp.rkey = tid_to_i64(SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 3))
22)
23UPDATE actors SET
24 profile_cid = $2::bytea,
25 profile_created_at = $11,
26 profile_avatar_cid = $3,
27 profile_banner_cid = $4,
28 profile_display_name = $5,
29 profile_description = $6,
30 profile_pinned_post_rkey = (SELECT rkey FROM pinned_lookup),
31 profile_joined_sp_id = (SELECT id FROM joined_sp_lookup),
32 profile_pronouns = $9,
33 profile_website = $10
34WHERE id = $1