Schema reference

This reference separates Django-managed application tables from the tables created dynamically in each client's Shopify Supabase database.

Application database models

ShopifyConnection

Existing control-plane model read by the subscriber to map a permanent Shopify domain to one organization.

Field	Purpose
organization_id	One-to-one tenant owner
setup_by_id	User who configured the connection
shop_url	Configured shop URL
client_id	Shopify application credential
api_secret_key	Secret used for direct webhook HMAC verification
myshopify_domain	Permanent domain used by subscriber organization resolution
access_token, token_expires_at	Shopify API access state
created_at, updated_at	Connection audit timestamps

ShopifyEvent

Existing direct-webhook audit model written by shopify_webhook_view.

Field	Purpose
organization_id	Owning organization
topic	Shopify slash topic
shop_domain	Source shop
payload	Raw JSON body
received_at	Application receipt time

Indexes: shopify_app_org_recv_idx on organization and descending receipt time, and shopify_app_org_topic_idx on organization and topic.

Database

Existing database registry read by ShopifyEventStorage.

Field	Purpose
database_name	Logical database name; shopify is the fallback selector
type	Application type; shopify is the primary selector
organization_id	Client owner
is_deleted	Excludes retired targets from resolution
datasource_id, last_sync_date	Existing Redash/sync metadata outside this pipeline

The model enforces unique (organization_id, database_name) values.

DatabaseCredential

Existing one-to-one credential record read after the target Database is selected.

Field	Purpose
database_id	One-to-one target database
db_type	Defaults to postgresql
host, port, db_name, username	Connection components
encrypted_password	Fernet-encrypted password
created_at, updated_at	Credential audit timestamps

Password decryption requires the backend's DB_CREDENTIAL_FERNET_KEY to match the key used during encryption.

SubscriberFailedEvent

New Django model introduced by PR #23. The default physical table name is subscriber_subscriberfailedevent.

Field	Type / default	Purpose
id	Big auto primary key	Retry record identifier
source	String, empty	Event source
event_type	String, empty	Normalized event type
event_id	String, empty	Webhook/event identifier when known
shop_domain	String, empty	Shopify tenant key
organization_id	Nullable bigint, indexed	Resolved organization when known
envelope	JSON object	Complete enriched replay payload
error_type	String	Latest exception class
error_message	Text	Latest exception message
traceback	Text	Full latest Python traceback
status	pending	pending, resolved, or failed
retry_count	0	Number of failed replay attempts
max_retries	5	Terminal failure threshold
next_retry_at	Nullable timestamp	Earliest next eligible replay
last_attempted_at	Nullable timestamp	Last replay start
resolved_at	Nullable timestamp	Successful replay completion
created_at, updated_at	Automatic timestamps	Lifecycle audit

Named indexes:

• sub_failed_status_retry_idx on (status, next_retry_at)
• sub_failed_source_type_idx on (source, event_type)
• sub_failed_shop_event_idx on (shop_domain, event_id)

Client Supabase event ledger

shopify_events

Column	PostgreSQL type	Constraint / behavior
id	BIGSERIAL	Primary key
event_id	TEXT	Required; explicit webhook ID or deterministic hash
organization_id	BIGINT	Required
shop_domain	TEXT	Required
topic	TEXT	Required slash form, for example orders/create
event_type	TEXT	Required dot form, for example orders.create
resource_type	TEXT	Required module name
resource_id	TEXT	Required, empty string when not found
payload	JSONB	Original Shopify payload
parsed_payload	JSONB	Typed customer representation or raw fallback
received_at	TIMESTAMPTZ	Envelope timestamp or processing time
inserted_at	TIMESTAMPTZ	Defaults to NOW() on first insert

Uniqueness and indexes:

• Unique (organization_id, shop_domain, event_id)
• shopify_events_org_topic_idx on (organization_id, topic, received_at DESC)
• shopify_events_org_resource_idx on (organization_id, resource_type, resource_id)

Client Supabase resource snapshots

All 12 module tables share one physical shape.

Column	PostgreSQL type	Constraint / behavior
organization_id	BIGINT	Composite primary key
shop_domain	TEXT	Composite primary key
shopify_id	TEXT	Composite primary key
topic	TEXT	Most recently processed slash topic
event_type	TEXT	Most recently processed dot event type
payload	JSONB	Latest raw resource body
parsed_payload	JSONB	Latest parsed or fallback body
is_deleted	BOOLEAN	Defaults to false; set from delete topics
first_seen_at	TIMESTAMPTZ	Defaults to first insert time and is never overwritten
last_seen_at	TIMESTAMPTZ	Event receipt time, updated on conflict

Every table has a primary key on (organization_id, shop_domain, shopify_id) and an index named <table>_org_topic_idx on (organization_id, topic).

Provisioned target tables

Resource	Table
Event ledger	shopify_events
Orders	shopify_orders
Checkouts	shopify_checkouts
Carts	shopify_carts
Products	shopify_products
Customers	shopify_customers
Customer groups	shopify_customer_groups
Payment terms	shopify_payment_terms
Refunds	shopify_refunds
Selling plan groups	shopify_selling_plan_groups
Returns	shopify_returns
Reverse deliveries	shopify_reverse_deliveries
Reverse fulfillment orders	shopify_reverse_fulfillment_orders

Security and retention notes

• Supabase passwords are designed to be stored encrypted in DatabaseCredential; plaintext DSNs should not be committed.
• ShopifyConnection.api_secret_key and access_token are currently plain model fields with an in-code encryption TODO. That is outside PR #22/#23 but remains a production hardening item.
• Failure envelopes and tracebacks may include client payload data. Access, retention, and redaction rules should treat the failure table as sensitive.
• No row-level security policy, table partitioning, TTL, or purge process is introduced by these PRs.