group_members

Current membership as (group_id, identity_id)

What you'll learn

You're writing SQL that reads group_members and need its columns, what each holds, and the DDL as the spec states it.

every column and its type
what the object holds and who writes it
the DDL as the spec states it

A view. Current membership as (group_id, identity_id)

Columns

group_id, identity_id.

Definition

From spec/ledger.sql, which is the machine-checkable form of SPEC.md §3:

-- Current membership: the newest added/removed event wins per (group,
-- identity) — the ADR-003 append-then-derive pattern. touched events never
-- affect membership; they are the delivery-history trail suppression windows
-- read (SPEC §8).
CREATE VIEW group_members AS
SELECT group_id, identity_id
FROM (
  SELECT group_id, identity_id, event,
         ROW_NUMBER() OVER (
           PARTITION BY group_id, identity_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM group_events
  WHERE event IN ('added', 'removed')
)
WHERE rn = 1 AND event = 'added';