GateKeeper — Database

Table-by-table reference for the MySQL schema. Full DDL + seed data: docs/gatekeeper-schema.sql. High-level entity summary and design rationale: Architecture. Seed-data/FK issues in this schema are tracked in Known Issues.

Seven tables, MySQL 8.0.16+, InnoDB, utf8mb4.

Relationships at a glance

customers ──< api_tokens                (one customer, many tokens)
customers ──< customer_subscriptions >── packages   (many-to-many, via subscriptions)
customers ──< customer_overrides        (one customer, many overrides)
customers ──< api_request_logs >── api_tokens        (audit trail references both)

──< reads "one to many." customer_subscriptions is the join table that makes customers-to-packages many-to-many: one customer can hold several packages, one package can be sold to many customers.

---

customers

The account. Status here gates everything downstream, independently of any individual token's status. client_guid is the caller-supplied identifier — the "client-guid" half of the two-credential auth model (client-guid + secret key).

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK, internal only — never sent to or by callers
client_guid	CHAR(36)	NO	—	public identifier the caller sends on every request; unique
name	VARCHAR(200)	NO	—
contact_email	VARCHAR(200)	NO	—
status	ENUM('active','suspended','cancelled')	NO	active	checked on every request, separately from token status
created_at / updated_at	DATETIME(3)	NO	now

Indexes: PRIMARY KEY (id), UNIQUE (client_guid), KEY (status)

Sample rows

id	client_guid	name	contact_email	status
401	a1b2c3d4-0004-4a11-8b11-000000000401	CWS	CHANGE_ME_CWS_CONTACT_EMAIL	active
402	a1b2c3d4-0005-4a11-8b11-000000000402	WSB	CHANGE_ME_WSB_CONTACT_EMAIL	active

---

api_tokens

The "secret key" half of the two-credential auth model. One customer, many tokens (prod, staging, per-integration, etc.) — each independently revocable and expirable, without touching the customer record. Every request must present both this secret key and the customer's client_guid, and the two must belong together — a valid secret under the wrong client-guid is rejected.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
customer_id	BIGINT UNSIGNED	NO	—	FK → customers.id
label	VARCHAR(100)	NO	—	'prod', 'staging', 'trial'
token_hash	BINARY(32)	NO	—	SHA-256 of the raw secret key; the raw secret itself is never stored
status	ENUM('active','revoked')	NO	active
expires_at	DATETIME(3)	YES	NULL	NULL = no expiry
revoked_at	DATETIME(3)	YES	NULL
created_at	DATETIME(3)	NO	now

Indexes: PRIMARY KEY (id), UNIQUE (token_hash) — the only lookup path on the request's hot path, KEY (customer_id) — admin "list this customer's tokens"

Sample rows: none seeded — CWS/WSB have no tokens until one is minted via POST /admin/customers/{id}/tokens, since only the SHA-256 hash is ever stored and the plaintext secret only exists at issuance time.

---

packages

A named, reusable, shared entitlement bundle — the product catalog. Not tied to any one customer; many customers can subscribe to the same package.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
code	VARCHAR(100)	NO	—	unique, human-referenceable, e.g. MEETINGS_RACECARDS
name	VARCHAR(200)	NO	—	display name
is_active	TINYINT(1)	NO	1	soft-disable a package without deleting it
rules	JSON	NO	—	array of scope objects — see below

rules shape: [{ "dimension": ["value", ...] }, ...]. A missing dimension key = wildcard for that dimension. Multiple objects in the array = OR across scopes — this is why it's an array and not a single object: one package can legitimately cover several disjoint scopes.

Indexes: PRIMARY KEY (id), UNIQUE (code), CHECK (JSON_TYPE(rules) = 'ARRAY')

Sample rows — the three base product tiers, one per data_type, seeded wildcard on country/discipline (open to all). Narrow a package to specific countries/disciplines by adding values to its rule scope(s) via the Admin UI's package edit form, or grant/restrict a single customer without touching the shared package via customer_overrides.

id	code	name	rules
1	MEETINGS_RACECARDS	Meeting + Racecard	[{"data_type":["meetings","racecards"]}]
2	SPEEDMAPS	Speedmap	[{"data_type":["speedmaps"]}]
3	RESULTS	Result	[{"data_type":["results"]}]

Example of the same package narrowed to AU thoroughbred only (edited via the Admin UI, not a schema change): [{"data_type":["meetings","racecards"],"country":["AU"],"discipline":["thoroughbred"]}].

---

data_types

Admin-managed picklist backing the data_type dimension specifically (e.g. meetings, racecards, speedmaps) — a partial exception to "dimensions are just JSON keys," kept only so the Admin UI can offer a managed multi-select. No FK ties packages.rules / customer_overrides.constraints back to it, since those stay JSON — see Known Issues for the practical effect of that gap.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
code	VARCHAR(100)	NO	—	unique, immutable after creation, matches the value used in rules/constraints JSON
name	VARCHAR(200)	NO	—	display name
created_at	DATETIME(3)	NO	now

Indexes: PRIMARY KEY (id), UNIQUE (code)

---

customer_subscriptions

