Databases
The default template's two D1 databases, table purposes, and relationships.
The default template uses two separate Cloudflare D1 databases. DB stores application data, and ANALYTICS_DB stores first-party analytics data. There are no table relationships between the two databases, and no SQL query joins tables across them.
DB
Browse all 31 tables and their fields in the main application database.
ANALYTICS_DB
Browse all 4 tables and their fields in the analytics database.
DB
| Item | Value | Description |
|---|---|---|
| Binding name | DB | The Worker uses this binding to access the application database. |
| Default database name | saavo-template-db | On the first deployment, the deployment script regenerates this name from the Worker name. |
| Initialization script | schema/db-init.sql | Only for initializing an empty database or manually resetting a database. |
| Initialization marker table | saas_user | db:migrate uses this table to determine whether the application database has been initialized. |
Tables
| Area | Tables | Purpose |
|---|---|---|
| Accounts and authentication | saas_user, saas_session, two_factor_setup_session, email_verification_session, password_reset_session | User accounts, sign-in sessions, two-factor authentication, email verification, and password resets |
| OAuth 2.0 | oauth_auth_session, oauth_client, oauth_auth_code, oauth_token | Authorization requests, clients, authorization codes, and tokens |
| Roles and entitlements | user_role_capability, user_entitlement_capability, capability_quota_event | Role capabilities, entitlement capabilities, and quota change records |
| Reaction | event_execution, command_execution | Event and Command execution records |
| Payments | webhook_events, payment_customers, checkout_sessions, purchase_items, subscriptions, subscription_items, transactions, transaction_items, refunds | Webhooks, Customers, Checkout, purchases, subscriptions, transactions, and refunds |
| Affiliate program | affiliate_payout_profiles, affiliate_commissions, affiliate_monthly_payouts, affiliate_monthly_payout_records | Payout details, commissions, monthly settlements, and payment records |
| Support tickets and content | ticket, newsletter_subscriber | Support ticket indexes and newsletter subscriptions |
| In-app notifications | notification, user_notification | Notification content and users' read status |
Entity relationships
The following ER diagrams show the main database by application module. Relationships reflect field semantics and actual queries in repositories and services, regardless of whether SQL declares a FOREIGN KEY. Shared entities such as saas_user appear in multiple diagrams to keep the relationship lines readable.
Accounts, authentication, and OAuth 2.0
saas_user.invited_by_id defines referral relationships between users. Sign-in, verification, and OAuth 2.0 records link to users, clients, and authorization codes through fields such as user_id, client_id, and oauth_code.
Roles, entitlements, and Reaction
Role capabilities, entitlement capabilities, and quota change records can all be associated with users. The event_id and command_id fields in capability grant records point to the corresponding Reaction execution records. Quota change records link to the corresponding entitlement capability records through fields such as subject, source, entitlement, and capability.
Payments
payment_customers maps user accounts to payment provider Customers. Checkout, subscriptions, and transactions can be linked through local IDs, or identified as the same business record using provider, connection_id, and provider-issued IDs together. webhook_events stores incoming webhooks that trigger payment data synchronization. A webhook record does not necessarily correspond to just one record in a payment table.
Affiliate program
Commission records link to the affiliate, referred user, transaction, and transaction line item. Subscription commissions use the payment provider, payment connection, and subscription ID to find the corresponding subscription. Monthly settlements aggregate commissions by affiliate, settlement month, and currency, without requiring separate foreign key fields.
Support tickets, notifications, and newsletter subscriptions
user_notification is the association table between users and notifications. Support tickets can link to a requester and an assignee. A requester_id less than or equal to 0 indicates an anonymous submission with no corresponding saas_user. Newsletter subscriptions are recorded by email address alone and have no entity relationship with user accounts.
ANALYTICS_DB
| Item | Value | Description |
|---|---|---|
| Binding name | ANALYTICS_DB | The Worker uses this binding to access the analytics database. |
| Default database name | saavo-template-analytics-db | On the first deployment, the deployment script regenerates this name from the Worker name. |
| Initialization script | schema/analytics-init.sql | Only for initializing an empty database or manually resetting a database. |
| Initialization marker table | analytics_session | db:migrate uses this table to determine whether the analytics database has been initialized. |
Tables
| Table | Purpose |
|---|---|
analytics_session | Visitor identifiers, device information, first activity time, and first receipt time for analytics sessions |
analytics_event | Page views and custom events, along with source, device, region, and performance metrics |
analytics_event_property | Custom event properties |
analytics_session_trait | Custom session-level traits |
Entity relationships
Both analytics_event and analytics_session_trait link to analytics_session through (site_id, session_id). analytics_event_property.event_id links to analytics_event.event_id. All three relationships in the analytics database are also enforced by SQL foreign keys. Event properties and session traits are deleted along with their parent records.
Field conventions
| Convention | Description |
|---|---|
| Time | Unless explicitly stated otherwise in the SQL, fields such as *_at, *_start, and *_end use Unix timestamps in milliseconds. |
| Booleans | D1 stores boolean states as INTEGER, typically using 1 for true and 0 for false. |
| Amounts | *_minor uses the currency's smallest unit, such as fen for Chinese yuan or cents for US dollars. |
| JSON | JSON is a SQLite type declaration. The actual content is stored as text, with JSON validity enforced by the application or a CHECK constraint. |
| Soft deletion | deleted_at typically uses 0 for records that have not been deleted and a nonzero value for the deletion time. |
| Primary keys | WITHOUT ROWID tables store records using their declared primary key and do not provide an additional implicit rowid. |
Initialize databases and update schemas
Both schema/db-init.sql and schema/analytics-init.sql contain DROP TABLE IF EXISTS. Use them only to initialize an empty database or manually reset a database, never to update a production schema.
All database migration files go in schema/migrations. Application database files use the pattern NNNN-db-description.sql, and analytics database files use NNNN-analytics-description.sql. To change a production schema, add a migration file and then run npm run db:migrate:remote.
Do not run initialization scripts on production databases
Initialization scripts delete existing tables and data. db:reset:local and db:reset:remote also clear the database. Use them only when you are certain that you need to rebuild it.