Design Rationale — Integrations
Non-obvious design choices for the integrations module — the WHY behind each decision.
Integrations Module (Locked 2026-06-11)
DR1 — Generic connector framework, not per-integration tables
Decision: One connector table with a connector_type free-text discriminator routes all connector-specific logic. QuickBooks, Shopify, Resend, Twilio, Picas CSV all use the same 9 tables with different connector_type values. No connector gets its own schema tables.
Why: Per-integration tables (quickbooks_sync_run, shopify_sync_run, etc.) create N identical schemas that diverge over time. Vrida expects 8–12 integration types across v1.0–v1.5. A generic framework means one migration per framework improvement instead of N. The connector_type discriminator is sufficient for routing — connectors differ in auth type, sync cadence, and entity mapping, none of which require separate tables.
Rejected: Per-integration tables (quickbooks_sync, shopify_sync, etc.) — schema proliferation with no structural benefit; every framework improvement becomes an N-table migration.
DR2 — Config-vs-runtime split + writeback
Decision: admin.integration_config owns the config (enabled/disabled toggle, credentials_ref vault pointer, sync_schedule). admin.webhook_config owns outbound endpoint definitions. integrations.connector owns the runtime. Integrations reads Admin config at execution time and writes back last_sync_at/last_sync_status to admin.integration_config after each sync run.
Why: Config and runtime have different change cadences and different owners. Config is tenant-configured (set-once, infrequent change); runtime is system-written (updates on every sync). Merging them into one table means every sync-run update touches the config row — creating write contention on the config record and making it harder to audit intentional config changes vs system heartbeat updates. Separating them mirrors the Billing→Purchasing writeback pattern (Billing writes paid_at/payment_status_ref back to purchasing.vendor_invoice) — a controlled cross-schema write with a defined seam.
Rejected: Storing config + runtime in a single table. Duplicating Admin config columns (is_enabled, credentials_ref, sync_schedule) into integrations.connector — would create two sources of truth for config with no FK to enforce consistency.
Guard: Never add is_enabled, credentials_ref, or sync_schedule to any integrations.* table. These belong to admin.integration_config. Integrations reads them; never duplicates them.
DR3 — provider_call mechanical-not-delivery (reference-don't-copy)
Decision: provider_call records only the mechanical API-call state: did the HTTP request to Resend/Twilio/FCM succeed, get rate-limited, enter retry? It stores the provider_message_ref returned by the provider. It does NOT store recipient, content, delivery outcome, or engagement. notifications.delivery_attempt is the authoritative business record for delivery status and engagement.
Why: provider_call and notifications.delivery_attempt serve different concerns and different readers. provider_call is for connector-layer observability: rate-limit debugging, retry exhaustion, dead-letter; read by Integrations engineers. delivery_attempt is for business delivery reporting: delivered/bounced/opened/clicked; read by notification workflows, journey branches, and engagement analytics. Merging them creates a table with mixed mutability (mechanical state updates on every retry; engagement timestamps update on provider webhooks) and mixed ownership (Integrations owns the API-call lifecycle; Notifications owns the delivery outcome). provider_message_ref is the single handoff value — Integrations returns it from IntegrationsService.send(); Notifications stores it on delivery_attempt. Integrations never writes to delivery_attempt; Notifications never reads provider_call for business logic.
Rejected: A shared delivery-and-call table. Adding recipient/content/engagement columns to provider_call (would make Integrations a partial second source of truth for delivery data — violating single-ownership).
Guard: Never add recipient_customer_id, recipient_user_id, content, subject, body, template_id, delivery_status, opened_at, or clicked_at to provider_call. notifications.delivery_attempt is the authoritative delivery record. provider_message_ref on provider_call is a handoff — not business data.
DR4 — Owner-processes-own-webhooks (no central cross-module webhook table)
Decision: Each module processes inbound webhooks for the integrations it owns. Payments owns Stripe inbound webhooks (payments.stripe_event_log). Integrations owns QuickBooks', Twilio's, etc. (integrations.connector_webhook_event). There is no central cross-module webhook table.
Why: A central webhook table would require a routing layer that knows which module processes which event type. This routing logic would become a cross-cutting dependency, and the central table would couple Payments, Integrations, and any future modules at the schema level. The module that owns the integration understands the event schema, the idempotency contract, and the processing semantics. Ownership co-location means each module can evolve its event processing independently without touching a shared table. The payments.stripe_event_log pattern was established first and works correctly — connector_webhook_event follows the same design for Integrations-owned providers.
Rejected: A central webhook_event table spanning all modules — requires a routing layer; couples all modules at the schema level; ownership is ambiguous.
Guard: Do NOT add a central cross-module webhook table. Do NOT add Stripe events to connector_webhook_event — Payments owns Stripe's inbound webhooks via stripe_event_log. The module that owns the integration owns that integration's inbound webhooks.
DR5 — connector_credential rotating OAuth vs admin.integration_config.credentials_ref static config
Decision: Two separate credential references co-exist: (1) admin.integration_config.credentials_ref — a vault pointer to the initial OAuth grant (written once at setup, infrequently changes); (2) integrations.connector_credential.access_token_ref + refresh_token_ref — vault pointers to the live rotating access/refresh tokens (updated hourly by the token refresh sweep).
Why: OAuth access tokens expire frequently (typically 1 hour for QuickBooks). The refresh cycle must update the live tokens without touching the Admin config row — which is owned by the tenant and would create confusing "last modified" timestamps on a config record they didn't touch. Separating the rotating token state from the static grant config also means the Admin config row is stable for auditing intentional configuration changes, while the credential row handles the automated token lifecycle independently.
Rejected: Storing rotating tokens in admin.integration_config (pollutes config history with automated system updates; couples token lifecycle to tenant config management). Storing the initial grant pointer in connector_credential (the initial grant is config, not runtime state — it belongs with the rest of the integration config in Admin).
Guard: Never merge credentials_ref (Admin's static vault pointer) with access_token_ref/refresh_token_ref (Integrations' rotating token state). admin.integration_config.credentials_ref is the OAuth grant vault pointer — written at setup. integrations.connector_credential holds the live rotating tokens — written by the automated refresh sweep.
DR6 — Global unique on connector_webhook_event.external_event_id
Decision: UNIQUE (external_event_id) is global — not tenant-scoped (UNIQUE (tenant_id, external_event_id)) and not a partial index with WHERE deleted_at IS NULL.
Why: Provider event IDs (QuickBooks, Twilio, etc.) are globally unique UUIDs generated by the provider without tenant scope. Webhooks from providers arrive before tenant resolution — the webhook handler receives the event and inserts it into connector_webhook_event before it can resolve which tenant the event belongs to (tenant_id is nullable for exactly this reason). A tenant-scoped unique (UNIQUE (tenant_id, external_event_id)) would allow a second insert for the same event while tenant_id is still NULL — defeating the idempotency guarantee. A partial-by-deleted_at unique is inapplicable because webhook events are never deleted (insert-then-update-status pattern). The global unique must be unconditional.
Rejected: UNIQUE (tenant_id, external_event_id) — NULL tenant_id doesn't collide in Postgres (NULL ≠ NULL), defeating dedup before tenant resolution. UNIQUE (external_event_id) WHERE deleted_at IS NULL — no deleted_at on this table; the partial would be incorrect syntax / no-op. Standard UNIQUE (external_event_id) without any partial is the only correct choice.
Guard: Do NOT change the UNIQUE on external_event_id to a tenant-scoped or partial unique. Provider event IDs are globally unique. Webhooks arrive before tenant resolution. The dedup must be global and unconditional.
DR7 — source_type = 'file' is deterministic import; AI zero-mapping is the AI module
Decision: connector.source_type = 'file' handles deterministic, known-format file imports (Picas CSV, templated bulk loads). The field map is configured in integrations.field_mapping; the sync runs via integrations.sync_run. AI zero-mapping — where the AI infers structure from an arbitrary uploaded file — belongs to the AI module (or a dedicated onboarding cluster designed alongside the AI module).
Why: source_type = 'file' imports have a known schema (the Picas CSV format is documented; the mapping is configured ahead of time in field_mapping). The Integrations framework handles batch processing, retry, and error logging correctly for this case. AI zero-mapping imports are fundamentally different: the AI must identify what kind of data is in the file, infer field mappings, and handle arbitrary formats. This is AI module territory (requires AIService; the zero-mapping magic is the AI inference, not the sync execution). Conflating them would put AI inference logic inside the connector framework, which owns none of the AI infrastructure.
Rejected: Extending source_type to cover AI-mapped imports within the Integrations schema (would require AI inference logic in the connector framework; misplaces the AI dependency). import_job/import_file/import_record tables in Integrations (relocated to AI module — see PROJECT_DECISIONS "Integrations Module Scope (2026-06-11)").
Guard: source_type = 'file' = deterministic, known-format imports with a pre-configured field_mapping. Do NOT add AI inference, model calls, or dynamic schema detection to integrations.*. AI zero-mapping belongs to the AI module.
DR8 — Auth providers not here
Decision: SSO/SAML/OIDC/social-login authentication providers are handled by Supabase Auth + identity.sso_provider config. integrations.connector connects Vrida to business systems (QuickBooks, Shopify, Resend, Twilio) — never to auth providers.
Why: Auth providers require a fundamentally different flow (redirect-based OAuth, PKCE, session management, user provisioning on first login) and are owned by the Identity module and Supabase Auth. Adding them to the Integrations connector framework would conflate two categorically different integration patterns: business-system data sync/execution vs user authentication. Identity already has identity.sso_provider for SSO configuration.
Rejected: Auth provider connectors in integrations.connector (wrong abstraction; Identity/Supabase Auth owns auth).
Guard: Never add SSO, SAML, OIDC, Google Sign-In, Apple Sign-In, or any authentication-flow connectors to integrations.connector. Auth providers = Supabase Auth + identity.sso_provider.