Runbook: Add messages.temp_id Column

This migration adds the temp_id column and its partial unique index to communications.messages. It is required for the outbound messaging feature — without it, idempotency enforcement does not exist and the persistence layer cannot store the client-side tempId.

When to run

Before deploying the build that includes outbound messaging (comunications-outbound-iter2).

The migration is ADDITIVE and NON-DESTRUCTIVE:

• Adds a nullable text column — all existing rows get NULL, which is valid.
• Adds a partial unique index that excludes NULL values — inbound messages (which have no tempId) are unaffected.

No existing data is modified or deleted.

Step 1 — Generate the migration

pnpm --filter api migration:generate communications/AddMessageTempIdColumn

This command reads the TypeORM entity diff and generates a timestamped migration file at:

apps/api/src/modules/communications/infrastructure/migrations/<timestamp>-AddMessageTempIdColumn.ts

Step 2 — Review the generated SQL

The expected SQL is:

ALTER TABLE communications.messages ADD COLUMN temp_id text NULL;

CREATE UNIQUE INDEX uq_messages_conversation_temp_id
  ON communications.messages (conversation_id, temp_id)
  WHERE temp_id IS NOT NULL;

Verify:

• No NOT NULL constraint (would fail on existing rows).
• The index has WHERE temp_id IS NOT NULL (partial — inbound message rows must not conflict).
• No other unexpected changes (only these two statements should be present).

Step 3 — Apply the migration

pnpm --filter api migration:run

Verify the migration completes without errors.

Step 4 — Verify

-- Connect to the database and confirm:
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'communications'
  AND table_name   = 'messages'
  AND column_name  = 'temp_id';
-- Expected: column_name=temp_id, data_type=text, is_nullable=YES

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'communications'
  AND tablename  = 'messages'
  AND indexname  = 'uq_messages_conversation_temp_id';
-- Expected: partial unique index with WHERE (temp_id IS NOT NULL)

Rollback

If a rollback is needed:

DROP INDEX IF EXISTS communications.uq_messages_conversation_temp_id;
ALTER TABLE communications.messages DROP COLUMN IF EXISTS temp_id;

This is safe — the column and index are only used by the outbound messaging feature.

Notes

• Migration generation is a developer/operator task. It is NOT automated by the CI pipeline.
• The TypeORM entity (MessagePersistence) already includes the @Column decorator for tempId. The migration will reflect this diff.
• The partial unique index is NOT expressible via TypeORM's @Unique decorator alone — verify that the generated migration correctly includes the WHERE clause before applying.