ddl.sql

-- spec-lint relational schema
--
-- Design rationale:
--   schemas            one row per named spec format (e.g. "pin-mapping-v1")
--   schema_versions    every historical revision of that schema's rules,
--                       so documents can always be validated against the
--                       exact rule set that was active when they were authored
--   lint_runs           an audit trail of every validation run: who ran it,
--                       against which schema version, and what the outcome was
--   lint_run_errors     the individual field-level errors/warnings produced
--                       by a single run, normalised out of lint_runs so they
--                       can be queried and aggregated (e.g. "which fields
--                       fail validation most often across the whole org")
--
-- This intentionally mirrors the kind of audit-friendly, versioned schema
-- design needed for a tool that many engineers depend on to author
-- specifications correctly: nothing is silently overwritten, and every
-- validation outcome is traceable.

CREATE TABLE schemas (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE schema_versions (
    id               BIGSERIAL PRIMARY KEY,
    schema_id        BIGINT NOT NULL REFERENCES schemas(id) ON DELETE CASCADE,
    version          INTEGER NOT NULL,
    constraints_json JSONB NOT NULL,
    description      TEXT NOT NULL DEFAULT '',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (schema_id, version)
);

CREATE INDEX idx_schema_versions_schema_id ON schema_versions(schema_id);

CREATE TABLE lint_runs (
    id               BIGSERIAL PRIMARY KEY,
    document_path    TEXT NOT NULL,
    schema_id        BIGINT NOT NULL REFERENCES schemas(id),
    schema_version   INTEGER NOT NULL,
    run_by           TEXT NOT NULL,
    passed           BOOLEAN NOT NULL,
    error_count      INTEGER NOT NULL DEFAULT 0,
    warning_count    INTEGER NOT NULL DEFAULT 0,
    run_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_lint_runs_document_path ON lint_runs(document_path);
CREATE INDEX idx_lint_runs_run_at ON lint_runs(run_at);

CREATE TABLE lint_run_errors (
    id           BIGSERIAL PRIMARY KEY,
    lint_run_id  BIGINT NOT NULL REFERENCES lint_runs(id) ON DELETE CASCADE,
    field        TEXT NOT NULL,
    message      TEXT NOT NULL,
    severity     TEXT NOT NULL CHECK (severity IN ('error', 'warning'))
);

CREATE INDEX idx_lint_run_errors_field ON lint_run_errors(field);

-- Example query this schema design enables: "which fields cause the most
-- validation failures across every spec authored in the last 30 days?"
-- (useful for the documentation support team identifying training gaps)
--
-- SELECT e.field, COUNT(*) AS failure_count
-- FROM lint_run_errors e
-- JOIN lint_runs r ON r.id = e.lint_run_id
-- WHERE r.run_at > now() - interval '30 days'
--   AND e.severity = 'error'
-- GROUP BY e.field
-- ORDER BY failure_count DESC;