> ## Documentation Index
> Fetch the complete documentation index at: https://docs-terra.withunify.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Complete reference for Terra's 35+ PostgreSQL tables

# Database Schema

> Terra uses PostgreSQL via Supabase with 35+ tables organized into logical domains.

This document provides a tiered reference: detailed coverage for core tables, brief listings for supporting tables.

***

## Schema Overview

```mermaid theme={null}
erDiagram
    FORMS ||--o{ FORM_SUBMISSIONS : receives
    FORMS ||--o{ FORM_VERSIONS : versions
    FOLDERS ||--o{ FORMS : contains
    USER_PROFILES ||--o{ FOLDER_MEMBERS : "belongs to"
    AGENCIES ||--o{ AGENCY_MEMBERS : has
    FORM_SUBMISSIONS ||--o{ ASYNC_OPERATIONS : triggers
    FORMS ||--o{ WEBHOOK_CONFIGS : has
    WEBHOOK_CONFIGS ||--o{ WEBHOOK_EVENTS : logs
```

***

## Core Tables (Detailed)

### forms

The central table storing form definitions and settings.

```sql theme={null}
CREATE TABLE forms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',

  -- Schema storage (JSONB trees)
  draft_schema JSONB,
  published_schema JSONB,

  -- Publishing
  published_at TIMESTAMPTZ,
  scheduled_publish_at TIMESTAMPTZ,

  -- Organization
  folder_id UUID REFERENCES folders(id),

  -- Settings (JSONB for flexibility)
  settings JSONB DEFAULT '{}',

  -- Notifications
  email_field_id TEXT,
  sms_field_id TEXT,
  email_notifications_enabled BOOLEAN DEFAULT true,
  sms_notifications_enabled BOOLEAN DEFAULT false,

  -- Custom code injection
  custom_head_code TEXT,
  custom_body_code TEXT,

  -- Integrations
  google_drive_folder_id TEXT,
  custom_domain TEXT,
  domain_status TEXT DEFAULT 'pending',

  -- Submission settings
  submission_prefix TEXT,
  allow_multiple_submissions BOOLEAN DEFAULT false,
  require_auth BOOLEAN DEFAULT false,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Key columns:**

* `draft_schema` / `published_schema` — Recursive JSON form definitions
* `settings` — JSONB blob for branding, deadlines, etc.
* `folder_id` — Which workspace contains this form
* `submission_prefix` — Custom prefix for reference IDs (e.g., "RENT-")

***

### form\_submissions

Individual submissions with answers stored as JSONB.

```sql theme={null}
CREATE TABLE form_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id UUID NOT NULL REFERENCES forms(id),

  -- Answers (JSONB map of fieldId → value)
  answers JSONB NOT NULL DEFAULT '{}',

  -- Status tracking
  status TEXT NOT NULL DEFAULT 'submitted',
  submission_number INT,
  reference_id TEXT,

  -- Identity
  user_id TEXT,  -- WorkOS user ID (if authenticated)
  applicant_id UUID REFERENCES applicants(id),

  -- Draft support
  is_draft BOOLEAN DEFAULT false,

  -- Plaid tokens (encrypted)
  plaid_access_tokens JSONB DEFAULT '{}',

  -- Timestamps
  submitted_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_submissions_form ON form_submissions(form_id);
CREATE INDEX idx_submissions_status ON form_submissions(status);
CREATE INDEX idx_submissions_reference ON form_submissions(reference_id);
CREATE INDEX idx_submissions_applicant ON form_submissions(applicant_id);
```

**Key columns:**

* `answers` — `{"full-name": "Jane Doe", "income": 45000, ...}`
* `reference_id` — Human-readable ID (e.g., "RENT-2024-001")
* `status` — `submitted`, `under_review`, `approved`, `denied`, etc.
* `plaid_access_tokens` — Encrypted Plaid tokens per field

***

### user\_profiles

Admin users with roles (synced from WorkOS).

```sql theme={null}
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workos_user_id TEXT UNIQUE NOT NULL,
  email TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,

  -- RBAC
  role TEXT NOT NULL DEFAULT 'applicant',
  -- 'super_admin', 'admin', 'user', 'applicant'

  -- Settings
  preferences JSONB DEFAULT '{}',

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Role hierarchy:**

