Skip to content

data-model.md

Latest commit

 

History

History
137 lines (104 loc) · 10.9 KB

data-model.md

File metadata and controls

137 lines (104 loc) · 10.9 KB
Raw

Data model and reporting

Storage choice

Forms are defined dynamically without code deployments or database migrations for each new form. This implementation uses a typed entity/attribute/value model with strict ownership and scalar constraints, then publishes conventional named-column SQL views for reports. This is a deliberate choice between two valid interpretations of structured forms:

Approach Adding a form Reporting Schema changes
Implemented: typed relational answers plus named views API / CMS operation Stable typed columns per named form revision New immutable revision and view
Dedicated physical table per form Migration and generated Prisma model Direct table columns Reviewed schema migration and deployment

No answers or form definitions are saved into JSON columns, serialized objects, or comma-separated values. JSON remains the HTTP transport format. The database stores field names, validation rules, and option definitions in real columns and related rows.

erDiagram
    Form ||--o{ FormVersion : versions
    FormVersion ||--o{ FormField : defines
    FormField ||--o{ FieldOption : choices
    FormVersion ||--o{ Submission : receives
    Submission ||--o| SubmissionEvent : delivery
    Submission ||--o{ Answer : contains
    FormField ||--o{ Answer : constrains
    Answer ||--o{ AnswerSelection : selects
    FieldOption ||--o{ AnswerSelection : allows
Loading

Tables and invariants

Table Purpose and important constraints
Form UUID identity and globally unique immutable snake_case key.
FormVersion Title, description, access policy, immutable definition fingerprint, sequential version number, status, audit actors/times. At most one published version per form.
FormField Named key unique within a version; explicit type, position, required flag, text length and numeric bounds.
FieldOption Named option key and label, owned by a field/version.
Submission Exact version, server timestamp, UUID idempotency key, canonical request hash, verified member ID, and optional source page path.
SubmissionEvent Optional one-to-one delivery receipt with submission UUID and nullable publication timestamp; cascades on submission deletion.
Answer One scalar value in a type-specific column, or a multi-select answer parent; unique per submission/field.
AnswerSelection One row per selected option, with composite ownership foreign keys and duplicate prevention.

Composite foreign keys ensure:

  • An answer's submission and field belong to the same version.
  • Its discriminator matches the field's declared type.
  • A selected option belongs to the answer's field and version.
  • Selection rows belong only to multi-select answers.

SQL CHECKs enforce scalar presence and prevent simultaneous values in incompatible columns. Deferred constraint triggers check required answers, configured lengths/numeric bounds, and nonempty multi-select answers after the entire submission transaction is written. Immediate checks would incorrectly reject the envelope before its answers exist.

The API additionally validates email syntax, exact decimal input precision, real calendar dates, safe field keys, definition consistency, option allowlists, and unknown fields. Database triggers reject changes to published/retired definitions and answer updates. No API allows submission editing. Deleting a whole submission through a controlled retention job cascades to answers/selections.

API form registration and revisions are serialized on the stable form row. Publication, retirement, and submission acceptance use the same lock. This favors simple consistency for occasional website forms; it serializes submissions to the same form. Revisit this locking strategy if measured submission volume requires greater throughput.

Kafka opt-in creates a SubmissionEvent row atomically with the submission. Its publication timestamp is updated after Bus API acceptance in a separate transaction; submission envelopes and answers remain immutable. Delivery retries lock this row to prevent concurrent duplicate sends. A failed or unacknowledged delivery remains pending until the caller retries. No JSON payload is stored; the validated request and immutable revision reconstruct the event.

Field semantics

  • INTEGER is PostgreSQL integer (signed 32-bit), sent as a JSON number.
  • DECIMAL is numeric(20,6): up to 14 integer digits and 6 fractional digits. Send exact decimal strings, not JavaScript floating-point numbers. HTTP reports return exact strings. SQL views retain the numeric type.
  • DATE is a calendar date from year 0001 through 9999; no timezone conversion. Send YYYY-MM-DD.
  • BOOLEAN accepts both true and false. “Required” means an explicit answer, not mandatory consent. Use a required single-choice acceptance field with only the accepted option if affirmative acknowledgement is needed.
  • Missing, null, blank, or empty-array optional answers are omitted. False and zero are retained. Missing required answers are rejected.
  • Choices use stable keys for reports; labels are presentation copy. Multi-selects are rows in storage and text[] in SQL views.

Versioned views

Publishing event_interest version 1 creates forms.event_interest_v1 in the same transaction as the publication status change. If view creation fails, publication and retirement of the previous version roll back.

View metadata columns are reserved and cannot be used as field keys:

Column SQL type
submission_id uuid
submitted_at timestamptz
member_id varchar, nullable
source_page varchar, nullable
form_version integer

Every remaining column has the field's name and real SQL type. Missing optional values are null. Identifiers are limited and validated before DDL construction; UUID literals are validated separately. All ordinary queries use Prisma or parameterized SQL. The API accepts no arbitrary SQL.

-- One row per submitted form, including exact named columns.
SELECT submission_id, submitted_at, email, full_name,
       interests, receive_updates
FROM forms.event_interest_v1
WHERE submitted_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00';

-- Aggregate relational choices using a conventional SQL array projection.
SELECT interest, count(*) AS submissions
FROM forms.event_interest_v1
CROSS JOIN LATERAL unnest(interests) AS interest
GROUP BY interest;

-- Explicitly combine compatible columns across revisions.
SELECT submission_id, submitted_at, email, 1 AS revision
FROM forms.event_interest_v1
UNION ALL
SELECT submission_id, submitted_at, email, 2 AS revision
FROM forms.event_interest_v2;

There is no automatically changing “latest” report view: silently changing its column types would break reports. Pin a revision, or create an explicitly reviewed cross-version report. Field semantics may change between versions, so a cross-version union is a reporting decision.

SQL views are computed rather than materialized. They are indexed through the submission/version and submission/field indexes on the base tables. For large BI workloads, use a reporting replica or a warehouse job based on these stable views.

Lifecycle and retention

The service supports DRAFT -> PUBLISHED -> RETIRED. Published versions cannot return to draft; retired versions cannot be reopened. Create the next sequential revision to reopen or change a form. Draft content becomes immutable through the API when first saved; local Payload drafts can be edited freely until synchronized.

Versions and historical reporting views are retained. The service does not impose an arbitrary retention period or add deletion endpoints. An approved retention policy can delete whole submission envelopes in batches; cascading foreign keys remove child data. Definition records remain for interpretation of retained historical data. Reports and database grants expose personal submission information only to their authorized readers.

Submission processing

ProcessorFlow, ProcessorEvent, and ProcessorDelivery support forms-processor-v6. The API owns their migrations; the processor uses these schema-qualified tables without running DDL at startup. A flow has a stable ID, form key, enabled flag, AND-combined answer predicates (rules.all), action name, and JSON action settings. The seeded lets-talk-sales-email flow matches all lets-talk submissions and is disabled with TBD recipients, sender, and template. Manage it externally in SQL; set updatedAt when changing settings. Disabling delivery preserves queued work.

The inbox stores the submitted event before Kafka commit. Routing records a unique receipt per submission/flow, including disabled matching flows. Retries read current action settings. No matching flow means the event is retained but has no actions. Flow additions/rule edits apply to events not yet routed; replay requires explicitly clearing routedAt and never deletes existing delivery receipts. Bus API email-event acceptance and the receipt cannot be atomic; a crash in between can cause a duplicate email.

Processor events contain answers and must be included in personal-data retention and erasure procedures separately from Submission. Deleting a ProcessorEvent cascades its delivery records and removes deduplication protection, so retain it through the Kafka retention/replay window. No automatic deletion policy is enabled. See forms-processor-v6/README.md for rules, settings, replay, and operational SQL.

The sendgrid-email action name is retained for compatibility, but forms-processor-v6 now publishes the v3 email contract to external.action.email through Bus API. deliveredAt records Bus API acceptance; email-service-v6 handles provider delivery and retries. Optional fromEmail overrides that service's default sender.