logicspike/docs

forms

Forms Service — Database

Last Updated: 2026-06-23 Status: Draft (v1 — Web3Forms parity) Source of truth: apps/forms-service/src/db/schema.ts · migration apps/forms-service/drizzle/0000_stormy_mongu.sql Companion docs: architecture.md · domain-model.md · api-spec.md

Single Neon PostgreSQL database (FORMS_DATABASE_URL). Every table is tenant-scoped via a tenant_id text column; there is no billing/core DB dependency in v1.

1. forms

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

Column Type Notes
id text PK form_<uuid> — public, safe in client HTML
tenant_id text NOT NULL Owning workspace
name text NOT NULL Internal label
slug text NOT NULL URL-friendly; unique per tenant (live rows)
schema jsonb NOT NULL { fields: FormField[] }; default {"fields":[]} (empty = accept any fields, Web3Forms parity)
settings jsonb NOT NULL { successMessage?, redirectUrl?, theme? }; default {}
notify jsonb NOT NULL { email?: { enabled, to? }, newsletterListId? }; default {}
webhook_url text Phase-4 fan-out target (stored now, unused in v1)
allowed_origins jsonb NOT NULL string[] CORS allowlist; default [] (empty = any origin)
honeypot_field text NOT NULL Hidden field name a bot fills; default botcheck
captcha_required boolean NOT NULL Require a valid Turnstile token; default false
status text NOT NULL active | paused | archived; default active
created_by text User id from the gateway (nullable)
created_at timestamp now()
updated_at timestamp now(), bumped on every update
deleted_at timestamp Soft-delete tombstone; NULL = live

Indexes

  • forms_tenant_slug_unique — partial unique on (tenant_id, slug) WHERE deleted_at IS NULL (a deleted form frees its slug). Name is stable — isSlugUniqueViolation() matches on it.
  • forms_tenant_idx on (tenant_id)
  • forms_tenant_status_idx on (tenant_id, status)

2. form_submissions

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

Column Type Notes
id text PK sub_<uuid>
form_id text NOT NULL FK → forms.id, ON DELETE CASCADE
tenant_id text NOT NULL Denormalized for tenant-scoped queries
data jsonb NOT NULL User field values (control fields stripped)
meta jsonb NOT NULL referer, origin, utm_*; default {} (Phase-3 enrichment lands here)
status text NOT NULL new | seen | handled | spam; default new
spam_reason text honeypot | captcha; null when not spam
source_ip text cf-connecting-ip
user_agent text Request UA
submitted_at timestamp now()
handled_at timestamp Set when status → handled

Indexes

  • form_submissions_tenant_form_submitted_idx on (tenant_id, form_id, submitted_at) — inbox 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

3. form_webhook_deliveries (Phase 4 — scaffold only)

Created now so the schema is stable; no code writes to it in v1.

Column Type Notes
id text PK wd_<uuid>
submission_id text NOT NULL FK → form_submissions.id, ON DELETE CASCADE
tenant_id text NOT NULL
url text NOT NULL Delivery target
attempt integer NOT NULL default 0
status_code integer Last HTTP status
response_body text Truncated response
status text NOT NULL pending | success | failed | retrying; default pending
delivered_at timestamp
created_at timestamp now()

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

4. jsonb shapes (from schema.ts)

type FormField = {
  name: string;
  type: "text" | "email" | "tel" | "url" | "number" | "textarea" | "select" | "checkbox" | "date";
  label?: string; required?: boolean; min?: number; max?: number; options?: string[];
};
type FormSchema   = { fields: FormField[] };
type FormSettings = { successMessage?: string; redirectUrl?: string; theme?: Record<string,string> };
type FormNotify   = { email?: { enabled: boolean; to?: string }; newsletterListId?: string | null };

5. Migrations

drizzle-kit generate against src/db/schema.ts → drizzle/0000_stormy_mongu.sql. Apply with npm run migrate (drizzle-kit) or npm run db:push against FORMS_DATABASE_URL. The forms DB can share the same Neon project as other services or be its own — it has no cross-service foreign keys.

forms