Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The Database Schema and Migrations system in Comply-Quick provides a robust foundation for managing legal compliance data, user projects, and automated regulatory tracking. The architecture relies on Supabase for data persistence, utilizing SQL migrations to define relational structures, enforce business logic via check constraints, and secure data through Row Level Security (RLS) policies.
The schema is divided into distinct functional areas: core project management, the "Autopilot" system for tracking regulatory changes, and a legal review queue for administrative oversight. This structure ensures that compliance packages generated for users are versioned, traceable, and subject to professional review.
Relational Architecture & Data Model
The database is structured to support the lifecycle of a compliance package, from initial generation through ongoing regulatory monitoring.
Core Entities and Relationships
The following ER diagram illustrates the primary relationships between regulatory data, user projects, and document versions.
The system tracks global regulations and their history in regulation_versions. Users own projects, which link to multiple document_versions representing proposed or accepted compliance updates.
Sources: supabase/migrations/0002_autopilot.sql:11-80, supabase/migrations/0015_legal_review_queue.sql:19-40
Autopilot System Tables
The Autopilot system monitors external regulations and proposes document updates when changes occur.
| Table Name | Description | Key Fields |
|---|---|---|
regulations | Global catalog of monitored jurisdictions. | id, name, region, content_hash |
regulation_versions | Append-only history of regulatory changes for auditing. | regulation_id, version, change_note |
document_versions | Proposals for regenerated compliance packages. | status, triggered_by, diff, package_markdown |
notifications | In-app alerts for users regarding proposed documents. | type, title, read |
Sources: supabase/migrations/0002_autopilot.sql:11-105
Implementation Details
Autopilot Workflow
When a regulation changes, the Autopilot pipeline computes a diff between the stored project and a new generation. This logic is supported by the document_versions table, which stores the status of these proposals.
The status of a document version is constrained to: proposed, accepted, rejected, or superseded.
Sources: supabase/migrations/0002_autopilot.sql:47-50, src/lib/autopilot/pipeline.ts:69-90
Administrative Legal Review
The system includes a dedicated legal_review_items table to manage the quarterly review of legal artifacts such as preambles and clauses.
| Field | Type | Constraint / Default |
|---|---|---|
category | text | clause_template, regulation, disclaimer, tos |
status | text | pending, approved, changes_requested |
next_review_at | date | Default: now() + interval '3 months' |
The table is seeded with critical artifacts including REPORT_DISCLAIMER, LIABILITY_CAP, and FRAMEWORK_LIABILITY_CLAUSES.
Sources: supabase/migrations/0015_legal_review_queue.sql:19-58
Security and Access Control
Row Level Security (RLS) is used to ensure data isolation and administrative protection.
- Global Reference Data: Tables like
regulationsandregulation_versionsallowSELECTfor all authenticated users but restrict writes to the service role (cron jobs). - User Data: Users can only
SELECTorUPDATEtheir owndocument_versionsandnotificationsusing theauth.uid() = user_idpolicy. - Administrative Data:
legal_review_itemshas RLS enabled with NO permissive policies. Access is restricted to the service-role client gated behind an admin-email allowlist in the application code.
Sources: supabase/migrations/0002_autopilot.sql:111-157, supabase/migrations/0015_legal_review_queue.sql:42-45
Data Structures for Compliance
The database supports complex JSON structures for compliance scoring and API usage.
-- Example of Document Version structure from Migration 0002
create table if not exists public.document_versions (
id uuid primary key default gen_random_uuid(),
project_id uuid not null references public.projects (id) on delete cascade,
status text not null default 'proposed' check (status in ('proposed', 'accepted', 'rejected', 'superseded')),
diff jsonb not null default '{}'::jsonb,
package_markdown text not null default '',
compliance_score jsonb,
created_at timestamptz not null default now()
);
The compliance_score field stores a JSON object containing keys such as overall, contractProtection, privacyCoverage, and preLaunchReadiness.
Sources: supabase/migrations/0002_autopilot.sql:43-62, src/components/ClauseEngine.tsx:57-63
API Usage Tracking
Metered usage for API access is tracked via the platform, although schema definitions for usage meters are primarily surfaced in the frontend views and documented in the API Reference.
| Meter Key | Label | Description |
|---|---|---|
api_call | API calls | General metered call quantity. |
api_template_upload | Template uploads (API) | Billed at $50.00 per upload. |
extra_scan | Scan overage | Billed for scans beyond plan limits. |
Sources: src/app/dashboard/api/ApiView.tsx:12-20
Summary
The Database Schema & Migrations system in Comply-Quick provides a secure, versioned environment for legal technical data. By leveraging Supabase's RLS and SQL constraints, it ensures that user compliance data is isolated while maintaining a global, auditable history of regulatory changes through the Autopilot system. The inclusion of a Legal Review Queue further strengthens the project's commitment to accuracy by enforcing periodic professional oversight of the legal templates.