Unified Schema Reference

All data synced from connected providers is stored in a normalised PostgreSQL schema. This document covers the entity relationships, table definitions, and field-level documentation.

---

Entity-Relationship Diagram

erDiagram
    tenants {
        uuid tenant_id PK
        text name
        text plan
        timestamptz created_at
    }

    api_keys {
        uuid key_id PK
        text key_hash
        uuid tenant_id FK
        text name
        text[] scopes
        timestamptz created_at
        timestamptz last_used_at
        timestamptz revoked_at
    }

    provider_connections {
        uuid connection_id PK
        uuid tenant_id FK
        text provider
        text status
        timestamptz last_synced_at
        jsonb credentials
        int events_count
    }

    events {
        uuid event_id PK
        uuid tenant_id FK
        text provider
        text provider_event_id
        text name
        text description
        text venue
        timestamptz start_date
        timestamptz end_date
        int capacity
        text status
        timestamptz created_at
    }

    ticket_types {
        uuid ticket_type_id PK
        uuid event_id FK
        uuid tenant_id FK
        text name
        int price_cents
        text currency
        int capacity
    }

    ticket_sales {
        uuid sale_id PK
        uuid tenant_id FK
        uuid event_id FK
        uuid ticket_type_id FK
        text provider
        int quantity
        int gross_cents
        int net_cents
        int fees_cents
        text currency
        timestamptz sold_at
        text purchase_channel
    }

    attendees {
        uuid attendee_id PK
        uuid tenant_id FK
        uuid event_id FK
        uuid ticket_type_id FK
        text country_code
        text purchase_channel
        timestamptz checked_in_at
        timestamptz registered_at
    }

    tenants ||--o{ api_keys : "owns"
    tenants ||--o{ provider_connections : "has"
    tenants ||--o{ events : "has"
    events ||--o{ ticket_types : "has"
    events ||--o{ ticket_sales : "has"
    events ||--o{ attendees : "has"
    ticket_types ||--o{ ticket_sales : "typed by"
    ticket_types ||--o{ attendees : "typed by"

(ASCII fallback below for environments without Mermaid support)

tenants
  ├── api_keys          (1:N)
  ├── provider_connections (1:N)
  └── events            (1:N)
        ├── ticket_types (1:N)
        ├── ticket_sales (1:N)  ──── ticket_type_id → ticket_types
        └── attendees    (1:N)  ──── ticket_type_id → ticket_types

---

Table definitions

tenants

Top-level account isolation. Every row in every other table carries a tenant_id that scopes queries.

Column	Type	Nullable	Example	Description
tenant_id	uuid	No	f47ac10b-...	Primary key, generated by gen_random_uuid()
name	text	No	Lumière Events SARL	Display name of the organisation
plan	text	No	pro	One of free, pro, business
created_at	timestamptz	No	2025-03-01T09:00:00Z	Account creation timestamp

---

api_keys

API keys used by the MCP server and REST adapter for authentication.

Column	Type	Nullable	Example	Description
key_id	uuid	No	a3f1...	Primary key
key_hash	text	No	sha256:abc...	SHA-256 of the raw key — raw key is never stored
tenant_id	uuid	No	f47ac10b-...	Owner tenant
name	text	Yes	Claude Desktop key	Human-readable label
scopes	text[]	No	{read}	Permission scopes (currently read only)
created_at	timestamptz	No	2025-06-01T10:00:00Z	Creation timestamp
last_used_at	timestamptz	Yes	2025-08-01T15:42:00Z	Updated on every successful auth
revoked_at	timestamptz	Yes	null	Set when key is revoked; null = active

Index: api_keys_hash_idx on (key_hash) WHERE revoked_at IS NULL — fast auth lookup.

---

provider_connections

One row per (tenant, provider) pair. Stores connection status and encrypted credentials.

Column	Type	Nullable	Example	Description
connection_id	uuid	No	b1c2...	Primary key
tenant_id	uuid	No	f47ac10b-...	Owner tenant
provider	text	No	eventbrite	Provider identifier (see Connector Reference)
status	text	No	connected	One of connected, syncing, error
last_synced_at	timestamptz	Yes	2025-08-01T10:30:00Z	Timestamp of last successful sync
credentials	jsonb	No	{"access_token": "..."}	Encrypted provider credentials (AES-256-CBC) — never readable in plaintext from the DB
events_count	integer	No	42	Cached count of synced events

Constraint: UNIQUE (tenant_id, provider) — one connection per provider per tenant.

---

sync_logs

One row per sync run. Stores coarse run status plus structured field-coverage details for provider-specific enrichments.

Column	Type	Nullable	Example	Description
log_id	uuid	No	ab12...	Primary key
tenant_id	uuid	No	f47ac10b-...	Owner tenant
provider_id	text	No	weezevent	Provider identifier
connection_id	uuid	No	b1c2...	FK-like reference to the synced connection
started_at	timestamptz	No	2026-05-31T10:00:00Z	Sync start timestamp
completed_at	timestamptz	Yes	2026-05-31T10:01:42Z	Sync finish timestamp
records_synced	integer	Yes	487	Total rows upserted during the run
status	text	No	success	One of running, success, error
error	text	Yes	null	Top-level sync failure message when the run errors
details	jsonb	No	{"field_coverage":{"payment_method":{"populated":42,"skipped":3}}}	Structured per-field counts, stage outcomes, incremental cursor, and provider-gap notes

---

events

Normalised event records, one row per provider event per tenant.

Column	Type	Nullable	Example	Description
event_id	uuid	No	c3d4...	Spekta-internal primary key
tenant_id	uuid	No	f47ac10b-...	Owner tenant
provider	text	No	eventbrite	Source provider
provider_event_id	text	No	123456789	Provider's own event ID
name	text	No	Summer Beats Festival	Event name
description	text	Yes	Annual music festival...	Event description (may be long)
venue	text	Yes	Parc de la Villette, Paris	Venue name or address string
start_date	timestamptz	No	2025-08-15T18:00:00Z	Event start (stored in UTC)
end_date	timestamptz	Yes	2025-08-16T02:00:00Z	Event end (nullable for open-ended)
capacity	integer	Yes	5000	Total capacity (null if not set by provider)
status	text	No	upcoming	One of upcoming, live, past, cancelled
created_at	timestamptz	No	2025-03-01T09:00:00Z	Record insertion time

Constraint: UNIQUE (tenant_id, provider, provider_event_id) — deduplication on upsert.
Indexes:

• events_tenant_start on (tenant_id, start_date) — range queries
• events_tenant_status on (tenant_id, status) — status filter

Extended fields (migration 002_extend_pivot_schema.sql)

Column	Type	Nullable	Description
city	text	Yes	Event city, usually sourced from provider venue metadata
country_code	text	Yes	Provider country / country code (default FR)
category	text	Yes	Normalised event category when the provider exposes one
expected_capacity	integer	Yes	Organiser target capacity, distinct from hard capacity
online_event	boolean	No	Whether the event is virtual / online-only

---

ticket_types

Ticket categories within an event (e.g. "Early Bird", "VIP", "General Admission").

Column	Type	Nullable	Example	Description
ticket_type_id	uuid	No	d5e6...	Primary key
event_id	uuid	No	c3d4...	Parent event
tenant_id	uuid	No	f47ac10b-...	Owner tenant (denormalised for fast tenant-scoped queries)
name	text	No	VIP	Ticket type name
price_cents	integer	No	8500	Face value in minor currency units (e.g. 8500 = €85.00)
currency	text	No	EUR	ISO 4217 currency code
capacity	integer	Yes	200	Allocation for this tier (null = unlimited)
sale_start	timestamptz	Yes	2025-05-01T09:00:00Z	When this ticket tier goes on sale
sale_end	timestamptz	Yes	2025-08-14T23:59:59Z	When this ticket tier stops selling
is_early_bird	boolean	No	true	Flag for early-bird tiers
is_vip	boolean	No	true	Flag for VIP / premium tiers
sort_order	integer	No	20	Provider ordering / display order within the event

Indexes:

• ticket_types_event on (event_id).
• ticket_types_event_sort_idx on (event_id, sort_order).

---

ticket_sales

Individual sale records. One row = one transaction line (may cover multiple tickets via quantity).

Column	Type	Nullable	Example	Description
sale_id	uuid	No	e7f8...	Primary key
tenant_id	uuid	No	f47ac10b-...	Owner tenant
event_id	uuid	No	c3d4...	Parent event
ticket_type_id	uuid	Yes	d5e6...	Ticket type (null if provider does not return type info)
provider	text	No	weezevent	Source provider
quantity	integer	No	2	Number of tickets in this transaction
gross_cents	integer	No	17000	Gross revenue in minor units (includes fees)
net_cents	integer	No	15300	Net revenue (after provider fees)
fees_cents	integer	No	1700	Provider fee component
currency	text	No	EUR	ISO 4217 currency code
sold_at	timestamptz	No	2025-07-01T14:22:00Z	Sale timestamp (UTC)
purchase_channel	text	Yes	web	e.g. web, app, box_office
refund_status	text	No	partial	One of none, partial, full
refunded_at	timestamptz	Yes	2025-07-02T10:15:00Z	When the provider reports the refund happened
refund_amount_cents	integer	No	8500	Refunded amount in minor units
promo_code_id	uuid	Yes	f1a2...	FK to promo_codes when a code was applied
utm_source	text	Yes	instagram	Marketing source tag
utm_medium	text	Yes	paid_social	Marketing medium tag
utm_campaign	text	Yes	summer_launch	Marketing campaign tag
payment_method	text	Yes	card	One of card, bank_transfer, cash, free, other

Indexes:

• ticket_sales_tenant_event on (tenant_id, event_id, sold_at) — per-event time-series
• ticket_sales_tenant_date on (tenant_id, sold_at) — cross-event date range
• ticket_sales_event_date on (event_id, sold_at) — event-level analytics
• ticket_sales_promo_code_idx on (promo_code_id) — joins to promo-code analytics
• ticket_sales_refund_status_idx on (tenant_id, event_id, refund_status, sold_at) — refund analysis

---

attendees

Per-attendee records. No PII is stored — names and emails are never persisted. Demographic data is limited to anonymised fields.

Column	Type	Nullable	Example	Description
attendee_id	uuid	No	g9h0...	Primary key
tenant_id	uuid	No	f47ac10b-...	Owner tenant
event_id	uuid	No	c3d4...	Parent event
ticket_type_id	uuid	Yes	d5e6...	Ticket type
country_code	text	Yes	FR	ISO 3166-1 alpha-2 country code
purchase_channel	text	Yes	app	Purchase channel
checked_in_at	timestamptz	Yes	2025-08-15T19:04:00Z	Check-in time (null = not checked in)
registered_at	timestamptz	Yes	2025-06-15T11:30:00Z	Registration timestamp

---

promo_codes

Discount and promo-code definitions, scoped by tenant and optionally by event.

Column	Type	Nullable	Description
promo_code_id	uuid	No	Primary key
tenant_id	uuid	No	Owner tenant
event_id	uuid	Yes	Event scope; null means all events
code	text	No	The promo code entered by the buyer
discount_type	text	No	percent or fixed_cents
discount_value	integer	No	Percent value or cents value, depending on discount_type
max_uses	integer	Yes	Usage cap (null = unlimited)
used_count	integer	No	Observed / synced uses
valid_from	timestamptz	Yes	First valid timestamp
valid_until	timestamptz	Yes	Expiry timestamp
created_at	timestamptz	No	Row creation time

Indexes:

• promo_codes_tenant_event_idx on (tenant_id, event_id).
• promo_codes_event_idx on (event_id).

---

check_in_logs

Granular scan events. Unlike attendees.checked_in_at, this table keeps each synced scan row.

Column	Type	Nullable	Description
check_in_log_id	uuid	No	Primary key
tenant_id	uuid	No	Owner tenant
event_id	uuid	No	Parent event
attendee_id	uuid	Yes	Linked attendee when one can be resolved
ticket_type_id	uuid	Yes	Linked ticket type when one can be resolved
scanned_at	timestamptz	No	Scan timestamp
gate	text	Yes	Gate / checkpoint label
scan_result	text	No	One of valid, already_used, invalid, not_found
device_id	text	Yes	Scanner / device identifier

Indexes:

• check_in_logs_tenant_event_scanned_idx on (tenant_id, event_id, scanned_at).
• check_in_logs_tenant_event_scan_result_idx on (tenant_id, event_id, scan_result).
• check_in_logs_attendee_idx on (attendee_id).
• check_in_logs_ticket_type_idx on (ticket_type_id).

---

waitlist

Demand capture for sold-out or gated ticket tiers.

Column	Type	Nullable	Description
waitlist_id	uuid	No	Primary key
tenant_id	uuid	No	Owner tenant
event_id	uuid	No	Parent event
ticket_type_id	uuid	Yes	Ticket tier the visitor joined for
joined_at	timestamptz	No	Waitlist join timestamp
converted	boolean	No	Whether the waitlisted buyer eventually converted
converted_at	timestamptz	Yes	Conversion timestamp

Indexes:

• waitlist_tenant_event_idx on (tenant_id, event_id).
• waitlist_ticket_type_idx on (ticket_type_id).

---

mv_daily_sales

Materialized daily sales aggregate used for fast rollups by date, event, and ticket type.

Column	Type	Description
tenant_id	uuid	Owner tenant
event_id	uuid	Parent event
sale_date	date	UTC sale date derived from ticket_sales.sold_at
ticket_type_id	uuid	Ticket type
orders	bigint	Count of synced sale rows
tickets_sold	bigint	Sum of quantity
gross_cents	bigint	Sum of gross revenue
net_cents	bigint	Sum of net revenue
refunded_qty	bigint	Quantity associated with refunded sales
refunded_cents	bigint	Sum of refunded minor units

Index: mv_daily_sales_tenant_event_date_ticket_idx on (tenant_id, event_id, sale_date, ticket_type_id).

---

Provider Field Coverage

Field family	Weezevent	Welkom	Notes
events.city, events.country_code	⚠️ Partial	✅	Weezevent event listing does not expose structured venue fields in the current connector; Welkom does.
events.category	❌	❌	Left null until a provider exposes a stable category field in the synced endpoints.
events.expected_capacity	❌	❌	Not exposed by the currently synced provider endpoints.
events.online_event	❌	❌	Defaults to false; no provider signal is mapped yet.
ticket_types.sale_start, ticket_types.sale_end	✅	❌	Weezevent /tickets exposes start_sale / end_sale; the engine persists them when present on the ticket payload.
ticket_types.is_early_bird, ticket_types.is_vip	✅ Heuristic	✅ Heuristic	Derived from ticket names when the provider does not send explicit flags.
ticket_types.sort_order	❌	✅	Welkom preserves sales-breakdown order.
ticket_sales.refund_*	✅ Partial	❌	Weezevent maps sale-level refund status plus timestamps/amounts when the participant payload exposes them; missing provider values are logged in sync_logs.details.
ticket_sales.promo_code_id + promo_codes	✅ Partial	❌	Weezevent now prefers a dedicated promo-code endpoint and falls back to participant-derived usage when that endpoint is unavailable.
ticket_sales.utm_*	❌	❌	Not exposed by the currently integrated provider endpoints.
ticket_sales.payment_method	⚠️ Partial	❌	Weezevent stores explicit payment-method fields when available and otherwise falls back to origin-based heuristics.
check_in_logs	✅ Partial	❌	Weezevent now prefers a dedicated scan-log endpoint and falls back to participant control-status scan data when that endpoint is unavailable.
waitlist	❌	❌	Table exists, but no current connector populates it.

---

Weezevent Mapping

Weezevent field / endpoint	Spekta target	Notes
GET /participant/list → status = refunded	ticket_sales.refund_status = 'full'	Also applies when refund markers resolve to a full refund.
GET /participant/list → status = partial_refund	ticket_sales.refund_status = 'partial'	Falls back to amount-based heuristics when only partial refund amounts are exposed.
GET /participant/list → other statuses / missing refund markers	ticket_sales.refund_status = 'none'	Default for non-refunded sales.
GET /participant/list → refund_date / last_update	ticket_sales.refunded_at	Uses the provider refund timestamp when present; otherwise the field remains NULL.
GET /participant/list → refund_amount / numeric refund	ticket_sales.refund_amount_cents	Amount is converted to minor units in the sync engine.
GET /events/{event_id}/promo-codes or GET /event/{event_id}/promo-codes	promo_codes table	Maps code, discount type/value, usage caps, usage counts, and validity windows.
GET /participant/list → promo_code	ticket_sales.promo_code_id	Used both as a direct sale link and as fallback promo-code discovery when the dedicated endpoint is unavailable.
GET /events/{event_id}/scannings or GET /event/{event_id}/scannings	check_in_logs table	Maps scan timestamp, gate, scan result, and device/operator identifiers.
GET /participant/list → control_status.scan_date / date_check_in	check_in_logs fallback and attendees.checked_in[_at]	Used when no dedicated scan-log endpoint is available.
GET /participant/list → payment_method / payment_type / origin	ticket_sales.payment_method	Explicit values win; origin-based heuristics backfill card, cash, bank_transfer, free, or other.
GET /participant/list → utm_source / utm_medium / utm_campaign	ticket_sales.utm_source, utm_medium, utm_campaign	Left NULL when Weezevent does not expose these fields.
GET /tickets → start_sale, end_sale	ticket_types.sale_start, ticket_types.sale_end	Ticket payload is flattened recursively across categories/sub-categories.
GET /tickets → name	ticket_types.is_early_bird, ticket_types.is_vip	Heuristic inference for early bird / prévente / tarif anticipé and vip / premium / gold / platinum.

Provider Gaps

• Weezevent’s public docs enumerate /events, /tickets, /participant/list, and scan-related endpoints, but do not document UTM fields; ticket_sales.utm_* remains NULL unless those keys appear in the payload.
• Refund timestamps and amounts are not guaranteed on every participant payload; missing values are logged as skipped in sync_logs.details.
• Promo-code and scan-log coverage can fall back to participant-derived observations if the dedicated endpoint is unavailable for the connected account.

---

Notes on data types

Money / amounts

All monetary amounts are stored as integer minor units (cents, pence, etc.). The currency column on the same row indicates the ISO 4217 code. When querying via MCP tools, amounts are returned in major units (e.g. 85.00) in the currency you specify (default EUR).

Timestamps

All timestamps are stored as timestamptz in UTC. The sync engine converts provider-native representations (UNIX seconds, UNIX milliseconds, ISO 8601 strings, local timestamps) to UTC before persisting.

Credentials encryption

provider_connections.credentials is an AES-256-CBC encrypted JSONB blob. The encryption key (ENCRYPTION_KEY env var) is never stored in the database. Without the key, the JSONB is unreadable ciphertext.