Forms Service — Database

Last Updated: 2026-06-28 Status: Active Source of truth: apps/forms-service/src/db/schema.ts · migrations in apps/forms-service/drizzle/ Companion docs: architecture.md · api-spec.md · billing.md

Single Neon PostgreSQL database (FORMS_DATABASE_URL). Every table is tenant-scoped via a tenant_id text column. Credit metering reads from CORE_DATABASE_URL (a separate database — see billing.md).

1. forms

The form definition. The id (form_<uuid>) is the public submit identifier embedded in the endpoint URL.

Indexes

• forms_tenant_slug_unique — partial unique on (tenant_id, slug) WHERE deleted_at IS NULL (deleted forms free their slug). isSlugUniqueViolation() matches on this index name.
• forms_tenant_idx on (tenant_id)
• forms_tenant_status_idx on (tenant_id, status)

2. form_submissions

One row per submission attempt, including spam (so the dashboard can surface / hide it).

Indexes

• form_submissions_tenant_form_submitted_idx on (tenant_id, form_id, submitted_at) — hot path: list a form's submissions newest-first
• form_submissions_tenant_status_idx on (tenant_id, status) — status filter (hide spam / show new)
• form_submissions_form_idx on (form_id) — cascade / join support

data JSONB — file field shape

When a submission includes file uploads, each file field is replaced with a structured reference after upload to Media Service:

{
  "resume": {
    "__file": true,
    "mediaId": "med_01j…",
    "name": "resume.pdf",
    "size": 184320,
    "type": "application/pdf",
    "url": "https://cdn.vlozi.app/…/resume.pdf"
  }
}

3. form_webhook_deliveries

One row per webhook delivery attempt. Created for every submission to a form with a webhook_url.

Indexes: form_webhook_deliveries_submission_idx on (submission_id); form_webhook_deliveries_tenant_status_idx on (tenant_id, status).

4. form_views_daily

Daily view counter per form, updated on every GET /f/:form_id/schema or GET /f/:form_id/page request.

Primary key: (form_id, day) — upsert increments views atomically.

Used by the analytics endpoints to compute views, conversion rate (submissions ÷ views), and daily view series.

5. tenant_forms_usage

Fractional credit accumulator for submission billing. See billing.md for the full metering model.

6. JSONB shapes (TypeScript)

type FormField = {
  name: string;
  type: "text" | "email" | "tel" | "url" | "number" | "textarea"
      | "select" | "checkbox" | "date" | "file";
  label?: string;
  placeholder?: string;
  help?: string;
  required?: boolean;
  min?: number;
  max?: number;
  pattern?: string;      // regex string for text/email/tel
  options?: string[];    // for select fields (max 100 options)
};
 
type FormSchema = { fields: FormField[] };  // max 100 fields
 
type FormSettings = {
  successMessage?: string;    // max 500 chars
  redirectUrl?: string;       // max 2000 chars, valid URL
  theme?: {
    accent?: string;          // hex color, max 32 chars
    buttonLabel?: string;     // max 60 chars
  };
  turnstileSiteKey?: string;  // max 100 chars (widget-side key)
};
 
type FormNotify = {
  email?: { enabled: boolean; to?: string };
  newsletterListId?: string | null;   // Phase 4 — stored, not yet triggered
};
 
type SubmissionFileMeta = {
  __file: true;
  mediaId: string;
  name: string;
  size: number;
  type: string;
  url: string;
};

7. Migrations

Migrations live in apps/forms-service/drizzle/ and are generated by drizzle-kit:

npm run generate   # drizzle-kit generate → new SQL file
npm run migrate    # apply pending migrations against FORMS_DATABASE_URL
npm run db:push    # push schema directly (dev only)

The forms database can share a Neon project with other services or be isolated — it has no cross-service foreign keys. All external relationships (credits, tenant plans) are resolved at runtime via CORE_DATABASE_URL queries, not FK constraints.