Data Model
One Postgres server, one database per service, and no cross-database queries under any circumstance.
Entity Relationship Diagram
Tenancy Tables (core)
tenants
Seller organization. Every other table carries a tenant_id.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
name | string | NOT NULL | Display name |
slug | string | UNIQUE, NOT NULL | URL-friendly identifier |
timezone | string | NOT NULL, DEFAULT 'UTC' | Seller's timezone |
domains | jsonb | NOT NULL, DEFAULT '[]' | Registered domains for widget CORS |
from_name | string | NOT NULL | Email sender display name |
from_email | string | NOT NULL | Email sender address |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
tenants_slug_unique— UNIQUE onslug
users
Seller staff members, scoped to a tenant.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
name | string | NOT NULL | Display name |
email | string | NOT NULL | Email address |
password | string | NOT NULL | Hashed password |
email_verified_at | timestampTz | NULLABLE | Email verification timestamp |
remember_token | string | NULLABLE | Remember me token |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
users_tenant_id_email_unique— UNIQUE on (tenant_id,email)
api_keys
Authentication keys for widget and push API.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
name | string | NOT NULL | Key name (e.g., "Widget key") |
kind | varchar(16) | NOT NULL | publishable or secret |
prefix | varchar(16) | UNIQUE, NOT NULL | First 16 chars for lookup |
hash | char(64) | NOT NULL | SHA-256 hash of full key |
reusable_plaintext | varchar(64) | NULLABLE | Plaintext for dashboard key only |
last_used_at | timestampTz | NULLABLE | Last usage timestamp |
revoked_at | timestampTz | NULLABLE | Revocation timestamp |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
api_keys_prefix_unique— UNIQUE onprefixapi_keys_tenant_id_kind_index— on (tenant_id,kind)
- Publishable:
pk_live_<24 random chars>(total 32 chars) - Secret:
sk_live_<24 random chars>(total 32 chars) - Prefix: First 16 chars used for indexed lookup
- Hash: SHA-256 of full plaintext, compared in constant time
admin_users
Platform operator credentials. Not tenant-scoped.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
email | string | UNIQUE | Email address |
password | string | NOT NULL | Hashed password |
name | string | NOT NULL | Display name |
created_at | timestamp | Creation timestamp | |
updated_at | timestamp | Last update timestamp |
Catalog Tables (core)
item_types
Product/service types defined by sellers.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
name | string | NOT NULL | Display name |
slug | string | NOT NULL | URL-friendly identifier |
attribute_schema | jsonb | NOT NULL, DEFAULT '[]' | Ordered list of attribute definitions |
schema_version | integer | NOT NULL, DEFAULT 1 | Current schema version |
notify_policy | varchar(32) | NOT NULL, DEFAULT 'all' | all or capacity_multiple |
notify_factor | unsignedSmallInteger | NOT NULL, DEFAULT 3 | Multiplier for capacity policy |
subscription_mode | varchar(32) | NOT NULL, DEFAULT 'once' | once or recurring |
action_url_template | string | NULLABLE | URL template for call-to-action |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
item_types_tenant_id_slug_unique— UNIQUE on (tenant_id,slug)
[
{
"key": "size",
"label": "Size",
"type": "enum",
"options": ["40", "41", "42"],
"required": true,
"matchable": true,
"multiple": false
}
]
items
Named instances of item types (optional).
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
item_type_id | uuid | FK → item_types, CASCADE DELETE | Parent type |
name | string | NOT NULL | Display name |
external_ref | string | NULLABLE | External reference (e.g., SKU) |
action_url | string | NULLABLE | Direct link to item |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
items_tenant_id_item_type_id_index— on (tenant_id,item_type_id)items_item_type_id_external_ref_unique— UNIQUE on (item_type_id,external_ref)
item_type_schema_versions
Archive of previous schema versions.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
item_type_id | uuid | FK → item_types, CASCADE DELETE | Parent type |
version | integer | NOT NULL | Version number |
definitions | jsonb | NOT NULL | Schema at this version |
created_at | timestampTz | NOT NULL | Creation timestamp |
item_type_schema_versions_item_type_id_version_unique— UNIQUE on (item_type_id,version)
People and Consent Tables (core)
consumers
End users waiting for notifications, scoped to a tenant.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
locale | varchar(16) | NOT NULL, DEFAULT 'en' | Consumer locale |
timezone | string | NOT NULL, DEFAULT 'UTC' | Consumer timezone |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
contact_points
Email/phone/WhatsApp addresses for consumers.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
consumer_id | uuid | FK → consumers, CASCADE DELETE | Owning consumer |
channel | varchar(16) | NOT NULL | email, sms, whatsapp, voice |
value | string | NOT NULL | Normalized contact value |
verified_at | timestampTz | NULLABLE | Verification timestamp |
unsubscribed_at | timestampTz | NULLABLE | Unsubscription timestamp |
invalid_at | timestampTz | NULLABLE | Invalidation timestamp (hard bounce) |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
contact_points_tenant_id_channel_value_unique— UNIQUE on (tenant_id,channel,value)contact_points_consumer_id_channel_index— on (consumer_id,channel)
verified_at IS NOT NULLunsubscribed_at IS NULLinvalid_at IS NULL
consent_events
Append-only audit trail for consent changes. Enforced by a PostgreSQL trigger that prevents UPDATE and DELETE.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
consumer_id | uuid | FK → consumers, CASCADE DELETE | Related consumer |
contact_point_id | uuid | FK → contact_points, CASCADE DELETE | Related contact point |
subscription_id | uuid | NULLABLE, NO FK | Related subscription (ledger outlives purged subscriptions) |
action | varchar(16) | NOT NULL | granted or withdrawn |
source | varchar(32) | NOT NULL | widget, confirmation_link, unsubscribe_link, dashboard, api, provider_complaint |
evidence | jsonb | NOT NULL, DEFAULT '{}' | Evidence payload |
ip_address | varchar(45) | NULLABLE | Client IP address |
user_agent | text | NULLABLE | Client user agent |
created_at | timestampTz | NOT NULL | Event timestamp |
consent_events_contact_point_id_created_at_index— on (contact_point_id,created_at)consent_events_tenant_id_created_at_index— on (tenant_id,created_at)
- Append-only: No UPDATE or DELETE operations allowed (enforced by database trigger
consent_events_no_mutation)
Subscription Tables (core)
subscriptions
Active waiting subscriptions.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
consumer_id | uuid | FK → consumers, CASCADE DELETE | Owning consumer |
contact_point_id | uuid | FK → contact_points, CASCADE DELETE | Contact point |
item_type_id | uuid | FK → item_types, CASCADE DELETE | Target item type |
item_id | uuid | NULLABLE, FK → items, NULL ON DELETE | Target item (optional) |
attributes | jsonb | NOT NULL, DEFAULT '{}' | Subscription attributes |
schema_version | integer | NOT NULL | Schema version at capture |
channels | jsonb | NOT NULL, DEFAULT '["email"]' | Notification channels |
fingerprint | char(64) | NOT NULL | Deduplication hash |
status | varchar(32) | NOT NULL | Subscription status |
expires_at | timestampTz | NOT NULL | Subscription expiry |
last_notified_at | timestampTz | NULLABLE | Last notification timestamp |
notified_at | timestampTz | NULLABLE | Notified timestamp |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
subscriptions_tenant_id_fingerprint_unique— UNIQUE on (tenant_id,fingerprint)subscriptions_stage_one_idx— on (tenant_id,item_type_id,status,expires_at)subscriptions_status_created_at_index— on (status,created_at)subscriptions_attributes_gin— GIN onattributesusingjsonb_path_ops
pending_confirmation → active → notified
→ expired
→ unsubscribed
subscription_tokens
Confirmation and unsubscribe tokens.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
subscription_id | uuid | FK → subscriptions, CASCADE DELETE | Related subscription |
contact_point_id | uuid | FK → contact_points, CASCADE DELETE | Related contact point |
purpose | varchar(16) | NOT NULL | confirm or unsubscribe |
token_hash | char(64) | UNIQUE | SHA-256 hash of plaintext |
reusable_plaintext | varchar(64) | NULLABLE | Plaintext for reusable tokens |
expires_at | timestampTz | NULLABLE | Token expiry |
used_at | timestampTz | NULLABLE | Usage timestamp |
created_at | timestampTz | NOT NULL | Creation timestamp |
subscription_tokens_token_hash_unique— UNIQUE ontoken_hashsubscription_tokens_subscription_id_purpose_index— on (subscription_id,purpose)
- confirm: Single-use, expires after 72 hours
- unsubscribe: Reusable (bearer token in email footers), never expires
Event Tables (core)
availability_events
Seller availability signals.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
item_type_id | uuid | FK → item_types, CASCADE DELETE | Target item type |
item_id | uuid | NULLABLE, FK → items, NULL ON DELETE | Target item (optional) |
attributes | jsonb | NOT NULL, DEFAULT '{}' | Event attributes |
capacity | unsignedInteger | NULLABLE | Available capacity |
available_from | timestampTz | NULLABLE | Availability window start |
available_until | timestampTz | NULLABLE | Availability window end |
source | varchar(32) | NOT NULL | push_api, dashboard, scraper, schedule |
idempotency_key | string(128) | NOT NULL | Deduplication key |
correlation_id | string(64) | NOT NULL | Request correlation ID |
processed_at | timestampTz | NULLABLE | Processing timestamp |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
availability_events_tenant_id_idempotency_key_unique— UNIQUE on (tenant_id,idempotency_key)availability_events_tenant_id_item_type_id_created_at_index— on (tenant_id,item_type_id,created_at)
notifications
Matched subscription + event pairs. Created by the matching engine.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | FK → tenants, CASCADE DELETE | Owning tenant |
subscription_id | uuid | FK → subscriptions, CASCADE DELETE | Related subscription |
availability_event_id | uuid | FK → availability_events, CASCADE DELETE | Triggering event |
contact_point_id | uuid | FK → contact_points, CASCADE DELETE | Target contact point |
created_at | timestampTz | NOT NULL | Creation timestamp |
notifications_subscription_id_availability_event_id_unique— UNIQUE on (subscription_id,availability_event_id) — double-send guard
outbox_messages
Transactional outbox for reliable event publishing.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | bigIncrements | PK | Unique identifier (auto-increment, not UUID) |
topic | string(64) | NOT NULL | Event topic |
payload | jsonb | NOT NULL | Event payload |
correlation_id | string(64) | NOT NULL | Request correlation ID |
published_at | timestampTz | NULLABLE | Publication timestamp |
attempts | unsignedSmallInteger | NOT NULL, DEFAULT 0 | Relay attempt count |
last_error | text | NULLABLE | Last error message |
created_at | timestampTz | NOT NULL | Creation timestamp |
outbox_unpublished_idx— on (published_at,id) WHEREpublished_at IS NULL
Dispatch Tables (dispatch database)
deliveries
Delivery state tracking. No foreign keys — references core entities as raw UUIDs.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | NOT NULL | Owning tenant |
subscription_id | uuid | NULLABLE | Related subscription |
availability_event_id | uuid | NULLABLE | Related availability event |
contact_point_id | uuid | NOT NULL | Target contact point |
channel | varchar(16) | NOT NULL | email, sms, whatsapp, voice |
purpose | varchar(32) | NOT NULL | confirmation or availability |
recipient | string | NOT NULL | Recipient address |
status | varchar(16) | NOT NULL, DEFAULT 'queued' | Delivery status |
provider_message_id | string | NULLABLE | Provider's message ID |
correlation_id | string(64) | NOT NULL | Request correlation ID |
attempts | unsignedSmallInteger | NOT NULL, DEFAULT 0 | Retry attempt count |
last_error | text | NULLABLE | Last error message |
sent_at | timestampTz | NULLABLE | Send start timestamp |
accepted_at | timestampTz | NULLABLE | Provider acceptance timestamp |
delivered_at | timestampTz | NULLABLE | Delivery confirmation timestamp |
failed_at | timestampTz | NULLABLE | Failure timestamp |
dedupe_key | char(64) | NOT NULL | Deduplication key |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
deliveries_tenant_id_dedupe_key_unique— UNIQUE on (tenant_id,dedupe_key)deliveries_provider_message_id_index— onprovider_message_iddeliveries_tenant_id_status_created_at_index— on (tenant_id,status,created_at)
templates
Per-tenant, per-channel message templates. No foreign keys.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK | Unique identifier |
tenant_id | uuid | NOT NULL | Owning tenant |
channel | varchar(16) | NOT NULL | email, sms, whatsapp, voice |
purpose | varchar(32) | NOT NULL | confirmation or availability |
subject | string | NOT NULL | Message subject |
html_body | text | NOT NULL | HTML message body |
text_body | text | NOT NULL | Plain text message body |
created_at | timestampTz | Creation timestamp | |
updated_at | timestampTz | Last update timestamp |
templates_tenant_id_channel_purpose_unique— UNIQUE on (tenant_id,channel,purpose)
Enum Reference
| Enum | Values | Used In |
|---|---|---|
SubscriptionStatus | pending_confirmation, active, notified, expired, unsubscribed | subscriptions.status |
DeliveryStatus | queued, sending, accepted, delivered, failed, bounced | deliveries.status |
ApiKeyKind | publishable, secret | api_keys.kind |
Channel | email, sms, whatsapp, voice | contact_points.channel, deliveries.channel, templates.channel |
ConsentAction | granted, withdrawn | consent_events.action |
ConsentSource | widget, confirmation_link, unsubscribe_link, dashboard, api, provider_complaint | consent_events.source |
TokenPurpose | confirm, unsubscribe | subscription_tokens.purpose |
NotifyPolicy | all, capacity_multiple | item_types.notify_policy |
SubscriptionMode | once, recurring | item_types.subscription_mode |
MessagePurpose | confirmation, availability | deliveries.purpose, templates.purpose |
AvailabilitySource | push_api, dashboard, scraper, schedule | availability_events.source |
AttributeType | enum, integer, decimal, date, date_range, boolean, text | item_types.attribute_schema (JSONB) |