Core data model

Core’s schema is small on purpose. It holds who the members are, their points, the one-time codes that move them between touchpoints, the check-ins, and who on staff may use the admin console. Bookings, rewards and campaigns belong to partners and are not stored here.

Domains

flowchart LR
  classDef ours fill:#13686C,stroke:#0B4A4D,color:#ffffff
  classDef area fill:#E3F4F3,stroke:#13686C,color:#12191B

  subgraph members["Members"]
    customer["customer"]:::ours
  end
  subgraph access["Access and handoff"]
    qr["qr"]:::area
    check_in["check_in"]:::area
    handoff["handoff"]:::area
  end
  subgraph points["Points ledger"]
    points_account["points_account"]:::area
    points_txn["points_txn"]:::area
    points_entry["points_entry"]:::area
    points_event["points_event"]:::area
  end
  subgraph staffing["Staff access"]
    staff["staff"]:::area
    staff_event["staff_event"]:::area
  end

  customer --> qr --> check_in
  customer --> handoff
  customer --> points_account
  customer --> points_txn --> points_entry
  points_txn --> points_event
  staff --> staff_event
  staff -. "posts adjustments" .-> points_txn

Entity-relationship diagram

The whole schema at a glance: ten tables, and how they relate.

erDiagram
  CUSTOMER ||--o{ QR : "is issued"
  QR |o--o{ CHECK_IN : "scanned as"
  CUSTOMER ||--o{ HANDOFF : "is issued"
  CUSTOMER ||--o| POINTS_ACCOUNT : "holds"
  CUSTOMER ||--o{ POINTS_TXN : "receives"
  POINTS_TXN ||--|{ POINTS_ENTRY : "balanced by"
  POINTS_ACCOUNT ||--o{ POINTS_ENTRY : "moves"
  POINTS_TXN |o--o| POINTS_EVENT : "announced as"
  STAFF |o--o{ POINTS_TXN : "adjusts"
  STAFF ||--o{ STAFF_EVENT : "audited by"

Members, passes and codes

erDiagram
  CUSTOMER ||--o{ QR : "is issued"
  QR |o--o{ CHECK_IN : "scanned as"
  CUSTOMER |o--o{ CHECK_IN : "checks in"
  CUSTOMER ||--o{ HANDOFF : "is issued"

  CUSTOMER {
    bigint id PK
    varchar keycloak_sub UK "Keycloak subject"
    uuid ext_id UK "the external id partners see"
    varchar display_name "copy of Keycloak"
    varchar email "copy of Keycloak"
    timestamptz keycloak_deleted_at
  }
  QR {
    uuid id PK
    bigint customer_id FK
    bytea token_hash UK "SHA-256 only"
    timestamptz expires_at "issue + 60 s"
    timestamptz used_at "single use"
    varchar used_gate
  }
  CHECK_IN {
    bigint id PK
    uuid qr_id
    bigint customer_id
    varchar gate_id
    varchar result "GRANTED, DENIED"
    varchar reason "EXPIRED, USED, UNKNOWN, MALFORMED"
    varchar source "ONLINE, OFFLINE"
    timestamptz scanned_at
  }
  HANDOFF {
    uuid id PK
    bigint customer_id FK
    varchar audience "BOOKING, WIDGET"
    bytea code_hash UK
    varchar keycloak_sid "for sign-out"
    timestamptz expires_at "issue + 60 s"
    timestamptz used_at "single use"
  }

Points ledger

erDiagram
  CUSTOMER ||--o| POINTS_ACCOUNT : "holds"
  CUSTOMER ||--o{ POINTS_TXN : "receives"
  POINTS_TXN ||--|{ POINTS_ENTRY : "balanced by"
  POINTS_ACCOUNT ||--o{ POINTS_ENTRY : "moves"
  POINTS_TXN |o--o| POINTS_TXN : "refunds"
  POINTS_TXN |o--o| POINTS_EVENT : "announced as"

  POINTS_ACCOUNT {
    bigint id PK
    varchar kind "CUSTOMER, ISSUED, REDEEMED"
    bigint customer_id FK
    bigint balance ">= 0"
    bigint seq
  }
  POINTS_TXN {
    uuid id PK
    varchar kind "CREDIT, DEBIT, REFUND"
    bigint customer_id FK
    varchar client "PARTNER, STAFF, CORE"
    bigint staff_id
    varchar idempotency_key
    varchar reason
    varchar reference
    uuid refund_of FK
    date business_date
  }
  POINTS_ENTRY {
    bigint id PK
    uuid txn_id FK
    bigint account_id FK
    bigint amount
    bigint balance_after
  }
  POINTS_EVENT {
    bigint id PK
    xid8 xid
    varchar type
    bigint customer_id
    uuid txn_id
  }

Staff access

erDiagram
  STAFF ||--o{ STAFF_EVENT : "audited by"
  STAFF |o--o{ POINTS_TXN : "adjusts"

  STAFF {
    bigint id PK
    varchar email UK
    varchar role "STAFF, ADMIN"
    boolean active
    varchar jumpcloud_sub UK "linked at first sign-in"
    int version "optimistic lock"
  }
  STAFF_EVENT {
    bigint id PK
    bigint staff_id
    varchar action "CREATED, UPDATED, DELETED, LINKED, UNLINKED, BOOTSTRAPPED"
    jsonb changes
    varchar actor_email
  }

Key fields only. Some links are deliberately not foreign keys: audit and check-in history must outlive what they point to.

The tables

Table	Purpose	Notes
customer	One row per Keycloak user in realm finns	Created by Keycloak sync or on the first app call. ext_id is the external id, the only id given to the engagement partner. Deleted accounts keep their row, marked deleted, so the ledger still adds up.
qr	One-time check-in passes	60-second lifetime, single use, stored only as a hash.
check_in	Every gate scan, granted or denied	source = OFFLINE is reserved for gates that work without a connection.
handoff	One-time codes for the booking site and widgets	Bound to one audience. Booking codes carry name and email until used; widget codes carry no personal data. Purged an hour after expiry.
points_account	Balances	One per customer, plus two system accounts, ISSUED and REDEEMED.
points_txn	One posting	Records who posted (client, staff_id), why (reason, reference) and the idempotency key.
points_entry	The two sides of a posting	Always two entries summing to zero.
points_event	Outbound feed	Written in the same transaction as each change; partners poll it in commit order.
staff	Who may use the admin console, and with which role	Matched by email until first sign-in links the JumpCloud account.
staff_event	Audit of staff access changes	No foreign key, so history survives a deleted staff member.

Rules the database enforces

Application bugs can’t break these, because PostgreSQL itself refuses the write.

Append-only ledger

Triggers reject any UPDATE or DELETE on transactions and entries. Mistakes are corrected with a new posting, never by editing history.

Every posting balances

A deferred constraint trigger checks at commit that each transaction’s entries sum to zero.

No negative balances

A CHECK keeps every customer balance at or above zero; a debit that would overdraw fails as insufficient_points.

Exactly once

Idempotency keys are unique per client (unique nulls not distinct), single-use codes are consumed by one conditional UPDATE, and a refund can happen only once per transaction.

Not in core, by design

There are no tiers, rewards catalogue, redemption records, bookings, venues or payments in core’s schema. Rewards and redemptions live with the engagement partner; bookings and their payment with the booking engine. Tiers, venues and stored credit are open design topics; see Roadmap.