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

Core Tables (Detailed)

forms

The central table storing form definitions and settings.

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.

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).

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).

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.

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.

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 for details.

airtable_connections

OAuth tokens and field mappings for Airtable sync.

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.

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.

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
`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:

-- 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:

-- 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.

File Storage

How files are stored securely

Encryption

PII encryption strategy

Foundation

Core Systems

Features

Integrations

Operations

Reference

Database Schema

Database Schema

Schema Overview

Core Tables (Detailed)

forms

form_submissions

user_profiles

applicants

folders (Workspaces)

async_operations

airtable_connections

webhook_configs

notification_events

Supporting Tables (Brief)

Agencies & Multi-tenancy

Form Features

Notifications

Applicant Data

Webhooks & Events

Audit & History

System

Row Level Security

Indexes

Migrations

File Storage

Encryption

Foundation

Core Systems

Features

Integrations

Operations

Reference

​Database Schema

​Schema Overview

​Core Tables (Detailed)

​forms

​form_submissions

​user_profiles

​applicants

​folders (Workspaces)

​async_operations

​airtable_connections

​webhook_configs

​notification_events

​Supporting Tables (Brief)

​Agencies & Multi-tenancy

​Form Features

​Notifications

​Applicant Data

​Webhooks & Events

​Audit & History

​System

​Row Level Security

​Indexes

​Migrations

​Related

File Storage

Encryption

Database Schema

Schema Overview

Core Tables (Detailed)

forms

form_submissions

user_profiles

applicants

folders (Workspaces)

async_operations

airtable_connections

webhook_configs

notification_events

Supporting Tables (Brief)

Agencies & Multi-tenancy

Form Features

Notifications

Applicant Data

Webhooks & Events

Audit & History

System

Row Level Security

Indexes

Migrations

Related