Data model

The portal stores everything in one Cloudflare D1 (SQLite) database, binding DB, and archives raw Stripe payloads in one R2 bucket, binding FILES. This page is the single description of both. Rules built on top of these tables are in Product primitives.

Conventions

• The schema comes from two migrations applied in order: migrations/0001_init.sql (all tables) and migrations/0002_checkout_sessions.sql (one column on users). 0001 starts with PRAGMA foreign_keys = ON.
• Ids are UUID strings from crypto.randomUUID(), except sessions.token_hash and stripe_events.event_id.
• Timestamps are ISO-8601 UTC strings (TEXT, from Date#toISOString). Stripe's epoch-second periods are converted to this format when stored.
• Types in the tables are the SQLite declared types; there is no ORM. Row types in TypeScript live in src/env.ts.

Tables

users

One row per family account. Written by signup (src/index.ts); open_checkout_session_id by checkout and the webhook.

Column	Type	Meaning
id	TEXT primary key	The user id; also the Stripe client_reference_id and metadata.user_id.
email	TEXT NOT NULL UNIQUE	Login identity, stored trimmed and lowercased.
name	TEXT NOT NULL	Display name.
password_hash	TEXT NOT NULL	Base64 of the 256-bit PBKDF2-SHA-256 output (100,000 iterations).
password_salt	TEXT NOT NULL	Base64 of the 16 random salt bytes.
created_at	TEXT NOT NULL	Signup time.
open_checkout_session_id	TEXT (nullable; added by 0002_checkout_sessions.sql)	The id (cs_...) of the Stripe Checkout Session most recently created for this user. POST /api/billing/checkout expires it if still open before creating a new one; checkout.session.completed and checkout.session.expired clear it.

sessions

Server-side logins. Written by signup/login, deleted by logout, expiry and cleanup.

Column	Type	Meaning
token_hash	TEXT primary key	Base64 SHA-256 of the session token. The token itself exists only in the ra_session cookie.
user_id	TEXT NOT NULL → users(id) ON DELETE CASCADE	Owner.
expires_at	TEXT NOT NULL	Creation time plus 30 days. A session with expires_at <= now is not valid.
created_at	TEXT NOT NULL	Creation time.

Indexes: sessions_user_id_idx (user_id), sessions_expires_at_idx (expires_at).

inmates

The incarcerated person. Written by PUT /api/inmate; connection_* by the webhook and by Hermes acknowledgements.

Column	Type	Meaning
id	TEXT primary key	Inmate row id (not the inmate number).
user_id	TEXT NOT NULL UNIQUE → users(id) ON DELETE CASCADE	Owning account. UNIQUE makes it one inmate per account.
inmate_number	TEXT NOT NULL	The identifier Securus knows. Not unique across accounts.
first_name	TEXT NOT NULL	Trimmed.
last_name	TEXT NOT NULL	Trimmed.
state	TEXT NOT NULL	Two uppercase letters.
facility	TEXT NOT NULL	Trimmed free text.
connection_status	TEXT NOT NULL DEFAULT 'not_connected', CHECK in not_connected, pending, connected, failed	Hand-off state to Hermes; see the transitions in Product primitives.
connection_detail	TEXT nullable	Reason text from Hermes (failed acknowledgements), shown in the console. Cleared whenever the status changes to anything but connected through the webhook.
created_at	TEXT NOT NULL
updated_at	TEXT NOT NULL

Index: inmates_number_idx (inmate_number). The index is deliberately not unique: two accounts may enter the same number, so checkout refuses a second live subscriber (409 inmate_taken) and Hermes usage is attributed through the inmate's live subscription.

subscriptions

The account's Stripe subscription. Written only by the Stripe webhook.

Column	Type	Meaning
id	TEXT primary key	Local id; reused when the account's row is updated.
user_id	TEXT NOT NULL UNIQUE → users(id) ON DELETE CASCADE	One subscription row per account.
inmate_id	TEXT → inmates(id) ON DELETE SET NULL	The account's inmate when the webhook last ran.
stripe_customer_id	TEXT nullable	Stripe customer (cus_...); needed for the billing portal. Never overwritten by NULL.
stripe_subscription_id	TEXT UNIQUE nullable	Stripe subscription (sub_...). A different id for the same account is treated as a duplicate and not stored.
plan_id	TEXT NOT NULL	monthly, quarterly or annual, derived from the Stripe price.
status	TEXT NOT NULL (no CHECK)	active, trialing, past_due, canceled or incomplete.
current_period_start	TEXT nullable	Stripe billing period start; the anchor of the monthly windows.
current_period_end	TEXT nullable	Stripe billing period end (exposed as billing_period_end).
cancel_at_period_end	INTEGER NOT NULL DEFAULT 0	1 when Stripe will cancel at period end.
created_at	TEXT NOT NULL
updated_at	TEXT NOT NULL	Used to pick the newest subscription when attributing usage.

Indexes: subscriptions_status_idx (status), subscriptions_stripe_customer_idx (stripe_customer_id).

connections

The hand-off record Hermes lists and acknowledges. The webhook keeps one current row per subscription and updates it in place (inserting only if none exists).

Column	Type	Meaning
id	TEXT primary key	The connection_id exposed to Hermes.
subscription_id	TEXT NOT NULL → subscriptions(id) ON DELETE CASCADE
inmate_id	TEXT NOT NULL → inmates(id) ON DELETE CASCADE
status	TEXT NOT NULL, CHECK in not_connected, pending, connected, failed	Mirrors the inmate's connection_status.
detail	TEXT nullable	Hermes detail, kept while connected, cleared otherwise.
period_start	TEXT nullable	Stripe billing period start (not the monthly window; the Hermes API derives the window on read).
period_end	TEXT nullable	Stripe billing period end.
created_at	TEXT NOT NULL	Pending connections are listed oldest first.
updated_at	TEXT NOT NULL

Constraint: UNIQUE(subscription_id, period_start, period_end). Indexes: connections_status_idx (status), connections_inmate_idx (inmate_id).

usage_events

One row per message Hermes reports. Written only by POST /api/hermes/usage.

Column	Type	Meaning
id	TEXT primary key
inmate_id	TEXT NOT NULL → inmates(id) ON DELETE CASCADE	Chosen through the inmate's live subscription.
message_id	TEXT NOT NULL UNIQUE	Opaque idempotency key; the bridge sends <ledger_entry_id>:<message_id>.
kind	TEXT NOT NULL, CHECK in reply, snap, videogram
stamps	REAL NOT NULL, CHECK (stamps >= 0)	Stamps spent; fractional values are allowed (a ledger entry's stamps are split across its messages).
occurred_at	TEXT NOT NULL	When the message was sent; decides which monthly window counts it.
created_at	TEXT NOT NULL	When the portal stored it.

Index: usage_events_inmate_occurred_idx (inmate_id, occurred_at).

stripe_events

Idempotency ledger for the webhook.

Column	Type	Meaning
event_id	TEXT primary key	Stripe event id (evt_...). Inserted with INSERT OR IGNORE before processing; a replay finds it and answers duplicate: true. If processing throws, the row is deleted so Stripe's retry runs again.
event_type	TEXT NOT NULL	For example customer.subscription.updated.
received_at	TEXT NOT NULL

Relationships

users 1---1 inmates
users 1---1 subscriptions ---* connections *---1 inmates
users 1---* sessions
inmates 1---* usage_events
stripe_events (standalone idempotency ledger)

R2 bucket reform-assistant-files

Two key prefixes, both written by src/stripe_webhook.ts, both application/json. Nothing else reads them; they exist for audit and replay (see the runbook).

Key	Written when	Content
stripe/events/<event_id>.json	Every signature-verified, first-seen event, before it is processed. A retry after a failed attempt overwrites the same key.	The raw request body exactly as Stripe sent it.
stripe/duplicates/<subscription_id>.json	A subscription event that must not change D1: a second Stripe subscription for an account that already has a live one, or one for an inmate another account has live. Updated by later events for that subscription.	A JSON record: user_id, stripe_subscription_id, event_type, customer_id, subscription (the Stripe object, merged across events), recorded_at, cancellation_requested, canceled, and after a cancellation attempt cancellation_status and cancellation_response. With STRIPE_SECRET_KEY set the webhook cancels the duplicate in Stripe and records the outcome; without it nothing is cancelled.

Raw Stripe payloads and cancellation_response contain customer data: restrict bucket access to the Worker and operators.