PD-103 — Agent Developer — Module M13 : capture-migration¶

import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * PD-103: Create capture_events table
 *
 * Table mutable portant l'etat courant de chaque capture probatoire d'ecran.
 * FSM monotone 8 etats (INV-103-28), close-world enum.
 *
 * Invariants couverts:
 *   - INV-103-08: garde sealed via CHECK signature_status
 *   - INV-103-09: dek_wrapped BYTEA, jamais clair en base
 *   - INV-103-28: capture_state_enum 8 valeurs
 *   - INV-103-31: capture_id normalisé lowercase (applicatif)
 *   - INV-103-34: kek_id obligatoire pour rotation keyring
 *   - INV-103-35/36: seal_delayed + compteur cycles conformes
 *   - INV-103-37: payload_canonical_sha256 pour idempotence
 *
 * @see PD-103-specification.md §5.1, §5.7, §5.9
 */
export class PD103CreateCaptureEvents1742400000000 implements MigrationInterface {
  name = 'PD103CreateCaptureEvents1742400000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create enum type for capture state (8 states, close-world)
    await queryRunner.query(`
      CREATE TYPE vault_secure.capture_state_enum AS ENUM (
        'CAPTURED',
        'UPLOADING',
        'UPLOAD_DEFERRED',
        'UPLOADED',
        'PENDING_SEAL',
        'SEALED',
        'ANCHOR_CONFIRMED',
        'CANCELLED'
      );
    `);

    // Create capture_events table
    await queryRunner.query(`
      CREATE TABLE vault_secure.capture_events (
        id                              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        capture_id                      UUID NOT NULL,
        user_id                         UUID NOT NULL,

        -- FSM state
        state                           vault_secure.capture_state_enum NOT NULL DEFAULT 'CAPTURED',
        signature_status                VARCHAR(20) NOT NULL DEFAULT 'PENDING_SIGNATURE',
        hsm_signature_ref               VARCHAR(256),

        -- Crypto envelope (INV-103-09, INV-103-30)
        dek_wrapped                     BYTEA NOT NULL,
        kek_id                          VARCHAR(64) NOT NULL,
        aes_gcm_nonce_b64               VARCHAR(16) NOT NULL,
        aes_gcm_tag_b64                 VARCHAR(24) NOT NULL,

        -- Content identity
        hash_sha3_256                   VARCHAR(64) NOT NULL,
        mime_type                       VARCHAR(10) NOT NULL DEFAULT 'image/png',
        size_bytes                      BIGINT NOT NULL,

        -- Device metadata
        device_id                       UUID NOT NULL,
        app_version                     VARCHAR(32) NOT NULL,
        timestamp_device                TIMESTAMPTZ NOT NULL,

        -- OCR (optional, INV-103-04/05)
        ocr_text                        TEXT,
        ocr_confidence                  NUMERIC(3,2),
        ocr_language                    VARCHAR(35),

        -- Idempotence fingerprint (INV-103-37)
        payload_canonical_sha256        VARCHAR(64) NOT NULL,

        -- Upload S3 reference
        upload_object_key               TEXT,

        -- Sealing references (populated post-SEALED)
        merkle_proof_ref                TEXT,
        tsa_token_ref                   TEXT,
        tx_hash                         VARCHAR(66),

        -- SEAL_DELAYED monitoring (INV-103-35, INV-103-36)
        seal_delayed                    BOOLEAN NOT NULL DEFAULT FALSE,
        seal_delayed_conforming_cycles  INTEGER NOT NULL DEFAULT 0,

        -- Timestamps
        created_at                      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at                      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

        -- Constraints
        CONSTRAINT uq_capture_events_capture_id UNIQUE (capture_id),
        CONSTRAINT chk_signature_status CHECK (signature_status IN ('PENDING_SIGNATURE', 'SIGNED')),
        CONSTRAINT chk_hash_sha3_256 CHECK (hash_sha3_256 ~ '^[a-f0-9]{64}$'),
        CONSTRAINT chk_payload_canonical_sha256 CHECK (payload_canonical_sha256 ~ '^[a-f0-9]{64}$'),
        CONSTRAINT chk_size_bytes CHECK (size_bytes >= 1 AND size_bytes <= 524288000),
        CONSTRAINT chk_mime_type CHECK (mime_type = 'image/png'),
        CONSTRAINT chk_ocr_confidence CHECK (ocr_confidence IS NULL OR (ocr_confidence >= 0 AND ocr_confidence <= 1)),
        CONSTRAINT chk_seal_delayed_cycles CHECK (seal_delayed_conforming_cycles >= 0 AND seal_delayed_conforming_cycles <= 10),
        CONSTRAINT chk_kek_id CHECK (kek_id ~ '^[A-Za-z0-9._-]{1,64}$')
      );
    `);

