# ADR 051: Relational Notification Events and Subtypes

- HTML version: https://robbiepalmer.me/projects/recipe-site/adrs/051-relational-notification-events
- Project: Recipe Site (https://robbiepalmer.me/projects/recipe-site.md)
- Status: Accepted
- Date: 2026-07-15

# Summary

Model in-app notifications as generic events and per-recipient deliveries, with relational subtype
tables for domain-specific data and renderer/action registries for rich notification behaviour.

# Context

The first notification types describe household invitations and membership changes, but the planned
system also includes recipe forks, cooking-log comments, upstream recipe updates, recommendations,
and other activity. Those types have different data, links, visual treatments, and actions.

Putting household IDs, invitation IDs, and household names on a single notification table makes the
generic archive household-specific and creates increasingly sparse columns as new domains arrive.
A JSON payload would avoid widening the table, but it would move important relationships and
validation outside PostgreSQL.

# Decision

Use class-table inheritance:

* `notification_event` records the kind, actor reference, actor-name snapshot, and occurrence time
* `notification_delivery` records the recipient's read and dismissed state
* `notification_household_event` stores household-specific references and historical display data
* `notification_household_invitation_event` adds the invitation reference only for invitation events

```mermaid
erDiagram
USER {
text id PK
}
ORGANIZATION {
text id PK
}
INVITATION {
text id PK
}
NOTIFICATION_EVENT {
text id PK
text kind
text actor_user_id FK "nullable"
text actor_name_snapshot "nullable"
timestamp occurred_at
}
NOTIFICATION_DELIVERY {
text id PK
text event_id FK
text recipient_user_id FK
timestamp read_at "nullable"
timestamp dismissed_at "nullable"
}
NOTIFICATION_HOUSEHOLD_EVENT {
text event_id PK, FK
text household_id FK "nullable"
text household_name_snapshot
}
NOTIFICATION_HOUSEHOLD_INVITATION_EVENT {
text event_id PK, FK
text invitation_id FK "nullable"
}

USER o|--o{ NOTIFICATION_EVENT : acts_in
USER ||--o{ NOTIFICATION_DELIVERY : receives
NOTIFICATION_EVENT ||--o{ NOTIFICATION_DELIVERY : fans_out_as
NOTIFICATION_EVENT ||--o| NOTIFICATION_HOUSEHOLD_EVENT : specializes_as
NOTIFICATION_HOUSEHOLD_EVENT ||--o| NOTIFICATION_HOUSEHOLD_INVITATION_EVENT : adds_invitation_context
ORGANIZATION o|--o{ NOTIFICATION_HOUSEHOLD_EVENT : describes
INVITATION o|--o{ NOTIFICATION_HOUSEHOLD_INVITATION_EVENT : references
```

Each subtype primary key is also a foreign key to its immediate parent: household subtypes reference
`notification_event`, while invitation subtypes reference `notification_household_event`. This
enforces at most one row at each level and ensures invitation context cannot exist without household
context. The unique `(event_id, recipient_user_id)` index prevents the same event being delivered to
one recipient twice.

Foreign keys to mutable or deletable domain records use `ON DELETE SET NULL`, while snapshots retain
the text required to explain historical activity. One event can have multiple deliveries, so a
household deletion is recorded once even when several members are notified.

The API pages generic deliveries and events first, groups event IDs by represented subtype, and runs
one hydration query per subtype family. It does not issue one query per notification.

Interactive notifications use `POST /notifications/:deliveryId/actions/:actionKey`. The server
checks that the delivery belongs to the current user, dispatches by event kind, and returns the
rehydrated notification. Each frontend subtype renderer owns its rich content, links, and available
actions; the archive shell only owns shared list behaviour.

## Integrity Across Subtype Tables

PostgreSQL constraints can be inter-table. The model uses foreign keys to enforce that deliveries
and subtype rows reference real events, users, households, and invitations. Primary keys, unique
indexes, and `ON DELETE` actions enforce the other local cardinality and lifecycle rules shown in
the diagram.

The invitation subtype's foreign key already ensures it has household context. The remaining
awkward rule is bidirectional: for example, a `household_invited` event must have exactly one
household subtype row and exactly one invitation subtype row, while a future recipe-comment event
must not have either. Foreign keys enforce child-to-parent existence, but not the reverse
parent-to-required-child existence. PostgreSQL `CHECK` constraints only inspect the row being
written and cannot contain a query that checks another table. PostgreSQL also does not implement
the SQL-standard database-wide `ASSERTION` feature.

There are database-level ways to enforce this stronger rule. A deferred constraint trigger could
inspect the event and all subtype tables at transaction commit. Repeating `kind` in subtype tables,
then using composite foreign keys and local checks, could enforce parts of the rule, but would still
not make the parent event require a child row. Both approaches add duplicated state or trigger
complexity to every subtype.

For this first slice, the application creates an event, all required subtype rows, and its
deliveries together through one transactional notification factory. Tests exercise that factory.
This is a deliberate integrity boundary, not a claim that PostgreSQL cannot enforce cross-table
rules. If another service or manual writer starts creating notifications, deferred constraint
triggers should be reconsidered so the database protects itself from every writer.

## Event Lifetime After Delivery Deletion

The event is the shared parent and deliveries are its per-recipient children. `ON DELETE CASCADE`
therefore works from event to deliveries: deleting an event removes all of its deliveries. Deleting
one delivery does not cascade upwards because other recipients may still reference the same event,
and a foreign key cannot treat “this happened to be the last child” as an instruction to delete its
parent.

Normal dismiss and clear operations do not delete deliveries; they set `dismissed_at`, retaining the
archive record. Hard deletion can still happen through retention work or when a recipient account
is deleted. If every delivery for an event is eventually hard-deleted, the event and its subtype
rows remain valid but no longer appear in anyone's archive. “Orphaned” here means delivery-less,
not referentially broken.

For example, a household invitation initially has one delivery to its invitee. If that invitee
deletes their account, the recipient foreign key cascades away the delivery, but nothing deletes
the shared event parent. The same can happen to an owner-only “member left” notification when the
owner later deletes their account.

That may be desirable for a retention or audit window. If it is not, a periodic transaction can
delete events for which no delivery exists, cascading into the subtype rows. Immediate upward
cascading is intentionally avoided because it complicates multi-recipient writes and concurrent
deletes, and it would make retention policy an implicit side effect of deleting one recipient's
state.

## Persisted Notifications Versus Persisted Actions

The notifications are persisted. What is not persisted is the definition of their actions. Today
the database stores the facts and state, while application code derives behaviour:

| Persisted today                                 | Derived from code today                            |
| ----------------------------------------------- | -------------------------------------------------- |
| Event kind, actor snapshot, and occurrence time | Which action keys a kind supports                  |
| Recipient, read state, and dismissed state      | Whether an action is currently available           |
| Household and invitation references/snapshots   | Labels, ordering, renderer, and server handler     |
| Invitation status in the domain table           | The action result and refreshed notification shape |

For example, an invitation notification persists the invitation reference. The API emits `accept`
and `decline` only while that invitation is pending, and a code registry maps those keys to domain
operations. This already supports notifications with no actions, links, or different action sets;
it does not require an actions table.

Persisted action entities would become useful only if action definitions must be authored or
changed at runtime without deploying code—for example, an administrator-defined rule chooses the
label, order, policy, or workflow attached to a particular event. One possible extension would be:

```mermaid
erDiagram
NOTIFICATION_EVENT {
text id PK
text kind
}
NOTIFICATION_ACTION_DEFINITION {
text id PK
text key
text label
text handler_type
}
NOTIFICATION_EVENT_ACTION {
text event_id PK, FK
text action_definition_id PK, FK
integer position
timestamp available_until "nullable"
}

NOTIFICATION_EVENT ||--o{ NOTIFICATION_EVENT_ACTION : offers
NOTIFICATION_ACTION_DEFINITION ||--o{ NOTIFICATION_EVENT_ACTION : instantiates
```

Even then, executable handlers and authorization would remain trusted server code; storing an
arbitrary callback or URL from configuration would be unsafe. This extension is unnecessary while
product-defined notification types and actions ship with the application. A rules engine would not
automatically require one row per offered action either: persisted rule definitions could still
derive the available actions at read time. The diagram is one possible runtime-authored model, not
a requirement for configurable behaviour.

# Consequences

## Positive

* Domain foreign keys and constraints remain relational.
* Generic delivery state stays independent of notification content.
* One event can fan out to several recipients without duplicating event data.
* New rich types add subtype tables, hydration, an action handler when needed, and a renderer without
  changing the archive shell.
* Deleted actors and targets do not erase the historical explanation shown to recipients.
* Batched subtype hydration avoids N+1 queries.

## Negative

* Each new subtype family requires a migration and explicit hydration code.
* Reading a heterogeneous page takes more than one query.
* Matching an event kind to all of its required subtype rows relies on the transactional write path;
  stricter database enforcement would require deferred constraint triggers or a more redundant
  schema.
* Delivery-less events may need retention-aware cleanup if hard-deleted deliveries make their volume
  material.

# When To Revisit

* Notification volume makes the joined occurrence-time ordering or batched hydration measurably slow.
* A large number of types have truly schema-less, non-queryable payloads where relational subtypes no
  longer provide useful integrity.
* Multiple delivery channels need channel-specific state beyond the current in-app delivery record.
* Runtime-authored action rules, rather than product-defined actions shipped in code, justify
  reconsidering persisted action definitions and event-action assignments.

---

Markdown index of this site: https://robbiepalmer.me/llms.txt