Data Model

One Postgres server, one database per service, and no cross-database queries under any circumstance.

Entity Relationship Diagram

erDiagram TENANTS ||--o{ USERS : has TENANTS ||--o{ API_KEYS : has TENANTS ||--o{ ITEM_TYPES : has ITEM_TYPES ||--o{ ITEMS : has ITEM_TYPES ||--o{ ITEM_TYPE_SCHEMA_VERSIONS : has TENANTS ||--o{ CONSUMERS : has CONSUMERS ||--o{ CONTACT_POINTS : has CONSUMERS ||--o{ SUBSCRIPTIONS : has SUBSCRIPTIONS ||--o{ SUBSCRIPTION_TOKENS : has SUBSCRIPTIONS ||--o{ NOTIFICATIONS : has AVAILABILITY_EVENTS ||--o{ NOTIFICATIONS : triggers TENANTS ||--o{ AVAILABILITY_EVENTS : has TENANTS ||--o{ OUTBOX_MESSAGES : has TENANTS { uuid id PK string name string slug UK string timezone jsonb domains string from_name string from_email timestamp created_at timestamp updated_at } USERS { uuid id PK uuid tenant_id FK string name string email string password timestamp email_verified_at timestamp created_at timestamp updated_at } API_KEYS { uuid id PK uuid tenant_id FK string name string kind string prefix UK char hash string reusable_plaintext timestamp last_used_at timestamp revoked_at timestamp created_at timestamp updated_at } ITEM_TYPES { uuid id PK uuid tenant_id FK string name string slug jsonb attribute_schema int schema_version enum notify_policy int notify_factor enum subscription_mode string action_url_template timestamp created_at timestamp updated_at } ITEMS { uuid id PK uuid tenant_id FK uuid item_type_id FK string name string external_ref string action_url timestamp created_at timestamp updated_at } CONSUMERS { uuid id PK uuid tenant_id FK string locale string timezone timestamp created_at timestamp updated_at } CONTACT_POINTS { uuid id PK uuid tenant_id FK uuid consumer_id FK enum channel string value timestamp verified_at timestamp unsubscribed_at timestamp invalid_at timestamp created_at timestamp updated_at } CONSENT_EVENTS { uuid id PK uuid tenant_id FK uuid consumer_id FK uuid contact_point_id FK uuid subscription_id enum action enum source jsonb evidence string ip_address string user_agent timestamp created_at } SUBSCRIPTIONS { uuid id PK uuid tenant_id FK uuid consumer_id FK uuid contact_point_id FK uuid item_type_id FK uuid item_id FK jsonb attributes int schema_version jsonb channels string fingerprint enum status timestamp last_notified_at timestamp expires_at timestamp created_at timestamp updated_at } SUBSCRIPTION_TOKENS { uuid id PK uuid tenant_id FK uuid subscription_id FK uuid contact_point_id FK enum purpose char token_hash string reusable_plaintext timestamp expires_at timestamp used_at timestamp created_at } AVAILABILITY_EVENTS { uuid id PK uuid tenant_id FK uuid item_type_id FK uuid item_id FK jsonb attributes int capacity timestamp available_from timestamp available_until enum source string idempotency_key UK string correlation_id timestamp processed_at timestamp created_at timestamp updated_at } NOTIFICATIONS { uuid id PK uuid tenant_id FK uuid subscription_id FK uuid availability_event_id FK uuid contact_point_id FK timestamp created_at } OUTBOX_MESSAGES { bigint id PK string topic jsonb payload string correlation_id timestamp published_at int attempts string last_error timestamp created_at }


Tenancy Tables (core)

tenants

Seller organization. Every other table carries a tenant_id.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
namestringNOT NULLDisplay name
slugstringUNIQUE, NOT NULLURL-friendly identifier
timezonestringNOT NULL, DEFAULT 'UTC'Seller's timezone
domainsjsonbNOT NULL, DEFAULT '[]'Registered domains for widget CORS
from_namestringNOT NULLEmail sender display name
from_emailstringNOT NULLEmail sender address
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes:

users

Seller staff members, scoped to a tenant.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
namestringNOT NULLDisplay name
emailstringNOT NULLEmail address
passwordstringNOT NULLHashed password
email_verified_attimestampTzNULLABLEEmail verification timestamp
remember_tokenstringNULLABLERemember me token
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes:

api_keys

Authentication keys for widget and push API.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
namestringNOT NULLKey name (e.g., "Widget key")
kindvarchar(16)NOT NULLpublishable or secret
prefixvarchar(16)UNIQUE, NOT NULLFirst 16 chars for lookup
hashchar(64)NOT NULLSHA-256 hash of full key
reusable_plaintextvarchar(64)NULLABLEPlaintext for dashboard key only
last_used_attimestampTzNULLABLELast usage timestamp
revoked_attimestampTzNULLABLERevocation timestamp
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes: Key Format:

admin_users

Platform operator credentials. Not tenant-scoped.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
emailstringUNIQUEEmail address
passwordstringNOT NULLHashed password
namestringNOT NULLDisplay name
created_attimestampCreation timestamp
updated_attimestampLast update timestamp

Catalog Tables (core)

item_types

Product/service types defined by sellers.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
namestringNOT NULLDisplay name
slugstringNOT NULLURL-friendly identifier
attribute_schemajsonbNOT NULL, DEFAULT '[]'Ordered list of attribute definitions
schema_versionintegerNOT NULL, DEFAULT 1Current schema version
notify_policyvarchar(32)NOT NULL, DEFAULT 'all'all or capacity_multiple
notify_factorunsignedSmallIntegerNOT NULL, DEFAULT 3Multiplier for capacity policy
subscription_modevarchar(32)NOT NULL, DEFAULT 'once'once or recurring
action_url_templatestringNULLABLEURL template for call-to-action
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes: attribute_schema Structure:
json
[

{

"key": "size",

"label": "Size",

"type": "enum",

"options": ["40", "41", "42"],

"required": true,

"matchable": true,

"multiple": false

}

]

items

Named instances of item types (optional).

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
item_type_iduuidFK → item_types, CASCADE DELETEParent type
namestringNOT NULLDisplay name
external_refstringNULLABLEExternal reference (e.g., SKU)
action_urlstringNULLABLEDirect link to item
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes:

item_type_schema_versions

Archive of previous schema versions.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
item_type_iduuidFK → item_types, CASCADE DELETEParent type
versionintegerNOT NULLVersion number
definitionsjsonbNOT NULLSchema at this version
created_attimestampTzNOT NULLCreation timestamp
Indexes:

People and Consent Tables (core)

consumers

End users waiting for notifications, scoped to a tenant.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
localevarchar(16)NOT NULL, DEFAULT 'en'Consumer locale
timezonestringNOT NULL, DEFAULT 'UTC'Consumer timezone
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp

contact_points

Email/phone/WhatsApp addresses for consumers.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
consumer_iduuidFK → consumers, CASCADE DELETEOwning consumer
channelvarchar(16)NOT NULLemail, sms, whatsapp, voice
valuestringNOT NULLNormalized contact value
verified_attimestampTzNULLABLEVerification timestamp
unsubscribed_attimestampTzNULLABLEUnsubscription timestamp
invalid_attimestampTzNULLABLEInvalidation timestamp (hard bounce)
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes: Sendable Condition:

consent_events

Append-only audit trail for consent changes. Enforced by a PostgreSQL trigger that prevents UPDATE and DELETE.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
consumer_iduuidFK → consumers, CASCADE DELETERelated consumer
contact_point_iduuidFK → contact_points, CASCADE DELETERelated contact point
subscription_iduuidNULLABLE, NO FKRelated subscription (ledger outlives purged subscriptions)
actionvarchar(16)NOT NULLgranted or withdrawn
sourcevarchar(32)NOT NULLwidget, confirmation_link, unsubscribe_link, dashboard, api, provider_complaint
evidencejsonbNOT NULL, DEFAULT '{}'Evidence payload
ip_addressvarchar(45)NULLABLEClient IP address
user_agenttextNULLABLEClient user agent
created_attimestampTzNOT NULLEvent timestamp
Indexes: Constraints:

Subscription Tables (core)

subscriptions

Active waiting subscriptions.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
consumer_iduuidFK → consumers, CASCADE DELETEOwning consumer
contact_point_iduuidFK → contact_points, CASCADE DELETEContact point
item_type_iduuidFK → item_types, CASCADE DELETETarget item type
item_iduuidNULLABLE, FK → items, NULL ON DELETETarget item (optional)
attributesjsonbNOT NULL, DEFAULT '{}'Subscription attributes
schema_versionintegerNOT NULLSchema version at capture
channelsjsonbNOT NULL, DEFAULT '["email"]'Notification channels
fingerprintchar(64)NOT NULLDeduplication hash
statusvarchar(32)NOT NULLSubscription status
expires_attimestampTzNOT NULLSubscription expiry
last_notified_attimestampTzNULLABLELast notification timestamp
notified_attimestampTzNULLABLENotified timestamp
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes: Status Flow:
pending_confirmation → active → notified

→ expired

→ unsubscribed

Fingerprint: SHA-256 hash of normalized attributes, used to prevent duplicate subscriptions.

subscription_tokens

Confirmation and unsubscribe tokens.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
subscription_iduuidFK → subscriptions, CASCADE DELETERelated subscription
contact_point_iduuidFK → contact_points, CASCADE DELETERelated contact point
purposevarchar(16)NOT NULLconfirm or unsubscribe
token_hashchar(64)UNIQUESHA-256 hash of plaintext
reusable_plaintextvarchar(64)NULLABLEPlaintext for reusable tokens
expires_attimestampTzNULLABLEToken expiry
used_attimestampTzNULLABLEUsage timestamp
created_attimestampTzNOT NULLCreation timestamp
Indexes: Token Rules:

Event Tables (core)

availability_events

Seller availability signals.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
item_type_iduuidFK → item_types, CASCADE DELETETarget item type
item_iduuidNULLABLE, FK → items, NULL ON DELETETarget item (optional)
attributesjsonbNOT NULL, DEFAULT '{}'Event attributes
capacityunsignedIntegerNULLABLEAvailable capacity
available_fromtimestampTzNULLABLEAvailability window start
available_untiltimestampTzNULLABLEAvailability window end
sourcevarchar(32)NOT NULLpush_api, dashboard, scraper, schedule
idempotency_keystring(128)NOT NULLDeduplication key
correlation_idstring(64)NOT NULLRequest correlation ID
processed_attimestampTzNULLABLEProcessing timestamp
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes:

notifications

Matched subscription + event pairs. Created by the matching engine.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidFK → tenants, CASCADE DELETEOwning tenant
subscription_iduuidFK → subscriptions, CASCADE DELETERelated subscription
availability_event_iduuidFK → availability_events, CASCADE DELETETriggering event
contact_point_iduuidFK → contact_points, CASCADE DELETETarget contact point
created_attimestampTzNOT NULLCreation timestamp
Constraints:

outbox_messages

Transactional outbox for reliable event publishing.

ColumnTypeConstraintsDescription
idbigIncrementsPKUnique identifier (auto-increment, not UUID)
topicstring(64)NOT NULLEvent topic
payloadjsonbNOT NULLEvent payload
correlation_idstring(64)NOT NULLRequest correlation ID
published_attimestampTzNULLABLEPublication timestamp
attemptsunsignedSmallIntegerNOT NULL, DEFAULT 0Relay attempt count
last_errortextNULLABLELast error message
created_attimestampTzNOT NULLCreation timestamp
Indexes:

Dispatch Tables (dispatch database)

deliveries

Delivery state tracking. No foreign keys — references core entities as raw UUIDs.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidNOT NULLOwning tenant
subscription_iduuidNULLABLERelated subscription
availability_event_iduuidNULLABLERelated availability event
contact_point_iduuidNOT NULLTarget contact point
channelvarchar(16)NOT NULLemail, sms, whatsapp, voice
purposevarchar(32)NOT NULLconfirmation or availability
recipientstringNOT NULLRecipient address
statusvarchar(16)NOT NULL, DEFAULT 'queued'Delivery status
provider_message_idstringNULLABLEProvider's message ID
correlation_idstring(64)NOT NULLRequest correlation ID
attemptsunsignedSmallIntegerNOT NULL, DEFAULT 0Retry attempt count
last_errortextNULLABLELast error message
sent_attimestampTzNULLABLESend start timestamp
accepted_attimestampTzNULLABLEProvider acceptance timestamp
delivered_attimestampTzNULLABLEDelivery confirmation timestamp
failed_attimestampTzNULLABLEFailure timestamp
dedupe_keychar(64)NOT NULLDeduplication key
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes: Status Flow:

stateDiagram-v2 [*] --> queued queued --> sending sending --> accepted sending --> failed sending --> queued accepted --> delivered accepted --> bounced accepted --> failed delivered --> [*] failed --> [*] bounced --> [*]

templates

Per-tenant, per-channel message templates. No foreign keys.

ColumnTypeConstraintsDescription
iduuidPKUnique identifier
tenant_iduuidNOT NULLOwning tenant
channelvarchar(16)NOT NULLemail, sms, whatsapp, voice
purposevarchar(32)NOT NULLconfirmation or availability
subjectstringNOT NULLMessage subject
html_bodytextNOT NULLHTML message body
text_bodytextNOT NULLPlain text message body
created_attimestampTzCreation timestamp
updated_attimestampTzLast update timestamp
Indexes:

Enum Reference

EnumValuesUsed In
SubscriptionStatuspending_confirmation, active, notified, expired, unsubscribedsubscriptions.status
DeliveryStatusqueued, sending, accepted, delivered, failed, bounceddeliveries.status
ApiKeyKindpublishable, secretapi_keys.kind
Channelemail, sms, whatsapp, voicecontact_points.channel, deliveries.channel, templates.channel
ConsentActiongranted, withdrawnconsent_events.action
ConsentSourcewidget, confirmation_link, unsubscribe_link, dashboard, api, provider_complaintconsent_events.source
TokenPurposeconfirm, unsubscribesubscription_tokens.purpose
NotifyPolicyall, capacity_multipleitem_types.notify_policy
SubscriptionModeonce, recurringitem_types.subscription_mode
MessagePurposeconfirmation, availabilitydeliveries.purpose, templates.purpose
AvailabilitySourcepush_api, dashboard, scraper, scheduleavailability_events.source
AttributeTypeenum, integer, decimal, date, date_range, boolean, textitem_types.attribute_schema (JSONB)