    // Index: state for FSM queries and reconciliation
    await queryRunner.query(`
      CREATE INDEX idx_capture_events_state
      ON vault_secure.capture_events (state);
    `);

    // Index: user_id for user queries + RLS
    await queryRunner.query(`
      CREATE INDEX idx_capture_events_user_id
      ON vault_secure.capture_events (user_id);
    `);

    // Partial index: non-terminal states for reconciliation (M12)
    await queryRunner.query(`
      CREATE INDEX idx_capture_events_reconciliation
      ON vault_secure.capture_events (created_at)
      WHERE state NOT IN ('CANCELLED', 'ANCHOR_CONFIRMED');
    `);

    // Partial index: SEAL_DELAYED active captures
    await queryRunner.query(`
      CREATE INDEX idx_capture_events_seal_delayed
      ON vault_secure.capture_events (seal_delayed, state)
      WHERE seal_delayed = TRUE;
    `);

    // Composite index: idempotence lookup (M10)
    await queryRunner.query(`
      CREATE INDEX idx_capture_events_idempotence
      ON vault_secure.capture_events (capture_id, payload_canonical_sha256);
    `);

    // Enable RLS
    await queryRunner.query(`
      ALTER TABLE vault_secure.capture_events ENABLE ROW LEVEL SECURITY;
    `);

    // RLS policy: users can only access their own captures
    await queryRunner.query(`
      CREATE POLICY capture_events_user_policy
      ON vault_secure.capture_events
      FOR ALL
      USING (user_id = current_setting('app.current_user_id')::uuid);
    `);

    // Trigger: auto-update updated_at (reuse existing function)
    await queryRunner.query(`
      CREATE TRIGGER trg_capture_events_updated_at
      BEFORE UPDATE ON vault_secure.capture_events
      FOR EACH ROW
      EXECUTE FUNCTION vault_secure.update_timestamp();
    `);

    // Comments
    await queryRunner.query(`
      COMMENT ON TABLE vault_secure.capture_events IS
      'Capture probatoire d''ecran — etat courant FSM 8 etats (PD-103). INV-103-28: close-world state machine.';
    `);

    await queryRunner.query(`
      COMMENT ON COLUMN vault_secure.capture_events.dek_wrapped IS
      'DEK wrappe RSA-OAEP-SHA256 (INV-103-09). JAMAIS stocke en clair.';
    `);

    await queryRunner.query(`
      COMMENT ON COLUMN vault_secure.capture_events.payload_canonical_sha256 IS
      'Fingerprint SHA-256 du payload canonique pour idempotence (INV-103-37). Champs OCR exclus.';
    `);

    await queryRunner.query(`
      COMMENT ON COLUMN vault_secure.capture_events.seal_delayed IS
      'Flag SEAL_DELAYED (INV-103-35). Positionne par reconciliation si PENDING_SEAL > sealSla.';
    `);

