Data model — entity-relationship diagram¶
Generated from a canonical source
This page is a read-only projection of docs/architecture/data-model-erd.md.
Edit the canonical file, then run npm --prefix tools/project-knowledge-derive run derive.
@generated — mechanically parsed from apps/api/src/schema.sql by
tools/erd-derive/. Do not hand-edit —
regenerate with npx tsx tools/erd-derive/derive.ts whenever the schema changes.
CI (erd-derive-ci.yml) fails if the committed ERD drifts from a fresh derive,
which is what makes the derives_from contract above real.
85 tables, 75 foreign-key relationships. Every attribute is shown
(not just keys) so this doubles as a full schema reference; PK/FK annotations mark primary
and foreign keys (a column that is both is shown as PK). Cross-cutting context: see
ARCHITECTURE.md §3 — Systems of Record
for which entities BigCommerce owns vs. which this app owns.
How to read this page¶
Tables are grouped into domains — one small erDiagram per domain instead of a single
85-table diagram. A table with a foreign key into another domain still shows that
edge: the other domain's table appears as a bare box (name only, no attributes) inside this
domain's diagram — a stub. Look up the stub's full attributes in its own domain section (the
caption under each diagram names where). A domain with more than
14 tables splits into two alphabetical parts so no single diagram grows unreadable again.
Domain index¶
Core subscription lifecycle¶
20 tables — split into 2 alphabetical parts for readability.
Part 1 (alphabetical)¶
erDiagram
category_inclusions {
TEXT store_hash PK
INTEGER bc_category_id PK
TEXT note
TEXT created_at
INTEGER created_by
INTEGER is_deleted
}
charges {
TEXT id PK
TEXT subscription_id FK
TEXT payment_method_id FK
INTEGER amount_cents
TEXT currency
TEXT status
TEXT chain_position
INTEGER cycle_number
TEXT scheduled_at
TEXT attempted_at
TEXT succeeded_at
TEXT processor_transaction_id
INTEGER bc_order_id
TEXT failure_code
TEXT failure_message
INTEGER retry_attempt
TEXT idempotency_key
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
INTEGER is_deleted
TIMESTAMP next_retry_at
TEXT last_retry_at
INTEGER refund_amount_cents
TEXT refund_reason
TEXT skip_reason
TEXT order_overrides
TEXT charge_origin
INTEGER tax_cents
TEXT tax_source
INTEGER refund_tax_cents
TEXT external_ref
}
customers {
TEXT id PK
TEXT store_hash FK
INTEGER bc_customer_id
TEXT email
TEXT payment_customer_ref
TEXT consent_metadata
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
TEXT email_status
TEXT name
INTEGER is_deleted
TEXT deleted_at
TEXT anonymized_at
INTEGER marketing_opt_out
INTEGER lifecycle_email_opt_out
INTEGER profiling_opt_out
TEXT sms_phone_e164
TEXT sms_consent_status
TEXT sms_consent_at
TEXT sms_consent_source
TEXT external_ref
}
eligibility_rules_custom_fields {
TEXT id PK
TEXT store_hash
TEXT field_name
TEXT field_value
INTEGER created_by
TEXT created_at
}
eligibility_rules_dependency {
TEXT id PK
TEXT store_hash
TEXT target_product_id
TEXT required_prior_product_id
INTEGER created_by
TIMESTAMP created_at
}
eligibility_rules_mutex {
TEXT id PK
TEXT store_hash
TEXT product_a_id
TEXT product_b_id
TIMESTAMP created_at
}
entitlement_events {
TEXT id PK
TEXT store_hash
TEXT entitlement_id FK
TEXT from_status
TEXT to_status
TEXT reason
TEXT trigger_ref
TEXT created_at
}
entitlements {
TEXT id PK
TEXT store_hash
TEXT subscription_id FK
TEXT plan_id
TEXT entitlement_key
TEXT status
TEXT provider
TEXT provider_external_ref
TEXT granted_at
TEXT revoked_at
TEXT created_at
TEXT updated_at
}
manual_charges {
TEXT id PK
TEXT charge_id FK
TEXT subscription_id FK
TEXT store_hash
INTEGER amount_cents
TEXT currency
TEXT reason
TEXT status
TEXT failure_code
TEXT failure_message
INTEGER initiated_by
TEXT initiated_at
TEXT resolved_at
}
payment_methods {
TEXT id PK
TEXT customer_id FK
TEXT processor_connection_id FK
TEXT payment_method_ref
TEXT network_transaction_id
TEXT card_brand
TEXT card_last4
INTEGER card_exp_month
INTEGER card_exp_year
INTEGER is_default
TEXT status
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
INTEGER is_deleted
}
stores ||--o{ customers : "store_hash"
customers ||--o{ payment_methods : "customer_id"
processor_connections ||--o{ payment_methods : "processor_connection_id"
stores ||--o{ category_inclusions : "store_hash"
subscriptions ||--o{ charges : "subscription_id"
payment_methods ||--o{ charges : "payment_method_id"
subscriptions ||--o{ entitlements : "subscription_id"
entitlements ||--o{ entitlement_events : "entitlement_id"
charges ||--o{ manual_charges : "charge_id"
subscriptions ||--o{ manual_charges : "subscription_id"Cross-domain stub entities — full attributes live in: processor_connections (Payments & processors), stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Part 2 (alphabetical)¶
erDiagram
plan_channel_sales_mode {
TEXT plan_id PK
INTEGER bc_channel_id PK
TEXT sales_mode
TEXT created_at
TEXT updated_at
}
plan_entitlement_configs {
TEXT id PK
TEXT store_hash
TEXT plan_id
TEXT entitlement_key
TEXT provider
TEXT provider_config
TEXT created_at
}
plan_intervals {
TEXT id PK
TEXT plan_id FK
TEXT interval
INTEGER interval_count
INTEGER sort_order
TEXT created_at
}
plan_variant_eligibility {
TEXT plan_id PK
INTEGER bc_variant_id PK
TEXT created_at
}
plans {
TEXT id PK
TEXT store_hash FK
INTEGER bc_product_id
INTEGER bc_variant_id
TEXT name
INTEGER amount_cents
TEXT currency
TEXT interval
INTEGER interval_count
INTEGER trial_days
TEXT status
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
INTEGER commitment_cycles
INTEGER max_cycles
INTEGER lock_price_at_creation
TEXT pricing_strategy
INTEGER price_list_id
INTEGER accepting_new_subscribers
INTEGER is_deleted
INTEGER box_size
TEXT box_eligible_products
INTEGER box_customization_window_days
INTEGER billing_anchor_month
INTEGER billing_anchor_day
TEXT channel_ids
TEXT customer_group_ids
TEXT country_codes
TEXT price_list_ids
INTEGER min_qty
INTEGER max_qty
INTEGER cycle_discount_pct
TEXT cycle_discount_scope
INTEGER cycle_discount_count
INTEGER free_shipping_eligible
TEXT oos_renewal_policy
TEXT proration_policy
TEXT sales_mode
INTEGER minimum_term_cycles
INTEGER minimum_term_days
TEXT early_cancel_policy
INTEGER early_termination_fee_cents
TEXT shipping_recalc_policy
TEXT shipping_method_label
INTEGER shipping_flat_cents
INTEGER discount_pct
TEXT discount_base
TEXT shipping_interval_unit
INTEGER shipping_interval_count
TEXT processor_connection_id FK
TEXT cycle_discounts
TEXT volume_tiers
}
stores {
TEXT store_hash PK
INTEGER bc_store_id
TEXT installed_at
TEXT uninstalled_at
TEXT scope_grant
TEXT metadata
TEXT access_token_encrypted
TEXT webhook_secret_encrypted
INTEGER test_mode_enabled
INTEGER nti_freshness_days
INTEGER nudge_lead_days
TEXT capture_timing
TEXT country_code
INTEGER auth_window_days_override
INTEGER bundle_window_days
INTEGER allow_mixed_bundle
INTEGER sync_order_edits_to_subscription
INTEGER admin_shell_v2
INTEGER reactivation_grace_days
TEXT stencil_script_uuid
TEXT storefront_token_encrypted
INTEGER storefront_token_expires_at
INTEGER default_sub_status_id
INTEGER bundle_anchor_day
TEXT contract_cancel_guard_mode
INTEGER checkout_supports_custom_fields
TEXT default_discount_base
INTEGER require_subscribe_consent
}
subscription_actors {
TEXT id PK
TEXT store_hash
TEXT subscription_id FK
TEXT customer_id
TEXT role
TEXT notification_prefs
TEXT processor_connection_ref
TEXT created_at
TEXT updated_at
}
subscription_change_requests {
TEXT id PK
TEXT store_hash FK
TEXT subscription_id FK
TEXT customer_id FK
TEXT change_type
TEXT change_payload
TEXT status
INTEGER reviewer_user_id
TEXT reviewer_notes
TEXT expires_at
TEXT applied_at
TEXT created_at
TEXT updated_at
}
subscriptions {
TEXT id PK
TEXT store_hash FK
TEXT customer_id FK
TEXT plan_id FK
TEXT payment_method_id FK
TEXT processor_connection_id FK
TEXT status
TEXT mit_subtype
TEXT current_period_start
TEXT current_period_end
TEXT next_charge_at
TEXT cancel_at
TEXT cancelled_at
TEXT cancel_reason
TEXT metadata
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
TEXT chosen_interval
INTEGER chosen_interval_count
INTEGER quantity
INTEGER bc_variant_id
TEXT delivery_cadence_rule
INTEGER created_from_order_id
INTEGER created_from_line_item_id
INTEGER test_mode
INTEGER locked_price_cents
TEXT gift_from_customer_id
TEXT gift_origin_subscription_id
TEXT last_nti_refreshed_at
TIMESTAMP term_end_at
INTEGER pending_renewal_decision
INTEGER cycles_completed
TEXT starts_at
INTEGER is_deleted
INTEGER channel_id
TEXT anchor_date
TEXT country_code
INTEGER bundle_opt_in
TEXT shipping_address_hash
INTEGER term_length_days
TEXT active_promotion_ids
INTEGER notice_period_days
TIMESTAMP lock_expires_at
TEXT external_ref
TEXT legacy_cancellation_reason
TEXT imported_from
INTEGER trial_ending_soon_sent
TEXT company_id
TEXT company_name
TEXT parent_company_id
}
users {
TEXT store_hash PK
INTEGER bc_user_id PK
TEXT email
INTEGER is_owner
TEXT first_seen_at
TEXT last_seen_at
TEXT email_history_jsonb
}
stores ||--o{ users : "store_hash"
plans ||--o{ plan_intervals : "plan_id"
plans ||--o{ plan_variant_eligibility : "plan_id"
stores ||--o{ plans : "store_hash"
processor_connections ||--o{ plans : "processor_connection_id"
subscriptions ||--o{ subscription_actors : "subscription_id"
stores ||--o{ subscription_change_requests : "store_hash"
subscriptions ||--o{ subscription_change_requests : "subscription_id"
customers ||--o{ subscription_change_requests : "customer_id"
stores ||--o{ subscriptions : "store_hash"
customers ||--o{ subscriptions : "customer_id"
plans ||--o{ subscriptions : "plan_id"
payment_methods ||--o{ subscriptions : "payment_method_id"
processor_connections ||--o{ subscriptions : "processor_connection_id"
plans ||--o{ plan_channel_sales_mode : "plan_id"Cross-domain stub entities — full attributes live in: customers (Core subscription lifecycle — part 1), payment_methods (Core subscription lifecycle — part 1), processor_connections (Payments & processors).
Payments & processors¶
6 tables.
erDiagram
accounting_connections {
INTEGER id PK
TEXT store_hash
TEXT provider
TEXT endpoint_url
TEXT credential_hash
TEXT ar_account_code
TEXT revenue_account_code
INTEGER is_active
INTEGER is_deleted
TEXT created_at
TEXT updated_at
TEXT created_by
TEXT updated_by
}
accounting_post_attempts {
INTEGER id PK
INTEGER accounting_connection_id FK
TEXT store_hash
TEXT charge_id
TEXT journal_payload_json
TEXT status
INTEGER attempt_count
INTEGER last_response_code
TEXT last_response_body
TEXT last_attempted_at
TEXT next_retry_at
TEXT provider_journal_id
TEXT idempotency_key
TEXT created_at
}
authorizations {
TEXT id PK
TEXT charge_id FK
TEXT processor_connection_id FK
TEXT processor_transaction_id
TEXT status
TEXT capture_mode
INTEGER amount_cents
TEXT currency
TEXT authorized_at
TEXT captured_at
TEXT expires_at
TEXT created_at
TEXT updated_at
}
processor_connections {
TEXT id PK
TEXT store_hash FK
TEXT processor
TEXT account_ref
TEXT status
TEXT config
TEXT created_at
TEXT updated_at
INTEGER is_deleted
INTEGER is_default
TEXT routing_channel_ids
}
store_credit_applications {
TEXT id PK
TEXT store_credit_id FK
TEXT charge_id FK
TEXT subscription_id FK
TEXT store_hash
INTEGER amount_cents
TEXT status
TEXT applied_at
TEXT reversed_at
TEXT reversed_reason
}
store_credits {
TEXT id PK
TEXT store_hash
TEXT customer_id
TEXT subscription_id FK
INTEGER balance_cents
TEXT currency
TEXT source
TEXT reason
TEXT expires_at
TEXT status
INTEGER issued_by
TEXT issued_at
TEXT updated_at
}
stores ||--o{ processor_connections : "store_hash"
charges ||--o{ authorizations : "charge_id"
processor_connections ||--o{ authorizations : "processor_connection_id"
subscriptions ||--o{ store_credits : "subscription_id"
store_credits ||--o{ store_credit_applications : "store_credit_id"
charges ||--o{ store_credit_applications : "charge_id"
subscriptions ||--o{ store_credit_applications : "subscription_id"
accounting_connections ||--o{ accounting_post_attempts : "accounting_connection_id"Cross-domain stub entities — full attributes live in: charges (Core subscription lifecycle — part 1), stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Dunning & retries¶
5 tables.
erDiagram
dunning_policies {
TEXT id PK
TEXT store_hash FK
INTEGER stage_index
REAL delay_hours
TEXT on_exhaustion
TEXT email_template_key
INTEGER grace_period_days
TIMESTAMP created_at
TIMESTAMP updated_at
}
exceptions {
TEXT id PK
TEXT store_hash
TEXT charge_id FK
TEXT status
TEXT resolved_by
TEXT resolution_note
TIMESTAMP resolved_at
TIMESTAMP created_at
TEXT exception_type
TEXT subscription_id
TEXT payload
TEXT processor_dispute_id
TIMESTAMP evidence_due_by
}
network_updater_events {
TEXT id PK
TEXT processor_event_id
TEXT processor
TEXT payment_method_ref
TEXT payment_method_id FK
TEXT store_hash FK
INTEGER applied
TEXT raw_payload
TEXT received_at
}
reconciliation_drifts {
TEXT id PK
TEXT run_id FK
TEXT store_hash FK
TEXT drift_type
TEXT charge_id
TEXT subscription_id
TEXT detail
TEXT created_at
}
reconciliation_runs {
TEXT id PK
TEXT store_hash FK
TEXT started_at
TEXT finished_at
INTEGER charges_scanned
INTEGER drift_count
TEXT status
TEXT error_detail
}
stores ||--o{ reconciliation_runs : "store_hash"
reconciliation_runs ||--o{ reconciliation_drifts : "run_id"
stores ||--o{ reconciliation_drifts : "store_hash"
stores ||--o{ dunning_policies : "store_hash"
charges ||--o{ exceptions : "charge_id"
payment_methods ||--o{ network_updater_events : "payment_method_id"
stores ||--o{ network_updater_events : "store_hash"Cross-domain stub entities — full attributes live in: charges (Core subscription lifecycle — part 1), payment_methods (Core subscription lifecycle — part 1), stores (Core subscription lifecycle — part 2).
Extensions¶
7 tables.
erDiagram
bundle_charges {
TEXT bundle_id PK
TEXT charge_id PK
TEXT role
INTEGER included
INTEGER shipping_allocation_cents
}
bundle_plan_items {
TEXT id PK
TEXT store_hash
TEXT plan_id
TEXT variant_id
INTEGER quantity
REAL discount_pct
INTEGER position
INTEGER active
TEXT created_at
TEXT updated_at
}
bundles {
TEXT id PK
TEXT store_hash FK
TEXT status
TEXT anchor_date
INTEGER bc_order_id
INTEGER window_days
INTEGER is_deleted
TEXT created_at
TEXT updated_at
}
curation_cycle_picks {
TEXT id PK
TEXT store_hash
TEXT subscription_id FK
TEXT plan_id
TEXT cycle_period_start
TEXT variant_id
INTEGER quantity
TEXT rotation_rule_id
TEXT picked_at
}
curation_rotation_rules {
TEXT id PK
TEXT store_hash
TEXT plan_id
TEXT variant_id
INTEGER quantity
INTEGER position
INTEGER active
TEXT created_at
TEXT updated_at
}
delivery_instances {
TEXT id PK
TEXT subscription_id FK
TEXT store_hash FK
TEXT scheduled_at
TEXT status
TEXT override_payload
TEXT materialized_at
TEXT created_at
TEXT updated_at
INTEGER is_deleted
TEXT charge_id
INTEGER bc_order_id
TEXT claimed_at
}
subscription_extensions {
TEXT subscription_id PK
TEXT store_hash
TEXT extension_type PK
TEXT extension_data
INTEGER extension_version
TIMESTAMP created_at
TIMESTAMP updated_at
}
subscriptions ||--o{ delivery_instances : "subscription_id"
stores ||--o{ delivery_instances : "store_hash"
subscriptions ||--o{ subscription_extensions : "subscription_id"
stores ||--o{ bundles : "store_hash"
bundles ||--o{ bundle_charges : "bundle_id"
charges ||--o{ bundle_charges : "charge_id"
subscriptions ||--o{ curation_cycle_picks : "subscription_id"Cross-domain stub entities — full attributes live in: charges (Core subscription lifecycle — part 1), stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
B2B¶
5 tables.
erDiagram
allotment_debits {
TEXT id PK
TEXT grant_id FK
TEXT store_hash
INTEGER amount
TEXT reference_type
TEXT reference_id
TIMESTAMP debited_at
}
allotment_grants {
TEXT id PK
TEXT store_hash
TEXT customer_id
TEXT org_id
TEXT unit_type
INTEGER amount_per_period
INTEGER current_balance
TEXT refresh_cadence
TEXT rollover_policy
INTEGER rollover_cap
TIMESTAMP next_refresh_at
TIMESTAMP expires_at
TIMESTAMP created_at
TEXT status
TIMESTAMP revoked_at
TEXT revoked_by
TEXT revoke_reason
TIMESTAMP suspended_at
TEXT suspended_by
}
plan_customer_group_sales_mode {
TEXT plan_id PK
INTEGER bc_customer_group_id PK
TEXT sales_mode
TEXT created_at
TEXT updated_at
}
usage_cycle_totals {
TEXT id PK
TEXT store_hash
TEXT subscription_id
TEXT meter_name
TEXT cycle_period_start
REAL total_quantity
INTEGER event_count
TEXT last_updated_at
}
usage_events {
TEXT id PK
TEXT store_hash
TEXT subscription_id FK
TEXT meter_name
REAL quantity
TEXT cycle_period_start
TEXT idempotency_key
TEXT reported_at
TEXT metadata
}
allotment_grants ||--o{ allotment_debits : "grant_id"
subscriptions ||--o{ usage_events : "subscription_id"
plans ||--o{ plan_customer_group_sales_mode : "plan_id"Cross-domain stub entities — full attributes live in: plans (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Promotions & discounts¶
9 tables.
erDiagram
product_discount_base_overrides {
TEXT store_hash PK
INTEGER bc_product_id PK
TEXT discount_base
TEXT created_at
TEXT updated_at
}
product_exclusions {
TEXT store_hash PK
INTEGER bc_product_id PK
TEXT reason
TEXT created_at
INTEGER created_by
INTEGER is_deleted
}
promotion_settings {
INTEGER id PK
TEXT store_hash
INTEGER bc_promotion_id
INTEGER subscription_only
TEXT created_at
TEXT updated_at
INTEGER is_deleted
TEXT stacking_rule
}
referral_codes {
TEXT id PK
TEXT store_hash
TEXT customer_id
TEXT subscription_id FK
TEXT code
INTEGER credit_amount_cents
TEXT currency
TEXT status
TEXT created_at
}
referral_credits {
TEXT id PK
TEXT store_hash
TEXT referral_code_id FK
TEXT subscription_id FK
TEXT referral_event_id
INTEGER amount_cents
TEXT currency
TEXT status
TEXT applied_charge_id
TEXT reversed_reason
TEXT created_at
TEXT applied_at
TEXT reversed_at
}
referral_events {
TEXT id PK
TEXT store_hash
TEXT referral_code_id FK
TEXT referred_customer_id
TEXT event_type
TEXT charge_id
TEXT created_at
}
subscription_discounts {
TEXT id PK
TEXT subscription_id FK
INTEGER percent
INTEGER applies_to_cycle
TEXT expires_at
TEXT created_at
TEXT applied_at
TEXT reason
INTEGER cycle_min
INTEGER cycle_max
}
subscription_promotion_applications {
TEXT id PK
TEXT subscription_id FK
TEXT charge_id FK
TEXT promotion_id FK
TEXT source
INTEGER amount_cents
TEXT discount_type
INTEGER cycle_index_applied
TEXT applied_at
}
subscription_promotions {
TEXT id PK
TEXT store_hash
TEXT name
TEXT code
TEXT application_window
TEXT config
TEXT eligibility_rules
TEXT discount_type
TEXT stacking_rule
TEXT lock_policy
TEXT applies_to
INTEGER recurring
TEXT status
INTEGER max_redemptions
INTEGER current_redemptions
TEXT starts_at
TEXT ends_at
TEXT created_at
TEXT updated_at
TEXT archived_at
INTEGER is_deleted
}
subscriptions ||--o{ subscription_discounts : "subscription_id"
stores ||--o{ product_exclusions : "store_hash"
subscriptions ||--o{ referral_codes : "subscription_id"
referral_codes ||--o{ referral_events : "referral_code_id"
referral_codes ||--o{ referral_credits : "referral_code_id"
subscriptions ||--o{ referral_credits : "subscription_id"
subscriptions ||--o{ subscription_promotion_applications : "subscription_id"
charges ||--o{ subscription_promotion_applications : "charge_id"
subscription_promotions ||--o{ subscription_promotion_applications : "promotion_id"
stores ||--o{ product_discount_base_overrides : "store_hash"Cross-domain stub entities — full attributes live in: charges (Core subscription lifecycle — part 1), stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Notifications & email¶
13 tables.
erDiagram
email_domains {
TEXT store_hash PK
TEXT domain PK
TEXT resend_domain_id
TEXT dkim_record
TEXT dkim_record_name
TEXT verification_status
INTEGER dmarc_aligned
TIMESTAMP last_verification_attempt_at
TIMESTAMP verified_at
TIMESTAMP created_at
TEXT send_state
TEXT send_state_reason
TEXT send_state_updated_at
REAL last_bounce_rate
REAL last_complaint_rate
INTEGER last_sample_size
}
email_idempotency {
INTEGER id PK
TEXT store_hash
TEXT idempotency_key
TEXT template_key
TEXT recipient
TEXT source_event_id
TEXT provider_message_id
TEXT created_at
}
email_send_log {
TEXT id PK
TEXT send_key
TEXT event_id FK
TEXT recipient_email
TEXT template_id
TEXT locale
TEXT vendor_message_id
TEXT vendor_status
TEXT bounce_class
TEXT attempted_at
TEXT delivered_at
TEXT bounced_at
}
email_sends {
INTEGER id PK
TEXT store_hash
TEXT subscription_id
TEXT customer_email
TEXT template_key
TEXT provider
TEXT provider_message_id
TEXT status
TEXT last_event_at
TEXT last_processed_event_id
TEXT bounce_type
TEXT bounce_subtype
TEXT error_message
TEXT created_at
TEXT updated_at
INTEGER is_deleted
}
email_suppressions {
INTEGER id PK
TEXT store_hash
TEXT customer_email
TEXT reason
TEXT evidence_event_id
TEXT created_at
}
email_templates {
TEXT id
TEXT store_hash PK
TEXT template_key PK
TEXT format
TEXT subject
TEXT body_source
TEXT variables_used
INTEGER is_default
TIMESTAMP created_at
TIMESTAMP updated_at
}
klaviyo_connections {
INTEGER id PK
TEXT store_hash
TEXT api_key_encrypted
TEXT api_revision
TEXT event_map_json
INTEGER is_active
INTEGER is_deleted
TEXT created_at
TEXT updated_at
TEXT created_by
TEXT updated_by
}
klaviyo_delivery_attempts {
INTEGER id PK
INTEGER klaviyo_connection_id FK
TEXT store_hash
TEXT event_id
TEXT our_event_type
TEXT klaviyo_metric_name
TEXT request_body_json
TEXT status
INTEGER attempt_count
INTEGER last_response_code
TEXT last_response_body
TEXT last_attempted_at
TEXT next_retry_at
TEXT created_at
}
slack_destinations {
INTEGER id PK
TEXT store_hash FK
TEXT webhook_url_encrypted
TEXT channel_hint
INTEGER is_active
TEXT created_at
TEXT updated_at
}
slack_dispatch_attempts {
INTEGER id PK
TEXT store_hash
TEXT kind
TEXT metric
TEXT payload_json
TEXT status
INTEGER attempt_count
INTEGER last_response_code
TEXT last_response_body
TEXT last_attempted_at
TEXT next_retry_at
TEXT idempotency_key
TEXT created_at
}
sms_sends {
INTEGER id PK
TEXT store_hash
TEXT subscription_id
TEXT customer_id
TEXT to_phone_e164
TEXT template_key
TEXT provider
TEXT provider_message_sid
TEXT status
TEXT error_code
TEXT error_message
TEXT created_at
TEXT updated_at
INTEGER is_deleted
}
subscriber_preference_definitions {
TEXT id PK
TEXT store_hash FK
TEXT pref_key
TEXT pref_label
TEXT pref_type
TEXT options
TEXT excludes_variant_map
INTEGER required
INTEGER display_order
INTEGER active
TIMESTAMP created_at
TIMESTAMP updated_at
}
subscriber_preferences {
TEXT store_hash PK
TEXT customer_id PK
TEXT prefs
TIMESTAMP created_at
TIMESTAMP updated_at
}
events ||--o{ email_send_log : "event_id"
stores ||--o{ subscriber_preference_definitions : "store_hash"
customers ||--o{ subscriber_preferences : "customer_id"
klaviyo_connections ||--o{ klaviyo_delivery_attempts : "klaviyo_connection_id"
stores ||--o{ slack_destinations : "store_hash"Cross-domain stub entities — full attributes live in: customers (Core subscription lifecycle — part 1), events (Webhooks & integrations), stores (Core subscription lifecycle — part 2).
Webhooks & integrations¶
4 tables.
erDiagram
events {
TEXT id PK
TEXT store_hash FK
TEXT type
TEXT subscription_id FK
TEXT charge_id FK
TEXT customer_id FK
INTEGER actor_user_id
TEXT actor_kind
TEXT payload
TEXT created_at
TEXT queue_published_at
TEXT target_type
TEXT target_id
TEXT resource_type
TEXT resource_id
TEXT summary
TEXT operation_type
TEXT request_id
TEXT ip_address
TEXT fields_changed
}
installed_extensions {
TEXT store_hash PK
TEXT extension_id PK
TEXT name
TEXT status
TEXT granted_scopes
TEXT admin_panels
TIMESTAMP installed_at
TIMESTAMP uninstalled_at
}
webhook_delivery_attempts {
INTEGER id PK
INTEGER webhook_subscription_id FK
TEXT event_type
TEXT event_payload_json
TEXT status
INTEGER attempt_count
INTEGER last_response_code
TEXT last_response_body
TEXT last_attempted_at
TEXT next_retry_at
TEXT idempotency_key
TEXT created_at
}
webhook_subscriptions {
INTEGER id PK
TEXT store_hash
TEXT endpoint_url
TEXT events_json
TEXT secret_hash
INTEGER is_active
INTEGER is_deleted
TEXT created_at
TEXT updated_at
TEXT created_by
TEXT updated_by
TEXT field_subscriptions
TEXT query_filter
TEXT payload_projection
}
stores ||--o{ events : "store_hash"
subscriptions ||--o{ events : "subscription_id"
charges ||--o{ events : "charge_id"
customers ||--o{ events : "customer_id"
webhook_subscriptions ||--o{ webhook_delivery_attempts : "webhook_subscription_id"Cross-domain stub entities — full attributes live in: charges (Core subscription lifecycle — part 1), customers (Core subscription lifecycle — part 1), stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Admin/CS & audit¶
6 tables.
erDiagram
alert_thresholds {
INTEGER id PK
TEXT store_hash FK
TEXT metric
INTEGER window_minutes
TEXT comparator
INTEGER threshold_value
INTEGER is_active
TEXT created_at
TEXT updated_at
TEXT email_alert_address
TEXT email_digest_address
}
consent_records {
TEXT id PK
TEXT subscription_id FK
TEXT store_hash
TEXT consent_version
TEXT consent_text_ref
TEXT disclosed_terms
TIMESTAMP consented_at
TEXT actor
TEXT channel
TIMESTAMP created_at
}
cs_tool_sessions {
TEXT id PK
TEXT store_hash
TEXT subscription_id
INTEGER operator_id
TEXT expires_at
TEXT used_at
TEXT ip_addr
TEXT created_at
}
helpdesk_connections {
TEXT id PK
TEXT store_hash FK
TEXT provider
TEXT account_subdomain
TEXT account_username
TEXT api_token_encrypted
TEXT status
TEXT config
TEXT created_at
TEXT updated_at
INTEGER created_by
INTEGER updated_by
INTEGER is_deleted
}
helpdesk_tickets {
TEXT id PK
TEXT store_hash
TEXT subscription_id
TEXT provider
TEXT external_ticket_id
TEXT trigger_reason
TEXT status
TEXT context_json
TEXT created_at
TEXT updated_at
}
operations {
TEXT id PK
TEXT store_hash FK
TEXT type
TEXT reference_type
TEXT reference_id
TEXT metadata
INTEGER done
TEXT error
TEXT result
INTEGER attempts
TEXT created_at
TEXT updated_at
TEXT completed_at
INTEGER is_deleted
INTEGER max_attempts
TIMESTAMP locked_until
}
stores ||--o{ operations : "store_hash"
stores ||--o{ helpdesk_connections : "store_hash"
stores ||--o{ alert_thresholds : "store_hash"
subscriptions ||--o{ consent_records : "subscription_id"Cross-domain stub entities — full attributes live in: stores (Core subscription lifecycle — part 2), subscriptions (Core subscription lifecycle — part 2).
Imports & migration¶
1 tables.
erDiagram
subscription_import_jobs {
TEXT id PK
TEXT store_hash
INTEGER operator_id
TEXT status
INTEGER total_rows
INTEGER valid_rows
INTEGER error_rows
TEXT csv_payload
TEXT preview_json
TEXT result_json
TEXT created_at
TEXT confirmed_at
TEXT completed_at
}Platform & misc¶
9 tables.
erDiagram
api_keys {
TEXT id PK
TEXT store_hash FK
TEXT name
TEXT key_prefix
TEXT key_hash
TEXT scopes
TEXT created_at
INTEGER created_by
TEXT last_used_at
TEXT revoked_at
}
custom_field_definitions {
TEXT id PK
TEXT store_hash
TEXT scope
TEXT scope_id
TEXT field_key
TEXT field_type
TEXT field_label
INTEGER required
TEXT validation_regex
TEXT options
TEXT subscriber_visibility
INTEGER display_order
TIMESTAMP created_at
INTEGER is_archived
TIMESTAMP archived_at
}
experiment_assignments {
TEXT store_hash PK
TEXT experiment_id PK
TEXT subject_key PK
TEXT variant_key
TIMESTAMP assigned_at
TIMESTAMP converted_at
}
experiments {
TEXT id PK
TEXT store_hash
TEXT experiment_key
TEXT surface
TEXT status
TEXT variants
TIMESTAMP created_at
}
idempotency_keys {
TEXT key PK
TEXT store_hash PK
TEXT request_fingerprint
INTEGER response_status
TEXT response_body
TEXT response_headers
TIMESTAMP created_at
}
job_configuration {
TEXT cron_name PK
TEXT store_hash PK
TEXT config_key PK
TEXT config_value
INTEGER config_version
TIMESTAMP updated_at
}
magic_link_tokens {
TEXT token_hash PK
TEXT store_hash
TEXT customer_id
TIMESTAMP expires_at
TIMESTAMP consumed_at
TIMESTAMP created_at
}
portal_custom_domains {
TEXT store_hash PK
TEXT domain PK
TEXT status
TEXT expected_cname_target
TIMESTAMP last_verification_attempt_at
TEXT last_verification_detail
TIMESTAMP verified_at
TIMESTAMP created_at
}
sandboxes {
TEXT id PK
TEXT owner_store_hash
TEXT sandbox_store_hash
TEXT api_key_id
TEXT status
INTEGER test_subscription_count
TIMESTAMP created_at
TIMESTAMP destroyed_at
}
stores ||--o{ api_keys : "store_hash"Cross-domain stub entities — full attributes live in: stores (Core subscription lifecycle — part 2).