Multi-Agent Collaboration for Team and Friends
TypeScript agent-automationagent-orchestrationagent-workflowai-agentsai-collaborationai-memoryai-workforceautonomous-agentsclaude-codeclaude-code-skillscodexcoding-agentmulti-agentmultiple-agentone-person-businessone-person-companyone-person-teamopencodesolopreneur
-- phase2 forum≡thread, step5: migrate every existing forum_post channel into
-- "a message in its forum + a thread opened on that message" — the exact
-- shape the send-into-forum path (step3) produces going forward. A
-- forum_post's `display_title` becomes the content of a brand-new opener
-- message landing in the forum itself; the forum_post channel row then
-- type-flips to `thread`, rooted on that new opener via `parent_message_id`.
--
-- TYPE-FLIP, not new-thread+move-messages: the forum_post row's `id` is kept
-- (only its `type`/`parent_message_id` change) so any existing reference to
-- that channel id stays pointed at the same row. Its existing M messages
-- (opener body + replies) are untouched — their `channel_id` already points
-- at this row's id, which now simply IS a thread, so they become the
-- thread's messages with zero rows rewritten.
--
-- The live send path's message insert hardcodes `created_at = now` with no
-- historical-timestamp override, which would forge every migrated message's
-- time as "whenever this migration ran" — real history corruption. That path
-- is NOT used here. Only ONE new row needs inventing at all (the opener), and
-- this migration mints it directly with the forum_post's OWN creation
-- time/creator as the opener's created_at/author_id: display_title was
-- itself written at that moment, so that timestamp is the correct one for
-- "when was this title given," not "whenever this backfill executed."
--
-- Deterministic id `'mig_opener_' || <forum_post channel id>` (not a random
-- id) so step 4 below can point `parent_message_id` at the exact row step 1
-- inserted for THIS forum_post, with no content/timestamp-based re-lookup
-- that could ambiguously match more than one row.
--
-- IDEMPOTENT ACROSS RE-RUNS: every step's WHERE clause keys off
-- `type = 'forum_post'`. Step 4 flips that type away, so re-running this same
-- migration a second time matches zero rows in every step — a safe no-op,
-- not a double-insert.
--
-- EXCLUDED (deliberately, pending a separate decision): forum_post rows with
-- `creator_id IS NULL` (the row's creator account was hard-deleted after
-- posting). `community_message.author_id` is NOT NULL, so no opener message
-- can be minted for one of these without inventing an author that never
-- existed — the same class of historical-data forgery the created_at fix
-- above exists to avoid. Left as `forum_post` untouched; a live scan
-- determines whether this case has any real rows before it needs an answer.
--
-- M=0 orphans (a forum_post with zero messages — a pre-existing, non-atomic
-- artifact of the old create path, unrelated to this migration) fall out of
-- these SAME statements with no special-case branch: step 1 still inserts
-- the new opener from display_title and step 4 still flips the row to
-- `type='thread'` rooted on that opener — the result is one opener message +
-- an empty thread, an entirely ordinary state (any message can open a thread
-- nobody has replied to yet).
--
-- TAGS BACKFILL (step 6 below): a post's `forum_tags` JSON array moves onto
-- the SAME new opener message (tags are an opener-message property under the
-- collapsed model, per Aigneis #696/#853 — not a channel/thread column) as
-- rows in the new `community_message_tag` table. Never-drop: a tag the user
-- actually set must survive this migration, so this backfill rides the SAME
-- commit as the type-flip rather than a separate migration file — the old
-- `forum_tags` column cannot be dropped until this has run.
-- 1. One new opener message per forum_post, landing in the forum (the parent
-- channel) — matches the live send path's model: an opener lives in the
-- addressed channel, the thread is a separate row. `seq` is assigned
-- directly against the target forum's current counter via ROW_NUMBER,
-- safe without CAS/atomicity because migrations run single-threaded
-- offline, unlike the live path's concurrent-writer claim.
INSERT INTO community_message (
id, author_id, content, type, mention_type, reply_to_id, embeds,
created_at, channel_id, seq, friendship_id, client_nonce
)
SELECT
'mig_opener_' || fp.id,
fp.creator_id,
COALESCE(fp.display_title, ''),
'default',
NULL, NULL, NULL,
fp.created_at,
fp.parent_channel_id,
COALESCE((SELECT s.next_seq FROM community_message_seq s WHERE s.channel_id = fp.parent_channel_id), 0)
+ ROW_NUMBER() OVER (PARTITION BY fp.parent_channel_id ORDER BY fp.created_at, fp.id),
NULL, NULL
FROM community_channel AS fp
WHERE fp.type = 'forum_post' AND fp.creator_id IS NOT NULL;
-- 2. Advance each affected forum's seq counter by however many openers step 1
-- just claimed for it, so the next REAL send into that forum claims a
-- fresh seq with no collision — mirrors claimNextSeq's own upsert shape.
-- `excluded.next_seq` is the per-forum opener COUNT this SELECT computed
-- (the value that would have been inserted), not the pre-existing row's
-- value — SQLite's standard UPSERT reference for exactly this case.
INSERT INTO community_message_seq (channel_id, next_seq)
SELECT
fp.parent_channel_id,
COUNT(*)
FROM community_channel AS fp
WHERE fp.type = 'forum_post' AND fp.creator_id IS NOT NULL
GROUP BY fp.parent_channel_id
ON CONFLICT(channel_id) DO UPDATE SET
next_seq = next_seq + excluded.next_seq;
-- 3. Bump each affected forum's own message_count/last_message_at for the new
-- opener(s), the same bookkeeping a live message insert does.
-- last_message_at only ADVANCES, never regresses: a historical opener's
-- created_at could predate the forum's current last_message_at if the
-- forum has had newer, unrelated activity since. SQLite's multi-arg
-- scalar max() returns NULL if EITHER argument is NULL, so a forum with
-- no prior last_message_at (NULL) must be COALESCEd to the new value
-- directly rather than compared against NULL.
UPDATE community_channel
SET
message_count = message_count + (
SELECT COUNT(*) FROM community_channel fp
WHERE fp.type = 'forum_post' AND fp.parent_channel_id = community_channel.id AND fp.creator_id IS NOT NULL
),
last_message_at = CASE
WHEN last_message_at IS NULL THEN (
SELECT MAX(fp.created_at) FROM community_channel fp
WHERE fp.type = 'forum_post' AND fp.parent_channel_id = community_channel.id AND fp.creator_id IS NOT NULL
)
ELSE MAX(
last_message_at,
(SELECT MAX(fp.created_at) FROM community_channel fp
WHERE fp.type = 'forum_post' AND fp.parent_channel_id = community_channel.id AND fp.creator_id IS NOT NULL)
)
END
WHERE id IN (
SELECT DISTINCT parent_channel_id FROM community_channel
WHERE type = 'forum_post' AND creator_id IS NOT NULL
);
-- 4. Type-flip: the forum_post row itself becomes the thread, rooted on the
-- opener message step 1 minted for it (deterministic id, no ambiguity).
-- `parent_channel_id` is left untouched — already points at the forum,
-- exactly where a natively-opened thread's parent_channel_id would point.
--
-- `name` MUST also be rederived here, not left as the old forum_post
-- slug: createMessageWithThread's live path (create-channels.ts) sets a
-- thread's `name` from the opener content, trimmed and truncated to
-- MAX_CHANNEL_NAME_LENGTH (100) — NOT from any slug. A migrated thread
-- that kept its old slug in `name` would be a structural difference from
-- a natively-created thread (which never has a slug-shaped name), not
-- merely an old-vs-fresh content difference — exactly what the
-- migration-equivalence requirement forbids. TRIM+CASE mirrors the live
-- path's `trim() || "thread"` fallback (an empty/whitespace-only
-- display_title falls back to the literal "thread", never an empty
-- string); SUBSTR(...,1,100) mirrors its `.slice(0, MAX_CHANNEL_NAME_LENGTH)`.
UPDATE community_channel
SET
type = 'thread',
parent_message_id = 'mig_opener_' || id,
name = SUBSTR(
CASE WHEN TRIM(COALESCE(display_title, '')) = '' THEN 'thread' ELSE TRIM(display_title) END,
1, 100
)
WHERE type = 'forum_post' AND creator_id IS NOT NULL;
-- 5. Enroll participants on each newly-flipped thread — enroll is independent
-- social data; it does not appear automatically just because the row's
-- type changed. As of migration 0067, the notify/participant set lives on
-- `community_channel_member` with `relation='notify'` (the standalone
-- community_thread_participant table from 0058/0060/0061 no longer
-- exists — folded into this table). Seeded the same way 0060/0061 seeded
-- pre-existing threads/posts: distinct message authors ("spoke") + the
-- row's own creator (may not have posted, but always "spoke" the opener
-- here). INSERT OR IGNORE against uq_channel_member(channel_id, user_id,
-- relation), so re-running is a no-op (also naturally satisfied by step 4
-- already having flipped the type away on any second run).
INSERT OR IGNORE INTO community_channel_member (id, channel_id, user_id, relation, source, added_by, added_at)
SELECT DISTINCT
lower(hex(randomblob(16))),
t.id,
msg.author_id,
'notify',
'spoke',
NULL,
t.created_at
FROM community_channel AS t
JOIN community_message AS msg ON msg.channel_id = t.id
WHERE t.type = 'thread' AND t.parent_message_id = 'mig_opener_' || t.id;
INSERT OR IGNORE INTO community_channel_member (id, channel_id, user_id, relation, source, added_by, added_at)
SELECT
lower(hex(randomblob(16))),
t.id,
t.creator_id,
'notify',
'spoke',
NULL,
t.created_at
FROM community_channel AS t
WHERE t.type = 'thread' AND t.creator_id IS NOT NULL AND t.parent_message_id = 'mig_opener_' || t.id;
-- 6. Tags backfill: expand each migrated post's `forum_tags` JSON array (one
-- string per element) into `community_message_tag` rows on the new opener
-- message step 1 minted for it. `json_each` is SQLite core (no extension/
-- migration needed to enable it), reading directly off the now-flipped
-- `community_channel.type = 'thread'` rows this same commit just produced
-- — so this step must run AFTER step 4 above, keyed on the SAME
-- `parent_message_id = 'mig_opener_' || id` identity used to build the
-- enroll rows. `json_each.value` for a JSON array of strings yields the
-- unquoted string directly (SQLite decodes the JSON scalar), so no extra
-- unquoting/parsing is needed here. `id` reuses the same random-blob
-- shape as `community_message_tag.id` elsewhere (no natural key).
--
-- ⚠ The NULL/empty/malformed filter MUST live in the inner subquery, NOT
-- a WHERE on the outer join: `json_each(t.forum_tags)` is a table-valued
-- function evaluated per OUTER ROW before any outer-level WHERE narrows
-- the set, so a NULL/''/non-array argument throws "malformed JSON" for
-- that row regardless of an outer filter that would have excluded it —
-- verified against a real sqlite3 binary (a naive outer-WHERE version of
-- this statement aborted the whole INSERT on the very first tag-less
-- row). Restricting rows to json_each's input in a subquery FIRST means
-- json_each only ever sees rows already known to be a non-empty string.
-- No known malformed (non-JSON-array) `forum_tags` values exist (the live
-- read path has always parsed this same column successfully), so the
-- remaining case — a malformed value slipping through the inner filter —
-- is accepted as a hard migration-abort rather than silently swallowed
-- like the live path's `safeParseForumTags` fallback.
INSERT OR IGNORE INTO community_message_tag (id, message_id, tag)
SELECT
lower(hex(randomblob(16))),
'mig_opener_' || tagged.id,
je.value
FROM (
SELECT id, forum_tags FROM community_channel
WHERE type = 'thread' AND parent_message_id = 'mig_opener_' || id
AND forum_tags IS NOT NULL AND forum_tags != '' AND forum_tags != '[]'
) AS tagged
JOIN json_each(tagged.forum_tags) AS je;