The join between customers and packages — this is what makes package assignment many-to-many, and time-bound. Access is the union of every currently-active row for a customer.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
customer_id	BIGINT UNSIGNED	NO	—	FK → customers.id
package_id	BIGINT UNSIGNED	NO	—	FK → packages.id
start_date	DATE	NO	—
end_date	DATE	YES	NULL	NULL = open-ended
status	ENUM('active','cancelled')	NO	active

Indexes: PRIMARY KEY (id), KEY (customer_id, status) — the resolver's main lookup, KEY (package_id) — reverse lookups ("who has package X")

Sample rows: none seeded — CWS/WSB have no packages assigned yet; assign via the Admin UI or POST /admin/customers/{id}/subscriptions once their entitlements are known.

---

customer_overrides

One-off, customer-specific exceptions — trial add-ons, temporary blocks — that don't justify a whole new package. This is the only table where revoke exists, and revokes here always win over anything granted by a package.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
customer_id	BIGINT UNSIGNED	NO	—	FK → customers.id
effect	ENUM('grant','revoke')	NO	—	revoke is checked first when resolving access
constraints	JSON	NO	—	single scope object — see below
start_date	DATE	NO	—
end_date	DATE	YES	NULL
note	VARCHAR(300)	YES	NULL	free text, e.g. "data dispute — temp block"
created_by	VARCHAR(200)	YES	NULL	which admin user made the change

constraints shape: { "dimension": ["value", ...] } — one object, not an array, because an override addresses one specific adjustment at a time (contrast with packages.rules, which is an array to allow several OR'd scopes in one reusable bundle).

Indexes: PRIMARY KEY (id), KEY (customer_id, start_date, end_date), CHECK (JSON_TYPE(constraints) = 'OBJECT')

Sample rows: none seeded — CWS/WSB have no overrides yet.

---

api_request_logs

Append-only audit trail of every request, written asynchronously in batches by AuditLogBackgroundService (never on the request's critical path — see Architecture for the pipeline). Partitioned by month.

Column	Type	Null	Default	Notes
id	BIGINT UNSIGNED	NO	auto	PK
occurred_at	DATETIME(3)	NO	—	request timestamp
customer_id	BIGINT UNSIGNED	YES	NULL	FK → customers.id; NULL for /admin/* requests (they never resolve a customer)
token_id	BIGINT UNSIGNED	YES	NULL	FK → api_tokens.id; same NULL rule as above
endpoint	VARCHAR(200)	NO	—
method	VARCHAR(10)	NO	—
status_code	SMALLINT UNSIGNED	NO	—
latency_ms	INT UNSIGNED	NO	—
client_ip	VARCHAR(45)	YES	NULL	IPv4/IPv6

Indexes: PRIMARY KEY (id), KEY (customer_id, occurred_at), KEY (occurred_at) — partition pruning

---

packages vs customer_overrides — the direct comparison

	packages	customer_overrides
Scope	Shared — one row, many customers via subscriptions	Private — one row, exactly one customer
Effect	Grant only	Grant or revoke
JSON shape	Array of scopes (OR across several)	Single scope object
Time-bound how	Not itself — the subscription linking it to a customer expires	The override row itself has start_date/end_date
Precedence in resolution	Contributes to Grants, unioned with other packages/grant-overrides	revoke checked before any grant; grant overrides join Grants same as packages
Typical use	Product/pricing tiers — "Meeting + Racecard," "Speedmap," "Result"	Exceptions — trial add-on, fraud/dispute block, one-off grant
Who edits it	Product/commercial team, infrequently	Support/ops, reactively, per incident or per sales trial

In one line: a package is what a customer bought; an override is what's different about this one customer right now, on top of or instead of what they bought.

---

Common queries

-- Active packages (with their rules) for a customer
SELECT p.code, p.rules
FROM customer_subscriptions s
JOIN packages p ON p.id = s.package_id
WHERE s.customer_id = 401
  AND s.status = 'active'
  AND s.start_date <= CURDATE()
  AND (s.end_date IS NULL OR s.end_date >= CURDATE());

-- Active overrides for a customer
SELECT effect, constraints, end_date
FROM customer_overrides
WHERE customer_id = 310
  AND start_date <= CURDATE()
  AND (end_date IS NULL OR end_date >= CURDATE());

-- Which customers currently hold a given package
SELECT c.id, c.name
FROM customer_subscriptions s
JOIN customers c ON c.id = s.customer_id
WHERE s.package_id = (SELECT id FROM packages WHERE code = 'MEETINGS_RACECARDS')
  AND s.status = 'active';

-- Client-guid + secret key resolution (the request-time lookup, pre-cache).
-- Both credentials must resolve to the SAME customer row — a valid secret
-- under the wrong client_guid returns no rows and the request is rejected.
SELECT t.customer_id, t.status AS token_status, t.expires_at, c.status AS customer_status
FROM api_tokens t
JOIN customers c ON c.id = t.customer_id
WHERE t.token_hash = UNHEX(SHA2(@raw_secret_key, 256))
  AND c.client_guid = @client_guid;

Known schema issues

See Known Issues for the seed-script FK violation (seed rows reference customer ids 101/205/310 which are never created) and the data_types FK gap noted above.