kereru-forms/backend/internal/database/migrations.go

package database

import (
	"context"
	"fmt"

	"github.com/google/uuid"
)

func newID() string { return uuid.NewString() }

type migration struct {
	version    int
	name       string
	statements string
}

var migrations = []migration{
	{
		version: 1,
		name:    "initial schema",
		statements: `
CREATE TABLE IF NOT EXISTS forms (
	id                 TEXT PRIMARY KEY,
	form_group_id      TEXT NOT NULL,
	version            INTEGER NOT NULL DEFAULT 1,
	title              TEXT NOT NULL,
	description        TEXT,
	introduction       TEXT,
	is_active          INTEGER NOT NULL DEFAULT 1,
	requires_access_code INTEGER NOT NULL DEFAULT 0,
	logo_url           TEXT,
	company_name       TEXT,
	contact_email      TEXT,
	contact_phone      TEXT,
	contact_url        TEXT,
	created_at         TEXT NOT NULL,
	updated_at         TEXT NOT NULL,
	UNIQUE (form_group_id, version)
);
CREATE INDEX IF NOT EXISTS idx_forms_group ON forms (form_group_id);
CREATE INDEX IF NOT EXISTS idx_forms_active ON forms (is_active);

CREATE TABLE IF NOT EXISTS questions (
	id            TEXT PRIMARY KEY,
	form_id       TEXT NOT NULL REFERENCES forms (id) ON DELETE CASCADE,
	position      INTEGER NOT NULL,
	question_key  TEXT NOT NULL,
	label         TEXT NOT NULL,
	description   TEXT,
	type          TEXT NOT NULL,
	required      INTEGER NOT NULL DEFAULT 0,
	options_json  TEXT,
	min_value     REAL,
	max_value     REAL,
	placeholder   TEXT,
	multiline     INTEGER NOT NULL DEFAULT 0,
	UNIQUE (form_id, question_key)
);
CREATE INDEX IF NOT EXISTS idx_questions_form ON questions (form_id);

CREATE TABLE IF NOT EXISTS submissions (
	id           TEXT PRIMARY KEY,
	form_id      TEXT NOT NULL REFERENCES forms (id) ON DELETE CASCADE,
	form_version INTEGER NOT NULL,
	user_id      TEXT NOT NULL,
	user_id_type TEXT NOT NULL,
	submitted_at TEXT NOT NULL,
	ip_address   TEXT,
	user_agent   TEXT,
	locale       TEXT,
	timezone     TEXT,
	place        TEXT,
	referer      TEXT
);
CREATE INDEX IF NOT EXISTS idx_submissions_form ON submissions (form_id);
CREATE INDEX IF NOT EXISTS idx_submissions_user ON submissions (user_id);

CREATE TABLE IF NOT EXISTS answers (
	id            TEXT PRIMARY KEY,
	submission_id TEXT NOT NULL REFERENCES submissions (id) ON DELETE CASCADE,
	question_id   TEXT NOT NULL,
	question_key  TEXT NOT NULL,
	value_json    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_answers_submission ON answers (submission_id);

CREATE TABLE IF NOT EXISTS access_codes (
	id            TEXT PRIMARY KEY,
	form_id       TEXT NOT NULL REFERENCES forms (id) ON DELETE CASCADE,
	code          TEXT NOT NULL UNIQUE,
	used          INTEGER NOT NULL DEFAULT 0,
	used_at       TEXT,
	submission_id TEXT,
	created_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_codes_form ON access_codes (form_id);
`,
	},
	{
		version: 2,
		name:    "signatures and offline pdf",
		statements: `
ALTER TABLE forms ADD COLUMN pdf_url TEXT;
ALTER TABLE forms ADD COLUMN signature_required INTEGER NOT NULL DEFAULT 0;
ALTER TABLE submissions ADD COLUMN signature_json TEXT;
ALTER TABLE submissions ADD COLUMN offline_pdf_name TEXT;
ALTER TABLE submissions ADD COLUMN offline_pdf BLOB;
`,
	},
	{
		version: 3,
		name:    "external reference",
		statements: `
ALTER TABLE forms ADD COLUMN external_reference_required INTEGER NOT NULL DEFAULT 0;
ALTER TABLE forms ADD COLUMN external_reference_label TEXT;
ALTER TABLE submissions ADD COLUMN external_reference TEXT;
`,
	},
	{
		version: 4,
		name:    "question sections",
		statements: `
CREATE TABLE IF NOT EXISTS sections (
	id TEXT PRIMARY KEY,
	form_id TEXT NOT NULL REFERENCES forms (id) ON DELETE CASCADE,
	position INTEGER NOT NULL,
	identifier TEXT NOT NULL,
	title TEXT,
	description TEXT
);
CREATE INDEX IF NOT EXISTS idx_sections_form ON sections (form_id);

ALTER TABLE questions ADD COLUMN section_id TEXT REFERENCES sections (id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_questions_section ON questions (section_id);
`,
	},
}

func (s *sqliteStore) migrate(ctx context.Context) error {
	if _, err := s.db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
		version INTEGER PRIMARY KEY,
		name TEXT NOT NULL,
		applied_at TEXT NOT NULL DEFAULT (datetime('now'))
	)`); err != nil {
		return err
	}

	for _, m := range migrations {
		var exists int
		err := s.db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM schema_migrations WHERE version = ?`, m.version).Scan(&exists)
		if err != nil {
			return err
		}
		if exists > 0 {
			continue
		}

		tx, err := s.db.BeginTx(ctx, nil)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, m.statements); err != nil {
			_ = tx.Rollback()
			return fmt.Errorf("apply migration %d (%s): %w", m.version, m.name, err)
		}
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO schema_migrations (version, name) VALUES (?, ?)`,
			m.version, m.name); err != nil {
			_ = tx.Rollback()
			return err
		}
		if err := tx.Commit(); err != nil {
			return err
		}
	}
	return nil
}