ADR-018: Notify external-ID column projection

Formerly docs/architecture/ADR-NOTIFY-EXTERNAL-ID-COLUMNS.md.

FieldValue
StatusAccepted
Date2026-04-29
SprintSPRINT_20260429_002_Notify_ack_bridge_durability (archived)
Drift ticketDRIFT-NOTIFIER-ACKBRIDGE-001
AuthorsNotify guild

For: engineers working on the Stella Ops Notify ack-bridge and anyone diagnosing why an inbound PagerDuty/OpsGenie acknowledgement fails to resolve its incident after a database restart. This ADR records why external provider identifiers are persisted as first-class columns on notify.deliveries rather than read out of JSON metadata at lookup time.

Context

Inbound acknowledgement webhooks from PagerDuty and OpsGenie carry an external identifier (PagerDuty dedup_key, OpsGenie alias) and need to resolve to the matching tenant + incident inside Stella Ops so the ack-bridge can transition the escalation state. The resolver (PostgresAckExternalIdResolver) issues a SQL join against notify.deliveries and notify.channels:

SELECT delivery.tenant_id, delivery.event_payload, delivery.id, delivery.created_at
  FROM notify.deliveries delivery
  JOIN notify.channels   channel
    ON channel.id        = delivery.channel_id
   AND channel.tenant_id = delivery.tenant_id
 WHERE delivery.external_id = @externalId
   AND channel.channel_type = @expectedType
 ORDER BY delivery.created_at DESC
 LIMIT 8;

The bug captured in DRIFT-NOTIFIER-ACKBRIDGE-001 was that the durable persistence mapper (NotifyPersistenceModelMapper.ToDeliveryEntity) never copied Metadata["externalId"] into DeliveryEntity.ExternalId. The external_id column stayed NULL forever; the value lived only inside event_payload.metadata.externalId (JSONB). The resolver’s join therefore returned no rows after a Postgres restart or a connection-pool drop — the moment the in-process cache that previously held a column-shaped mapping went away. The originally-failing test AckBridge_ResolvesPagerDutyExternalIdFromPostgresAfterRestart was tagged Skip until this fix landed.

Decision

Promote externalId and channelId to first-class persisted columns on notify.deliveries rather than continuing to address them via JSON metadata keys.

Specifically:

  1. NotifyDelivery (the canonical model) gains nullable ExternalId and ChannelId properties. The constructor backfills these from Metadata["externalId"] / Metadata["channel"] so existing callers that pass the values via metadata keep working — but the column gets populated either way.
  2. NotifyPersistenceModelMapper.ToDeliveryEntity writes entity.ExternalId = delivery.ExternalId. The previous code path left that column NULL; this is the root cause fix.
  3. The persistence layer (DeliveryRepository.UpsertAsync) already projects external_id to the column via @p_external_id with COALESCE(EXCLUDED.external_id, notify.deliveries.external_id) semantics on conflict, so the column write is preserved across upserts.
  4. Migration 009_deliveries_external_id_columns.sql (idempotent, embedded resource):
    • Re-asserts the columns exist (ADD COLUMN IF NOT EXISTS).
    • Backfills external_id from event_payload -> 'metadata' ->> 'externalId' for in-flight rows.
    • Adds the partial composite index idx_notify_deliveries_external_id (channel_id, external_id) WHERE external_id IS NOT NULL for the resolver join.
  5. The resolver retains a metadata-fallback path for one release cycle to handle rows written by an older mapper that the migration UPDATE missed (zero-downtime rollouts where rows are written by an older node after the migration ran). Hits are tracked via the notifier_ack_bridge_metadata_fallback_total counter so operators can see when the fallback can be deleted.

Alternatives considered

A. Keep the JSON metadata path; fix the bug at read time

Teach the resolver to read delivery.event_payload -> 'metadata' ->> 'externalId' directly instead of the column.

Rejected:

B. Reverse the projection: keep externalId only as a column, drop it from metadata

Strip Metadata["externalId"] and Metadata["channel"] from NotifyDelivery on every write.

Deferred (not rejected): This is the correct end state, but doing it in the same change as the column-write fix risks breaking external consumers (UI, CLI, exports) that may still read those metadata keys. The follow-up sprint SPRINT_20260429_021_Notifier_ack_bridge_remove_metadata_fallback.md is gated on the notifier_ack_bridge_metadata_fallback_total counter staying at zero for at least 7 consecutive days post-deployment of one full release cycle that includes migration 009.

C. Store the mapping in a sidecar table

Introduce a dedicated notify.delivery_external_ids table.

Rejected:

Consequences

Positive

Negative

Neutral

Verification

Cross-references