* `super_admin` — Full system access
* `admin` — Dashboard access, manage forms
* `user` — Limited team member
* `applicant` — Portal only, view own applications

***

### applicants

Applicant identity records (separate from admin users).

```sql theme={null}
CREATE TABLE applicants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workos_user_id TEXT UNIQUE,  -- If they created a portal account
  email TEXT,
  phone TEXT,
  first_name TEXT,
  last_name TEXT,

  -- Verification status
  email_verified BOOLEAN DEFAULT false,
  phone_verified BOOLEAN DEFAULT false,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Design note:** Applicants and admin users are separate tables. An applicant can have a portal account (with `workos_user_id`) without being an admin.

***

### folders (Workspaces)

Logical grouping for forms with optional custom domains.

```sql theme={null}
CREATE TABLE folders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  color TEXT,  -- Hex color for UI

  -- Branding
  branding JSONB DEFAULT '{}',
  -- { logo: "...", primaryColor: "#...", ... }

  -- Custom domain
  custom_domain TEXT,
  domain_status TEXT DEFAULT 'pending',

  -- Custom code injection
  custom_head_code TEXT,
  custom_body_code TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

***

### async\_operations

Persistent queue for background jobs.

```sql theme={null}
CREATE TABLE async_operations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  operation_type TEXT NOT NULL,
  -- 'webhook', 'airtable_sync', 'email', 'sms', 'plaid_enrichment'

  payload JSONB NOT NULL,

  -- Linking
  submission_id UUID REFERENCES form_submissions(id),
  form_id UUID REFERENCES forms(id),
  workspace_id UUID REFERENCES folders(id),

  -- State machine
  status TEXT NOT NULL DEFAULT 'pending',
  -- 'pending', 'processing', 'completed', 'failed', 'dead'

  attempts INT NOT NULL DEFAULT 0,
  max_attempts INT NOT NULL DEFAULT 3,

  -- Timing
  last_attempt_at TIMESTAMPTZ,
  next_retry_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,

  -- Error tracking
  last_error TEXT,
  error_history JSONB DEFAULT '[]',
  result JSONB,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

See [Queue Architecture](/core-systems/async/queue-architecture) for details.

***

### airtable\_connections

OAuth tokens and field mappings for Airtable sync.

```sql theme={null}
CREATE TABLE airtable_connections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id UUID UNIQUE REFERENCES forms(id),

  -- OAuth tokens (encrypted)
  access_token TEXT NOT NULL,
  refresh_token TEXT NOT NULL,
  token_expires_at TIMESTAMPTZ NOT NULL,

  -- Selected base/table
  base_id TEXT NOT NULL,
  table_id TEXT NOT NULL,
  table_name TEXT,

  -- Field mapping: { terraFieldId: airtableFieldId }
  field_mappings JSONB DEFAULT '{}',

  -- Status
  is_active BOOLEAN DEFAULT true,
  last_sync_at TIMESTAMPTZ,
  last_error TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

***

### webhook\_configs

Webhook endpoints for forms.