    await queryRunner.query(`
      COMMENT ON COLUMN vault_secure.capture_events.seal_delayed_conforming_cycles IS
      'Compteur cycles conformes consecutifs pour clearing SEAL_DELAYED (INV-103-36). Reset si cycle non conforme.';
    `);
  }

  /**
   * down() vide — convention production PD-79.
   * En production, les migrations destructives ne sont jamais executees automatiquement.
   */
  public async down(_queryRunner: QueryRunner): Promise<void> {
    // Convention PD-79: down() is intentionally empty for production safety.
    // Manual rollback procedure:
    //   DROP TABLE vault_secure.capture_events;
    //   DROP TYPE vault_secure.capture_state_enum;
  }
}

import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * PD-103: Create capture_audit_log table (journal probatoire append-only)
 *
 * Event store INSERT-ONLY pour la tracabilite complete des captures probatoires.
 * Les UPDATE et DELETE sont interdits par trigger (pattern PD-272).
 *
 * Invariants couverts:
 *   - INV-103-25: chaque transition d'etat est loguee
 *   - INV-103-10: journal append-only, idempotent
 *   - Plan §11bis: separation capture_events (mutable) / capture_audit_log (immutable)
 *
 * @see PD-103-specification.md §5.6, §5.8
 */
export class PD103CreateCaptureAuditLog1742400000001 implements MigrationInterface {
  name = 'PD103CreateCaptureAuditLog1742400000001';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create capture_audit_log table (append-only)
    await queryRunner.query(`
      CREATE TABLE vault_secure.capture_audit_log (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        capture_id      UUID NOT NULL,
        event_type      VARCHAR(64) NOT NULL,
        payload_json    JSONB NOT NULL DEFAULT '{}',
        created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

        -- Constraints
        CONSTRAINT chk_event_type_not_empty CHECK (event_type <> '')
      );
    `);

    // Index: lookup by capture_id for audit trail
    await queryRunner.query(`
      CREATE INDEX idx_capture_audit_log_capture_id
      ON vault_secure.capture_audit_log (capture_id);
    `);

    // Index: temporal queries for compliance/audit
    await queryRunner.query(`
      CREATE INDEX idx_capture_audit_log_created_at
      ON vault_secure.capture_audit_log (created_at);
    `);

    // Immutability function (pattern PD-272)
    await queryRunner.query(`
      CREATE OR REPLACE FUNCTION vault_secure.capture_audit_log_immutable()
      RETURNS TRIGGER AS $$
      BEGIN
        IF TG_OP = 'UPDATE' THEN
          RAISE EXCEPTION 'capture_audit_log: modification interdite — journal immutable'
            USING ERRCODE = 'restrict_violation';
        ELSIF TG_OP = 'DELETE' THEN
          RAISE EXCEPTION 'capture_audit_log: suppression interdite — journal immutable'
            USING ERRCODE = 'restrict_violation';
        END IF;
        RETURN NULL;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // Immutability trigger
    await queryRunner.query(`
      CREATE TRIGGER trg_capture_audit_log_immutable
      BEFORE UPDATE OR DELETE ON vault_secure.capture_audit_log
      FOR EACH ROW
      EXECUTE FUNCTION vault_secure.capture_audit_log_immutable();
    `);

    // Comments
    await queryRunner.query(`
      COMMENT ON TABLE vault_secure.capture_audit_log IS
      'Journal probatoire append-only pour captures d''ecran (PD-103). UPDATE/DELETE interdits par trigger. INV-103-25.';
    `);

    await queryRunner.query(`
      COMMENT ON COLUMN vault_secure.capture_audit_log.event_type IS
      'Type d''evenement: STATE_TRANSITION, PURGE, UPLOAD_RETRY, SEAL_DELAYED_SET, SEAL_DELAYED_CLEARED, ORPHAN_GC, etc.';
    `);
  }

  /**
   * down() vide — convention production PD-79.
   */
  public async down(_queryRunner: QueryRunner): Promise<void> {
    // Convention PD-79: down() is intentionally empty for production safety.
    // Manual rollback procedure:
    //   DROP TRIGGER trg_capture_audit_log_immutable ON vault_secure.capture_audit_log;
    //   DROP FUNCTION vault_secure.capture_audit_log_immutable();
    //   DROP TABLE vault_secure.capture_audit_log;
  }
}