eden/packages/database/migrations/1788430575858_baseline-schema.cjs

/**
 * Baseline schema — Identity & Access bounded context (Fase 1).
 *
 * Covers: roles (with hierarchical weight, preserved from the legacy
 * OrçaFácil system — see eden.md §1 and docs/adr/0004-permission-model.md),
 * the resource/action/scope permission matrix (ADR-0004), applications +
 * the identity↔application link (ADR-0012), users, sessions with rotating
 * refresh tokens (ADR-0003), and an append-only, hash-chained audit log
 * (see .claude/skills/eden-security/references/audit.md).
 *
 * Organization (companies/legal entities), Commercial, and every other
 * bounded context land in their own migrations once those phases start —
 * this migration deliberately covers only what nothing else can function
 * without.
 */

exports.shorthands = undefined;

exports.up = (pgm) => {
  pgm.sql(`
    -- gen_random_uuid() is built into PostgreSQL core since v13 — no extension needed.

    CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
    BEGIN
      NEW.updated_at = now();
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    -- =====================================================================
    -- roles
    -- =====================================================================
    CREATE TABLE roles (
      key         TEXT PRIMARY KEY CHECK (key ~ '^[a-z][a-z0-9_]{1,29}$'),
      label       TEXT NOT NULL,
      is_system   BOOLEAN NOT NULL DEFAULT false,
      weight      INTEGER NOT NULL DEFAULT 10 CHECK (weight >= 0 AND weight <= 100),
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID
    );
    COMMENT ON TABLE roles IS
      'Role weight preserved from legacy OrçaFácil: an actor can never administer/assign a role with weight greater than their own. super_admin (weight 100) is the fixed, non-editable ceiling — see eden.md §1.6 and docs/adr/0004-permission-model.md.';

    CREATE TRIGGER trg_roles_updated_at BEFORE UPDATE ON roles
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    -- =====================================================================
    -- applications — the 3 EDEN web apps (docs/adr/0012-three-apps-shared-identity.md)
    -- =====================================================================
    CREATE TABLE applications (
      key   TEXT PRIMARY KEY CHECK (key IN ('core', 'reseller', 'subscriber')),
      label TEXT NOT NULL
    );
    INSERT INTO applications (key, label) VALUES
      ('core', 'EDEN Core'),
      ('reseller', 'EDEN Parceiros'),
      ('subscriber', 'EDEN Assinante');

    -- =====================================================================
    -- users
    -- =====================================================================
    CREATE TABLE users (
      id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by              UUID REFERENCES users(id),
      full_name               TEXT,
      email                   TEXT NOT NULL UNIQUE,
      password_hash           TEXT,
      password_algo_version   INTEGER NOT NULL DEFAULT 1,
      role_key                TEXT NOT NULL REFERENCES roles(key),
      is_active               BOOLEAN NOT NULL DEFAULT true,
      reset_token_hash        TEXT,
      reset_token_expires_at  TIMESTAMPTZ,
      mfa_enabled             BOOLEAN NOT NULL DEFAULT false,
      mfa_secret_encrypted    TEXT
    );
    COMMENT ON COLUMN users.password_hash IS 'Argon2id. password_algo_version tracks which parameter set produced it, per docs/adr/0005-encryption-secrets.md — never re-hash retroactively, only on next successful login.';
    COMMENT ON COLUMN users.mfa_secret_encrypted IS 'AES-256-GCM, reversible (TOTP needs the plaintext secret to verify) — see .claude/skills/eden-security/references/encryption.md. Never store this as a one-way hash.';
    CREATE INDEX idx_users_role ON users(role_key);
    CREATE INDEX idx_users_email_active ON users(email) WHERE is_active = true;

    CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON users
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    ALTER TABLE roles ADD CONSTRAINT fk_roles_created_by FOREIGN KEY (created_by) REFERENCES users(id);

    -- =====================================================================
    -- role_permissions — resource + action + scope matrix (ADR-0004)
    -- =====================================================================
    CREATE TABLE role_permissions (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      role_key    TEXT NOT NULL REFERENCES roles(key) ON DELETE CASCADE,
      resource    TEXT NOT NULL,
      action      TEXT NOT NULL CHECK (action IN ('view', 'create', 'edit', 'delete', 'approve', 'export', 'manage')),
      scope       TEXT NOT NULL CHECK (scope IN ('own', 'team', 'reseller', 'legal_entity', 'all')),
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      CONSTRAINT chk_role_permissions_no_super_admin CHECK (role_key <> 'super_admin'),
      UNIQUE (role_key, resource, action, scope)
    );
    COMMENT ON TABLE role_permissions IS
      'super_admin never gets a row here — it is hardcoded as full-bypass in application code, exactly like the legacy system (eden.md §2.1), and is enforced again here at the database level.';

    -- =====================================================================
    -- user_applications — which of the 3 apps an identity may access (ADR-0012)
    -- =====================================================================
    CREATE TABLE user_applications (
      user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      application_key  TEXT NOT NULL REFERENCES applications(key),
      created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
      PRIMARY KEY (user_id, application_key)
    );

    -- =====================================================================
    -- sessions — rotating refresh tokens (ADR-0003)
    -- =====================================================================
    CREATE TABLE sessions (
      id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id                  UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      application_key          TEXT NOT NULL REFERENCES applications(key),
      refresh_token_hash       TEXT NOT NULL UNIQUE,
      created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
      last_used_at             TIMESTAMPTZ,
      expires_at               TIMESTAMPTZ NOT NULL,
      revoked_at               TIMESTAMPTZ,
      replaced_by_session_id   UUID REFERENCES sessions(id),
      ip_address               INET,
      user_agent               TEXT
    );
    COMMENT ON TABLE sessions IS
      'One row per refresh-token lineage. refresh_token_hash is SHA-256 of the opaque token — the plaintext token is never persisted, only shown once at issuance. replaced_by_session_id records rotation for replay-detection: a reused, already-rotated token means the whole chain is compromised and must be revoked.';
    CREATE INDEX idx_sessions_user_active ON sessions(user_id) WHERE revoked_at IS NULL;

    -- =====================================================================
    -- audit_log — append-only, hash-chained (mirrors the legacy signature
    -- module's proven design, generalized to all critical actions —
    -- eden.md §4 §7 and .claude/skills/eden-security/references/audit.md)
    -- =====================================================================
    CREATE TABLE audit_log (
      id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      seq                   BIGSERIAL NOT NULL UNIQUE,
      created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
      actor_type            TEXT NOT NULL CHECK (actor_type IN ('user', 'system', 'service')),
      actor_id              UUID REFERENCES users(id),
      action                TEXT NOT NULL,
      resource_type         TEXT NOT NULL,
      resource_id           TEXT,
      metadata              JSONB NOT NULL DEFAULT '{}'::jsonb,
      ip_address            INET,
      previous_event_hash   TEXT,
      event_hash            TEXT NOT NULL
    );
    COMMENT ON TABLE audit_log IS
      'seq (bigserial), not created_at, defines chain order — timestamps can collide within a transaction. event_hash = SHA256(previous_event_hash + canonical_json(fields)), computed by the application before insert. Never write a raw secret into metadata.';
    CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id);
    CREATE INDEX idx_audit_log_actor ON audit_log(actor_id);

    CREATE OR REPLACE FUNCTION prevent_audit_log_mutation() RETURNS TRIGGER AS $$
    BEGIN
      IF current_setting('eden.allow_audit_mutation', true) IS DISTINCT FROM 'true' THEN
        RAISE EXCEPTION 'audit_log é append-only — mutação bloqueada (ver docs/security/threat-model.md)';
      END IF;
      IF TG_OP = 'DELETE' THEN
        RETURN OLD;
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER trg_audit_log_no_update BEFORE UPDATE ON audit_log
      FOR EACH ROW EXECUTE FUNCTION prevent_audit_log_mutation();
    CREATE TRIGGER trg_audit_log_no_delete BEFORE DELETE ON audit_log
      FOR EACH ROW EXECUTE FUNCTION prevent_audit_log_mutation();

    -- =====================================================================
    -- Seed: system roles (weights mirror the legacy defaults — eden.md §2)
    -- =====================================================================
    INSERT INTO roles (key, label, is_system, weight) VALUES
      ('user', 'Usuário', true, 10),
      ('backoffice', 'Backoffice', true, 20),
      ('admin', 'Administrador', true, 80),
      ('super_admin', 'Super Administrador', true, 100);
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    DROP TABLE IF EXISTS audit_log;
    DROP TABLE IF EXISTS sessions;
    DROP TABLE IF EXISTS user_applications;
    DROP TABLE IF EXISTS role_permissions;
    DROP TABLE IF EXISTS users CASCADE;
    DROP TABLE IF EXISTS applications;
    DROP TABLE IF EXISTS roles CASCADE;
    DROP FUNCTION IF EXISTS prevent_audit_log_mutation();
    DROP FUNCTION IF EXISTS set_updated_at();
  `);
};