Database Schema

Complete reference of all database tables, relationships, and enums in the Intrex schema.

Entity Relationships

tenants → users, branches, domains, connectors, templates, instances

branches → obligation_instances, domains, notification_routes

obligation_templates → obligation_instances

obligation_instances → obligation_documents

domains → ssl_check_results

connectors → notification_routes, notification_deliveries

notification_events → notification_deliveries, acknowledgements

Tables

tenants

— Customer organizations

Fields:

id (uuid PK)name (varchar)statusstripeCustomerIdplanNamecreatedAt

Relations:

usersbranchesdomainsconnectors

users

— Application users with roles

Fields:

id (serial PK)tenantId (FK)emailpasswordHashrole (enum)status

Relations:

tenantobligationInstancesacknowledgements

branches

— Business locations

Fields:

id (uuid PK)tenantId (FK)codenamecityCorporationdistrictregion

Relations:

tenantobligationInstancesdomains

jurisdictions

— Geographic compliance zones

Fields:

id (uuid PK)countryCoderegiondistrictcityCorporationlabel

Relations:

obligationTemplates

obligation_templates

— Reusable compliance rules

Fields:

id (uuid PK)tenantId (FK)jurisdictionId (FK)categorytitlerecurrenceTypeseverity

Relations:

tenantjurisdictioninstances

obligation_instances

— Actual compliance items

Fields:

id (uuid PK)tenantId (FK)branchId (FK)templateId (FK)categorystatusdueAtcompletedAt

Relations:

tenantbranchtemplatedocuments

obligation_documents

— Proof attachments

Fields:

id (uuid PK)obligationInstanceId (FK)storageKeyfilenamemimeTypesizeBytes

Relations:

obligationInstance

domains

— SSL-monitored domains

Fields:

id (uuid PK)tenantId (FK)branchId (FK)hostnameportstatuslastCheckedAt

Relations:

tenantbranchsslCheckResults

ssl_check_results

— SSL check history

Fields:

id (uuid PK)domainId (FK)checkStatusvalidFromvalidTodaysRemainingissuerCn

Relations:

domain

connectors

— Notification channels

Fields:

id (uuid PK)tenantId (FK)type (enum)namestatusconfigEncryptedJson

Relations:

tenantroutes

notification_routes

— Routing rules

Fields:

id (uuid PK)tenantId (FK)branchId (FK)connectorId (FK)eventTypeseverityMinrecipientRef

Relations:

tenantbranchconnector

notification_events

— Queued notifications

Fields:

id (uuid PK)tenantId (FK)eventTypefingerprintpayloadJsonscheduledForstatus

Relations:

tenantdeliveriesacknowledgement

notification_deliveries

— Delivery attempts

Fields:

id (uuid PK)notificationEventId (FK)connectorId (FK)attemptNodeliveryStatussentAt

Relations:

notificationEventconnector

acknowledgements

— User acknowledgments

Fields:

id (uuid PK)notificationEventId (FK)ackByUserId (FK)ackNoteackAt

Relations:

notificationEventuser

activity_logs

— Audit trail

Fields:

id (serial PK)tenantId (FK)userId (FK)actionentityTypeentityIdbeforeJsonafterJson

Relations:

tenantuser

Enumerations

app_role

head_office_adminbranch_manageroperator

obligation_category

trade_licensefire_safetytax_vatenvironmental_permitinspection_renewal

obligation_status

upcomingdue_todayoverduecompletedwaived

severity

lowmediumhighcritical

connector_type

email_smtptelegram_botwhatsapp_businesswebhook

connector_status

activedisablederrorpending_verification

ssl_check_status

okwarningexpiredhandshake_faileddns_failedtimeouthostname_mismatch

notification_event_type

obligation_dueobligation_overduessl_expiryssl_failuredigest

notification_status

queuedprocessingsentfailedcancelledackeddead_letter

Row Level Security

All tenant-scoped tables have RLS policies enforced. Users can only access rows wheretenantIdmatches their session tenant.

-- Example RLS Policy
CREATE POLICY tenant_isolation ON obligation_instances
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Key Indexes

Table	Index	Purpose
obligation_instances	(tenant_id, status, due_at)	Dashboard filtering
notification_events	(fingerprint)	Deduplication
ssl_check_results	(domain_id, checked_at)	History queries
activity_logs	(tenant_id, timestamp)	Audit queries