```sql theme={null}
CREATE TABLE webhook_configs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id UUID NOT NULL REFERENCES forms(id),
  name TEXT NOT NULL,
  url TEXT NOT NULL,

  -- Events to trigger on
  events TEXT[] DEFAULT ARRAY['submission.created'],
  -- 'submission.created', 'submission.updated', 'status.changed'

  -- Security
  secret TEXT NOT NULL,  -- For HMAC signing
  headers JSONB DEFAULT '{}',

  -- State
  is_active BOOLEAN DEFAULT true,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

***

### notification\_events

Log of all sent notifications.

```sql theme={null}
CREATE TABLE notification_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id UUID REFERENCES forms(id),
  submission_id UUID REFERENCES form_submissions(id),

  -- Delivery info
  channel TEXT NOT NULL,  -- 'email' or 'sms'
  event_type TEXT NOT NULL,  -- 'submission_receipt', 'status_change'
  recipient TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'sent',
  -- 'sent', 'delivered', 'bounced', 'failed'

  -- Content
  subject TEXT,  -- For email
  metadata JSONB DEFAULT '{}',

  -- Provider info
  provider TEXT,  -- 'resend', 'twilio'
  provider_message_id TEXT,

  -- Error tracking
  error TEXT,

  sent_at TIMESTAMPTZ DEFAULT NOW(),
  delivered_at TIMESTAMPTZ
);
```

***

## Supporting Tables (Brief)

### Agencies & Multi-tenancy

| Table                   | Purpose                                         |
| ----------------------- | ----------------------------------------------- |
| `agencies`              | External partner organizations                  |
| `agency_members`        | Users belonging to agencies                     |
| `agency_clients`        | Clients managed by agencies                     |
| `agency_form_access`    | Form-level access grants                        |
| `user_form_access`      | Admin-managed scoped access for editors/viewers |
| `folder_members`        | User access to workspaces                       |
| `folder_custom_domains` | Custom domains per folder                       |

### Form Features

| Table                 | Purpose                    |
| --------------------- | -------------------------- |
| `form_versions`       | Historical schema versions |
| `form_templates`      | Reusable form templates    |
| `form_sessions`       | Active form view sessions  |
| `form_visits`         | Page view analytics        |
| `form_insights_daily` | Aggregated daily metrics   |
| `form_import_jobs`    | AI form import tracking    |

### Notifications

| Table                        | Purpose                               |
| ---------------------------- | ------------------------------------- |
| `notification_providers`     | Provider credentials (Resend, Twilio) |
| `notification_templates`     | Custom email/SMS templates            |
| `form_notification_settings` | Per-form notification config          |

### Applicant Data

| Table                     | Purpose                  |
| ------------------------- | ------------------------ |
| `applicant_profiles`      | Extended applicant info  |
| `applicant_pii`           | Encrypted sensitive data |
| `applicant_bank_accounts` | Bank account details     |

### Webhooks & Events

| Table                    | Purpose                       |
| ------------------------ | ----------------------------- |
| `webhook_events`         | Outbound webhook delivery log |
| `webhook_inbound_events` | Inbound webhook processing    |

### Audit & History

| Table                       | Purpose                   |
| --------------------------- | ------------------------- |
| `audit_logs`                | All user actions          |
| `submission_status_history` | Status change audit trail |
| `airtable_sync_events`      | Airtable sync history     |

### System

| Table                   | Purpose                       |
| ----------------------- | ----------------------------- |
| `organization_settings` | Global branding, localization |
| `system_settings`       | Platform-wide configuration   |

***

## Row Level Security

Most tables use service role access (RLS bypassed) with application-level permission checks. RLS is enabled but permissive for background operations:

```sql theme={null}
-- Example: Audit logs are insert-only
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow inserts for authenticated"
ON audit_logs FOR INSERT
TO authenticated, service_role
WITH CHECK (true);

CREATE POLICY "Deny all reads"
ON audit_logs FOR SELECT
TO authenticated
USING (false);  -- Only service_role can read
```

***

## Indexes

Key indexes for query performance:

```sql theme={null}
-- Form lookups
CREATE INDEX idx_forms_slug ON forms(slug);
CREATE INDEX idx_forms_folder ON forms(folder_id);
CREATE INDEX idx_forms_status ON forms(status);

-- Submission queries
CREATE INDEX idx_submissions_form ON form_submissions(form_id);
CREATE INDEX idx_submissions_reference ON form_submissions(reference_id);
CREATE INDEX idx_submissions_applicant ON form_submissions(applicant_id);
CREATE INDEX idx_submissions_created ON form_submissions(created_at DESC);

-- Queue processing
CREATE INDEX idx_async_pending ON async_operations(status, next_retry_at)
  WHERE status IN ('pending', 'failed');

-- Audit queries
CREATE INDEX idx_audit_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_user ON audit_logs(user_id, created_at DESC);
```

***

## Migrations

Terra uses numbered SQL migrations in `apps/terra/migrations/`:

```
migrations/
├── 002_add_form_settings.sql
├── 003_add_branding_settings.sql
├── 004_storage_bucket.sql
├── ...
├── 071_*.sql  (latest)
```

Run migrations via Supabase dashboard or CLI. Migrations are idempotent where possible.

***

## Related

<CardGroup cols={2}>
  <Card title="File Storage" icon="folder-open" href="/core-systems/data/file-storage">
    How files are stored securely
  </Card>

  <Card title="Encryption" icon="lock" href="/core-systems/data/encryption-pii">
    PII encryption strategy
  </Card>
</CardGroup>