mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-17 07:09:53 +02:00
1de4b2d688
#5307 renamed 161_attachment_task_id -> 164 (and 162_..._index -> 165) to
resolve a prefix collision. schema_migrations keys on the full stem, so any DB
that applied the migration under its old 161 number does not have "164" in the
ledger and the runner re-applies the renamed file. The bare `ADD COLUMN task_id`
then aborts with 42701 ("column already exists"), blocking every later
migration — this is exactly what crashed the dev deploy of main (bf288349f) at
container startup, before it ever reached 166_project_dates.
Add `IF NOT EXISTS` so the re-run is a harmless no-op on already-migrated DBs
(dev/staging/prod) with no manual schema_migrations surgery, while staying
identical on a fresh DB. Sibling 165 already uses CREATE INDEX ... IF NOT EXISTS
for the same reason; this brings 164 in line. The down file already uses
DROP COLUMN IF EXISTS.
Verified on a throwaway DB reproducing the dev drift (forget 164/165 in the
ledger, keep the column+index): old file reproduces the 42701 crash, the fixed
file self-heals and completes. Migration lint + concurrent migrate tests pass.
Refs #5307
Co-authored-by: J <j@multica.ai>
Co-authored-by: multica-agent <github@multica.ai>
31 lines
2.0 KiB
SQL
31 lines
2.0 KiB
SQL
-- Task-scoped attachment ownership. An agent producing an image/file for a
|
|
-- chat reply uploads it during the run tagged with the producing task; on task
|
|
-- completion the server binds the task's still-unclaimed rows to the assistant
|
|
-- chat_message it synthesizes (see BindChatAttachmentsToMessage).
|
|
--
|
|
-- task_id is a TRANSIENT binding handle, not a durable relationship: it is
|
|
-- written once at upload (against a task the upload handler has already
|
|
-- validated) and read only during that task's own completion. The durable
|
|
-- owner is chat_message_id. We deliberately add NO foreign key here:
|
|
-- - No referential integrity is needed — the sole writer validates the task
|
|
-- before setting the column, so a dangling/garbage value cannot get in.
|
|
-- - No cascade cleanup is needed — orphan uploads (task_id set,
|
|
-- chat_message_id NULL) are already reaped when their chat_session is
|
|
-- deleted, via attachment.chat_session_id's own ON DELETE CASCADE. There is
|
|
-- no app-layer path that hard-deletes agent_task_queue rows, so an
|
|
-- ON DELETE action here would never fire in practice; adding an FK on the
|
|
-- hot attachment table would only add write overhead and a cascade
|
|
-- dependency the app does not rely on.
|
|
-- IF NOT EXISTS makes this self-heal on databases that applied it under its
|
|
-- pre-#5307 number (161). The renumber to 164 changed the schema_migrations
|
|
-- key, so the runner re-applies the renamed file on those DBs; without the
|
|
-- guard the bare ADD COLUMN aborts with 42701 ("column already exists") and
|
|
-- blocks every later migration (GH #5307's note; the dev deploy that hit it).
|
|
-- Sibling 165 already uses CREATE INDEX ... IF NOT EXISTS for the same reason.
|
|
ALTER TABLE attachment
|
|
ADD COLUMN IF NOT EXISTS task_id UUID;
|
|
|
|
-- The task_id lookup index is built CONCURRENTLY in the next migration.
|
|
-- CREATE INDEX CONCURRENTLY cannot share a transaction or multi-command
|
|
-- string with the ADD COLUMN above (see 138_issue_title_trgm_index).
|