Database Overview

Database

PostgreSQL is used. The ORM is Drizzle ORM, and heineken-survey-design-backend/src/db/schema.ts is the single source of truth for table definitions.

Tables

Design Data

Table	Logical name	Description
users	User	System users, created automatically at authentication
surveys	Survey	Surveys, holding the theme, status, and latest version number
survey_permissions	Survey permission	Per-survey sharing permissions (view / edit)
survey_versions	Version	Survey versions, added on every save
survey_version_pointers	Version pointer	Pointer to a survey's latest version
survey_version_sections	Section	Snapshots of the sections belonging to a version
survey_version_questions	Question	Snapshots of the questions belonging to a section
survey_version_question_options	Choice	Choices, matrix rows and columns, and FA input fields
survey_version_question_branch_rules	Branch rule	Logical operator, destination, message
survey_version_question_branch_conditions	Branch condition	Source question, operator, comparison value
survey_version_question_visibility_rules	Visibility rule	Visibility logic rules
survey_version_question_visibility_conditions	Visibility condition	Visibility conditions (identical shape to branch conditions)
survey_version_question_visibility_targets	Hide target	Choices hidden by visibility logic
question_library_items	Past question data	The question library
user_survey_favorites	Favorite	Per-user survey favorites

External Import (Creative Survey)

Table	Logical name	Description
external_surveys	External questionnaire	Questionnaires imported from CS
external_questions	External question	Questions imported from CS
external_answer_items	External choice	Choices imported from CS
external_sub_items	External sub item	Sub items imported from CS (matrix columns, and so on)
external_logics	External branch logic	Branch logic imported from CS
external_logic_items	External branch condition	Branch conditions imported from CS

ER Diagrams

Design Data

erDiagram
    users ||--o{ surveys : "createdBy / updatedBy"
    users ||--o{ survey_permissions : ""
    users ||--o{ user_survey_favorites : ""
    surveys ||--o{ survey_permissions : ""
    surveys ||--o{ survey_versions : ""
    surveys ||--|| survey_version_pointers : "latest"
    surveys ||--o{ user_survey_favorites : ""
    survey_versions ||--o{ survey_version_sections : ""
    survey_version_sections ||--o{ survey_version_questions : ""
    survey_version_questions ||--o{ survey_version_question_options : ""
    survey_version_questions ||--o{ survey_version_question_branch_rules : ""
    survey_version_questions ||--o{ survey_version_question_visibility_rules : ""
    survey_version_question_branch_rules ||--o{ survey_version_question_branch_conditions : ""
    survey_version_question_visibility_rules ||--o{ survey_version_question_visibility_conditions : ""
    survey_version_question_visibility_rules ||--o{ survey_version_question_visibility_targets : ""
    surveys ||--o{ question_library_items : "internal origin"

External Import

erDiagram
    external_surveys ||--o{ external_questions : ""
    external_questions ||--o{ external_answer_items : ""
    external_questions ||--o{ external_sub_items : ""
    external_questions ||--o{ external_logics : ""
    external_logics ||--o{ external_logic_items : ""
    external_surveys ||--o{ question_library_items : "external origin"
    external_questions ||--o| question_library_items : "external origin"

Common Rules

Rule	Contents
Primary keys	uuid (auto-generated with defaultRandom()). Join tables use composite primary keys
Timestamps	timestamp with time zone, converted to ISO 8601 strings in the API
Column names	snake_case. TypeScript properties are camelCase
Deletion	Only surveys is soft deleted (deleted_at); everything else is hard deleted
Cascades	Parent-child foreign keys set onDelete: cascade
Ordering	Managed by sort_order (integer), with a unique index on (parent ID, sort_order)
Enums	PostgreSQL enum types. New values are always appended at the end
Raw external data	Stored in jsonb columns

Enums

Enum	Values
survey_status	下書き / レビュー中 / 出力済み / アーカイブ
permission_role	view / edit
question_type	single / multi / free_text / matrix / intro / pulldown
branch_operator	equals / includes / answered / not_includes / only / not_only / has_other
branch_logical_operator	AND / OR
question_option_axis	row / column

Enum values may only be appended

Inserting a value in the middle produces unstable ADD VALUE BEFORE output from drizzle-kit, so new values must go at the end. This is why pulldown sits last in question_type.

Design Decisions

Version Snapshots

Sections, questions, choices, and branches do not hang off the survey directly; they are stored as snapshots belonging to a version (survey_versions). Every save copies the entire content of the latest version into a new version, so earlier versions never change.

flowchart TD
    S[surveys] --> V1[survey_versions v1]
    S --> V2[survey_versions v2]
    S --> V3[survey_versions v3<br/>latest]
    V3 --> Sec[survey_version_sections]
    Sec --> Q[survey_version_questions]
    Q --> Opt[survey_version_question_options]
    Q --> BR[branch_rules]
    Q --> VR[visibility_rules]

Stable IDs

Snapshot row ids are re-assigned when the version changes, but stable_section_id / stable_question_id are carried over unchanged during copying. This lets the question library find the corresponding question in the latest version even when it points at an older snapshot.

Why Branch Conditions Reference Labels

Updating a question recreates its choices, so choice ids are not stable. Branch conditions and visibility conditions therefore reference their target by label string rather than by choice ID.

Symmetry of Branching and Visibility

The visibility condition table (visibility_conditions) has the same column layout as the branch condition table (branch_conditions) and shares the branch_operator enum. The only difference is whether the rule holds a destination or a list of hide targets. This mirrors CS keeping logics and visibilities as symmetric, separate APIs.

Migrations

Command	Contents
bun run db:generate	Generate migration SQL in drizzle/ from changes to schema.ts
bun run db:migrate	Apply migrations to the database
bun run db:seed	Insert development seed data
bun run db:studio	Launch a GUI for inspecting the database in the browser

In deployed environments, a dedicated migration Lambda runs when drizzle/** changes. See Infrastructure for details.