cve-dashboard/.kiro/specs/postgres-migration/design.md

Design Document: PostgreSQL Migration

Overview

Migrate the CVE Dashboard backend from SQLite (cve_database.db, 13MB) to PostgreSQL 16 running in a dedicated Docker container (steam-postgres) on port 5433. The primary architectural change is decomposing the monolithic ivanti_findings_cache.findings_json blob (2.6MB TEXT column) into individual rows in an ivanti_findings table. This eliminates JSON parsing on every request, enables indexed per-BU filtering, provides per-BU closed finding counts, and removes SQLite's single-writer lock that blocks reads during sync.

The Postgres instance is fully isolated from the existing Postgres on port 5432 (belonging to another project). The frontend requires zero changes — the API contract remains identical.

Architecture

graph TB
    subgraph "Frontend (port 3000)"
        FE[React SPA]
    end

    subgraph "Backend (port 3001 prod / 3003 dev)"
        SERVER[Express Server]
        POOL[pg Pool - max 10 connections]
        ROUTES[Route Handlers - async/await]
        SYNC[Ivanti Sync Logic]
    end

    subgraph "Docker: steam-postgres (port 5433)"
        PG[(PostgreSQL 16 Alpine)]
        DB[cve_dashboard database]
        VOL[steam-pgdata volume]
    end

    subgraph "Existing Infrastructure (DO NOT TOUCH)"
        OTHER_PG[(Other Postgres - port 5432)]
        SQLITE[(SQLite backup - cve_database.db)]
    end

    FE -->|Same API contract| SERVER
    SERVER --> ROUTES
    ROUTES --> POOL
    SYNC --> POOL
    POOL -->|DATABASE_URL| PG
    PG --> DB
    DB --> VOL

Key Architecture Decisions

Decision	Rationale
Dedicated Docker container on port 5433	Isolation from existing Postgres on 5432; independent lifecycle
pg package with connection pool (max 10)	Concurrent reads during writes; no single-writer lock
Individual finding rows instead of JSON blob	Indexed queries, per-BU filtering in SQL, no JSON.parse
Closed findings stored with bu_ownership	Enables per-BU closed counts (currently only global count)
Batch upsert via INSERT ... ON CONFLICT	Idempotent sync; no data loss on re-runs
Blue-green cutover on same port	<30s downtime; instant rollback by reverting .env

Components and Interfaces

1. Connection Pool Module (backend/db.js)

New module that creates and exports a pg Pool instance. All route files import this instead of receiving a db (sqlite3) parameter.

// backend/db.js
const { Pool } = require('pg');

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    // postgresql://steam:<password>@localhost:5433/cve_dashboard
    max: 10,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 5000,
});

// Log pool errors (connection drops, etc.)
pool.on('error', (err) => {
    console.error('[DB Pool] Unexpected error on idle client:', err.message);
});

// Warn when approaching pool exhaustion
let activeCount = 0;
pool.on('acquire', () => {
    activeCount++;
    if (activeCount >= 8) {
        console.warn(`[DB Pool] WARNING: ${activeCount}/10 connections active — approaching exhaustion`);
    }
});
pool.on('release', () => { activeCount--; });

module.exports = pool;

2. Route Migration Pattern

Every route file changes from callback-based SQLite to async/await Postgres:

Before (SQLite):

function createUsersRouter(db, requireAuth, requireGroup, logAudit) {
    router.get('/', async (req, res) => {
        try {
            const users = await new Promise((resolve, reject) => {
                db.all(
                    'SELECT id, username, email, user_group AS "group" FROM users ORDER BY created_at DESC',
                    (err, rows) => {
                        if (err) reject(err);
                        else resolve(rows);
                    }
                );
            });
            res.json(users);
        } catch (err) {
            res.status(500).json({ error: 'Failed to fetch users' });
        }
    });
}

After (Postgres):

const pool = require('../db');

function createUsersRouter(requireAuth, requireGroup, logAudit) {
    router.get('/', async (req, res) => {
        try {
            const { rows: users } = await pool.query(
                'SELECT id, username, email, user_group AS "group" FROM users ORDER BY created_at DESC'
            );
            res.json(users);
        } catch (err) {
            console.error('Get users error:', err);
            res.status(500).json({ error: 'Failed to fetch users' });
        }
    });
}

3. Query Pattern Translation

SQLite Pattern	Postgres Equivalent
db.get(sql, [params], callback)	const { rows } = await pool.query(sql, [params]); const row = rows[0];
db.all(sql, [params], callback)	const { rows } = await pool.query(sql, [params]);
db.run(sql, [params], callback)	await pool.query(sql, [params]); or with RETURNING
? placeholders	$1, $2, $3... numbered params
INSERT OR IGNORE	INSERT ... ON CONFLICT DO NOTHING
datetime('now')	NOW()
LIKE (case-sensitive)	ILIKE (case-insensitive)

4. Ivanti Sync Component (Rewritten)

The sync logic changes from "serialize all findings to JSON blob" to "upsert individual rows":

// backend/routes/ivantiFindings.js — sync logic (simplified)
async function syncFindings(pool) {
    const allFindings = await fetchAllFromIvanti(); // paginated API calls

    // Batch upsert in chunks of 100
    for (let i = 0; i < allFindings.length; i += 100) {
        const batch = allFindings.slice(i, i + 100);
        const values = [];
        const placeholders = batch.map((f, idx) => {
            const offset = idx * 14;
            values.push(f.id, f.hostId, f.title, f.severity, f.vrrGroup,
                        f.hostName, f.ipAddress, f.dns, f.status, f.slaStatus,
                        f.dueDate, f.lastFoundOn, f.buOwnership, f.cves || []);
            return `($${offset+1},$${offset+2},$${offset+3},$${offset+4},$${offset+5},
                    $${offset+6},$${offset+7},$${offset+8},$${offset+9},$${offset+10},
                    $${offset+11},$${offset+12},$${offset+13},$${offset+14},'open')`;
        });

        await pool.query(`
            INSERT INTO ivanti_findings (id, host_id, title, severity, vrr_group,
                host_name, ip_address, dns, status, sla_status,
                due_date, last_found_on, bu_ownership, cves, state)
            VALUES ${placeholders.join(',')}
            ON CONFLICT (id) DO UPDATE SET
                title = EXCLUDED.title,
                severity = EXCLUDED.severity,
                host_name = EXCLUDED.host_name,
                ip_address = EXCLUDED.ip_address,
                dns = EXCLUDED.dns,
                status = EXCLUDED.status,
                sla_status = EXCLUDED.sla_status,
                due_date = EXCLUDED.due_date,
                last_found_on = EXCLUDED.last_found_on,
                bu_ownership = EXCLUDED.bu_ownership,
                cves = EXCLUDED.cves,
                state = EXCLUDED.state,
                synced_at = NOW()
        `, values);
    }

    // Update sync metadata
    await pool.query(`
        UPDATE ivanti_sync_state SET
            total = (SELECT COUNT(*) FROM ivanti_findings WHERE state = 'open'),
            synced_at = NOW(),
            sync_status = 'success',
            error_message = NULL
        WHERE id = 1
    `);
}

5. Auth Middleware Migration

// backend/middleware/auth.js — After
const pool = require('../db');

function requireAuth() {
    return async (req, res, next) => {
        const sessionId = req.cookies?.session_id;
        if (!sessionId) return res.status(401).json({ error: 'Authentication required' });

        try {
            const { rows } = await pool.query(
                `SELECT s.*, u.id as user_id, u.username, u.email, u.role,
                        u.user_group, u.bu_teams, u.is_active
                 FROM sessions s
                 JOIN users u ON s.user_id = u.id
                 WHERE s.session_id = $1 AND s.expires_at > NOW()`,
                [sessionId]
            );
            const session = rows[0];
            if (!session) return res.status(401).json({ error: 'Session expired or invalid' });
            if (!session.is_active) return res.status(401).json({ error: 'Account is disabled' });

            req.user = {
                id: session.user_id,
                username: session.username,
                email: session.email,
                role: session.role,
                group: session.user_group,
                teams: session.bu_teams ? session.bu_teams.split(',').filter(Boolean) : []
            };
            next();
        } catch (err) {
            console.error('Auth middleware error:', err);
            return res.status(500).json({ error: 'Authentication error' });
        }
    };
}

Data Models

Complete DDL for ivanti_findings Table

CREATE TABLE IF NOT EXISTS ivanti_findings (
    id TEXT PRIMARY KEY,                    -- Ivanti finding ID (e.g. "HF-12345")
    host_id INTEGER,
    title TEXT NOT NULL DEFAULT '',
    severity NUMERIC(4,2) NOT NULL DEFAULT 0,
    vrr_group TEXT NOT NULL DEFAULT '',
    host_name TEXT NOT NULL DEFAULT '',
    ip_address TEXT NOT NULL DEFAULT '',
    dns TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT '',
    sla_status TEXT NOT NULL DEFAULT '',
    due_date DATE,
    last_found_on DATE,
    bu_ownership TEXT NOT NULL DEFAULT '',
    cves TEXT[] DEFAULT '{}',               -- Postgres array type
    workflow_id TEXT,
    workflow_state TEXT,
    workflow_type TEXT,
    state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
    note TEXT NOT NULL DEFAULT '',           -- Merged from ivanti_finding_notes
    override_host_name TEXT,                -- Merged from ivanti_finding_overrides
    override_dns TEXT,                      -- Merged from ivanti_finding_overrides
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_findings_state ON ivanti_findings(state);
CREATE INDEX IF NOT EXISTS idx_findings_bu ON ivanti_findings(bu_ownership);
CREATE INDEX IF NOT EXISTS idx_findings_severity ON ivanti_findings(severity);
CREATE INDEX IF NOT EXISTS idx_findings_state_bu ON ivanti_findings(state, bu_ownership);

Core Tables (Postgres DDL)

-- Users
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'viewer' CHECK (role IN ('admin', 'editor', 'viewer')),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ,
    user_group VARCHAR(20) NOT NULL DEFAULT 'Read_Only',
    bu_teams TEXT NOT NULL DEFAULT ''
);

-- Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

-- Audit Logs
CREATE TABLE IF NOT EXISTS audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    username VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100),
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_audit_created_at ON audit_logs(created_at);

-- CVEs
CREATE TABLE IF NOT EXISTS cves (
    id SERIAL PRIMARY KEY,
    cve_id VARCHAR(20) NOT NULL,
    vendor VARCHAR(100) NOT NULL,
    severity VARCHAR(20) NOT NULL,
    description TEXT,
    published_date DATE,
    status VARCHAR(50) DEFAULT 'Open',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by INTEGER,
    UNIQUE(cve_id, vendor)
);

-- Jira Tickets
CREATE TABLE IF NOT EXISTS jira_tickets (
    id SERIAL PRIMARY KEY,
    cve_id TEXT NOT NULL,
    vendor TEXT NOT NULL,
    ticket_key TEXT NOT NULL,
    url TEXT,
    summary TEXT,
    status TEXT DEFAULT 'Open' CHECK(status IN ('Open', 'In Progress', 'Closed')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti Sync State (replaces ivanti_findings_cache metadata)
CREATE TABLE IF NOT EXISTS ivanti_sync_state (
    id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),
    total INTEGER DEFAULT 0,
    workflows_json TEXT DEFAULT '[]',
    synced_at TIMESTAMPTZ,
    sync_status TEXT DEFAULT 'never',
    error_message TEXT
);

-- Ivanti Counts Cache (for FP workflow counts)
CREATE TABLE IF NOT EXISTS ivanti_counts_cache (
    id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),
    open_count INTEGER DEFAULT 0,
    closed_count INTEGER DEFAULT 0,
    synced_at TIMESTAMPTZ,
    fp_workflow_counts_json TEXT DEFAULT '{}',
    fp_id_counts_json TEXT DEFAULT '{}'
);

-- Ivanti Counts History
CREATE TABLE IF NOT EXISTS ivanti_counts_history (
    id SERIAL PRIMARY KEY,
    open_count INTEGER NOT NULL,
    closed_count INTEGER NOT NULL,
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti Finding Archives
CREATE TABLE IF NOT EXISTS ivanti_finding_archives (
    id SERIAL PRIMARY KEY,
    finding_id TEXT NOT NULL UNIQUE,
    finding_title TEXT NOT NULL DEFAULT '',
    host_name TEXT NOT NULL DEFAULT '',
    ip_address TEXT NOT NULL DEFAULT '',
    current_state TEXT NOT NULL CHECK(current_state IN ('ARCHIVED','RETURNED','CLOSED','CLOSED_GONE')),
    last_severity NUMERIC(4,2) NOT NULL DEFAULT 0,
    first_archived_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_transition_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti Archive Transitions
CREATE TABLE IF NOT EXISTS ivanti_archive_transitions (
    id SERIAL PRIMARY KEY,
    archive_id INTEGER NOT NULL REFERENCES ivanti_finding_archives(id),
    from_state TEXT NOT NULL,
    to_state TEXT NOT NULL,
    severity_at_transition NUMERIC(4,2) NOT NULL DEFAULT 0,
    reason TEXT NOT NULL DEFAULT '',
    transitioned_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Ivanti Sync Anomaly Log
CREATE TABLE IF NOT EXISTS ivanti_sync_anomaly_log (
    id SERIAL PRIMARY KEY,
    sync_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    open_count_delta INTEGER NOT NULL DEFAULT 0,
    closed_count_delta INTEGER NOT NULL DEFAULT 0,
    newly_archived_count INTEGER NOT NULL DEFAULT 0,
    returned_count INTEGER NOT NULL DEFAULT 0,
    classification_json TEXT NOT NULL DEFAULT '{}',
    return_classification_json TEXT NOT NULL DEFAULT '{}',
    is_significant BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti Finding BU History
CREATE TABLE IF NOT EXISTS ivanti_finding_bu_history (
    id SERIAL PRIMARY KEY,
    finding_id TEXT NOT NULL,
    finding_title TEXT NOT NULL DEFAULT '',
    host_name TEXT NOT NULL DEFAULT '',
    previous_bu TEXT NOT NULL,
    new_bu TEXT NOT NULL,
    detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti FP Submissions
CREATE TABLE IF NOT EXISTS ivanti_fp_submissions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    username TEXT NOT NULL,
    ivanti_workflow_batch_id INTEGER,
    ivanti_generated_id TEXT,
    ivanti_workflow_batch_uuid TEXT,
    workflow_name TEXT NOT NULL,
    reason TEXT NOT NULL,
    description TEXT,
    expiration_date TEXT NOT NULL,
    scope_override TEXT NOT NULL DEFAULT 'Authorized',
    finding_ids_json TEXT NOT NULL,
    queue_item_ids_json TEXT NOT NULL,
    attachment_count INTEGER DEFAULT 0,
    attachment_results_json TEXT,
    status TEXT NOT NULL DEFAULT 'success' CHECK(status IN ('success', 'partial', 'failed')),
    lifecycle_status TEXT NOT NULL DEFAULT 'submitted' CHECK(lifecycle_status IN ('submitted', 'approved', 'rejected', 'rework', 'resubmitted')),
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NULL
);

-- Ivanti FP Submission History
CREATE TABLE IF NOT EXISTS ivanti_fp_submission_history (
    id SERIAL PRIMARY KEY,
    submission_id INTEGER NOT NULL REFERENCES ivanti_fp_submissions(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL,
    username TEXT NOT NULL,
    change_type TEXT NOT NULL CHECK(change_type IN ('created', 'fields_updated', 'findings_added', 'attachments_added', 'status_changed')),
    change_details_json TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ivanti Todo Queue
CREATE TABLE IF NOT EXISTS ivanti_todo_queue (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    finding_id TEXT NOT NULL,
    finding_title TEXT,
    cves_json TEXT,
    ip_address TEXT,
    hostname TEXT,
    vendor TEXT NOT NULL,
    workflow_type TEXT NOT NULL CHECK(workflow_type IN ('FP', 'Archer', 'CARD', 'GRANITE')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'complete')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_todo_queue_user ON ivanti_todo_queue(user_id, status);

-- Atlas Action Plans Cache
CREATE TABLE IF NOT EXISTS atlas_action_plans_cache (
    id SERIAL PRIMARY KEY,
    host_id INTEGER NOT NULL UNIQUE,
    has_action_plan BOOLEAN NOT NULL DEFAULT FALSE,
    plan_count INTEGER NOT NULL DEFAULT 0,
    plans_json TEXT NOT NULL DEFAULT '[]',
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Compliance Tables
CREATE TABLE IF NOT EXISTS compliance_uploads (
    id SERIAL PRIMARY KEY,
    filename TEXT NOT NULL,
    report_date TEXT,
    uploaded_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    uploaded_at TIMESTAMPTZ DEFAULT NOW(),
    new_count INTEGER DEFAULT 0,
    resolved_count INTEGER DEFAULT 0,
    recurring_count INTEGER DEFAULT 0,
    summary_json TEXT
);

CREATE TABLE IF NOT EXISTS compliance_items (
    id SERIAL PRIMARY KEY,
    upload_id INTEGER NOT NULL REFERENCES compliance_uploads(id) ON DELETE CASCADE,
    hostname TEXT NOT NULL,
    ip_address TEXT,
    device_type TEXT,
    team TEXT,
    metric_id TEXT NOT NULL,
    metric_desc TEXT,
    category TEXT,
    extra_json TEXT,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'resolved')),
    first_seen_upload_id INTEGER REFERENCES compliance_uploads(id) ON DELETE SET NULL,
    resolved_upload_id INTEGER REFERENCES compliance_uploads(id) ON DELETE SET NULL,
    seen_count INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS compliance_notes (
    id SERIAL PRIMARY KEY,
    hostname TEXT NOT NULL,
    metric_id TEXT NOT NULL,
    note TEXT NOT NULL,
    group_id TEXT,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Knowledge Base
CREATE TABLE IF NOT EXISTS knowledge_base (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    category VARCHAR(100),
    file_path VARCHAR(500),
    file_name VARCHAR(255),
    file_type VARCHAR(50),
    file_size INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by INTEGER REFERENCES users(id)
);

-- Archer Tickets
CREATE TABLE IF NOT EXISTS archer_tickets (
    id SERIAL PRIMARY KEY,
    exc_number TEXT NOT NULL UNIQUE,
    archer_url TEXT,
    status TEXT DEFAULT 'Draft' CHECK(status IN ('Draft', 'Open', 'Under Review', 'Accepted')),
    cve_id TEXT NOT NULL,
    vendor TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Documents
CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    cve_id VARCHAR(20) NOT NULL,
    vendor VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size VARCHAR(20),
    mime_type VARCHAR(100),
    uploaded_at TIMESTAMPTZ DEFAULT NOW(),
    notes TEXT
);

-- Required Documents (seed data)
CREATE TABLE IF NOT EXISTS required_documents (
    id SERIAL PRIMARY KEY,
    vendor VARCHAR(100) NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    is_mandatory BOOLEAN DEFAULT TRUE,
    description TEXT
);

Per-BU Count Queries

-- Open count for specific BUs (used by counts endpoint with teams filter)
SELECT COUNT(*) FROM ivanti_findings
WHERE state = 'open' AND bu_ownership ILIKE ANY(ARRAY['%STEAM%', '%ACCESS-ENG%']);

-- Closed count for specific BUs
SELECT COUNT(*) FROM ivanti_findings
WHERE state = 'closed' AND bu_ownership ILIKE ANY(ARRAY['%STEAM%', '%ACCESS-ENG%']);

-- Aggregated counts grouped by BU and state (single query)
SELECT bu_ownership, state, COUNT(*) as count
FROM ivanti_findings
GROUP BY bu_ownership, state;

-- Global totals (no filter — backward compatible)
SELECT state, COUNT(*) as count
FROM ivanti_findings
GROUP BY state;

Data Migration Script Design (backend/scripts/migrate-to-postgres.js)

flowchart TD
    A[Open SQLite read-only] --> B[Connect to Postgres pool]
    B --> C[Create all tables IF NOT EXISTS]
    C --> D[Copy simple tables]
    D --> E[Parse findings_json blob]
    E --> F[Insert individual finding rows state=open]
    F --> G[Merge ivanti_finding_notes → findings.note]
    G --> H[Merge ivanti_finding_overrides → findings.override_*]
    H --> I[Verify row counts]
    I --> J[Print summary report]

The migration script:

1. Opens SQLite with OPEN_READONLY flag
2. Connects to Postgres via DATABASE_URL
3. Creates schema idempotently (IF NOT EXISTS)
4. Copies each table using batch inserts with ON CONFLICT for idempotency
5. Special handling for findings: parses findings_json, creates one row per finding
6. Merges notes and overrides into the corresponding finding rows
7. Verifies source vs destination row counts
8. Never modifies the SQLite file

Correctness Properties

A property is a characteristic or behavior that should hold true across all valid executions of a system — essentially, a formal statement about what the system should do. Properties serve as the bridge between human-readable specifications and machine-verifiable correctness guarantees.

Property 1: Upsert Idempotence

For any finding synced N times (N ≥ 1) with the same finding ID, the ivanti_findings table SHALL contain exactly one row for that finding ID, with the data from the most recent sync.

Validates: Requirements 3.5, 6.6

Property 2: Finding Storage Preserves State and BU Ownership

For any finding (open or closed) stored in ivanti_findings, querying it back by ID SHALL return the same state and bu_ownership values it was stored with.

Validates: Requirements 3.4, 4.1

Property 3: Count Query Accuracy

For any set of findings in ivanti_findings and any BU filter (including empty/no filter), the count query result SHALL equal the actual number of rows matching that filter and state combination.

Validates: Requirements 4.2, 4.3, 4.5

Property 4: Migration Data Preservation (Findings)

For any finding in the source findings_json blob with associated notes (from ivanti_finding_notes) and overrides (from ivanti_finding_overrides), the migrated ivanti_findings row SHALL contain the finding data with state = 'open', the correct note value, and the correct override_host_name/override_dns values.

Validates: Requirements 7.4, 7.5, 7.6

Property 5: Migration Table Copy Preservation

For any table copied from SQLite to Postgres, the row count in Postgres SHALL equal the row count in SQLite, and each row's data SHALL be equivalent (accounting for type conversions: 0/1 → boolean, DATETIME → TIMESTAMPTZ).

Validates: Requirements 7.7, 7.8

Property 6: Migration Idempotence

For any initial state of the SQLite and Postgres databases, running the migration script N times (N ≥ 1) SHALL produce the same final state in Postgres as running it exactly once (no duplicate rows, no errors).

Validates: Requirements 7.3, 7.9

Property 7: Migration Source Safety

For any execution of the migration script, the SQLite database file SHALL remain byte-for-byte identical before and after (checksum unchanged).

Validates: Requirements 7.10

Property 8: Schema Creation Idempotence

For any number of times the schema creation DDL is executed against the same database, the resulting schema SHALL be identical (no errors, same tables, same indexes, same constraints).

Validates: Requirements 2.5

Property 9: API Response Shape Preservation

For any valid API request to any endpoint, the response JSON structure (top-level keys and value types) after migration SHALL be identical to the pre-migration response structure.

Validates: Requirements 10.1

Error Handling

Error Scenario	Handling Strategy
Pool connection failure	pool.on('error') logs error; automatic reconnection on next query attempt
Pool exhaustion (all 10 busy)	Queries queue internally; warning logged at 8 active connections
Query timeout	connectionTimeoutMillis: 5000 — rejects after 5s with error
Sync failure mid-batch	Transaction rollback; sync_status = 'error' with message; previous data preserved
Migration script failure	Idempotent design — safe to re-run; prints error and exits with code 1
Docker container crash	--restart unless-stopped auto-recovers; pool reconnects on next query
Invalid finding data	NOT NULL DEFAULT '' columns prevent null constraint violations; CHECK constraints reject invalid state values
Rollback needed	Stop Postgres backend → revert .env → restart SQLite backend; SQLite file always preserved

Error Response Format (Unchanged)

All error responses maintain the existing format:

{ "error": "Human-readable error message" }

With appropriate HTTP status codes: 400 (validation), 401 (auth), 403 (permission), 404 (not found), 500 (server error).

Testing Strategy

Unit Tests (Example-Based)

• Verify each route returns correct response shape with known test data
• Verify auth middleware rejects expired sessions
• Verify parameter placeholder conversion (? → $1) in all queries
• Verify schema DDL executes without errors
• Verify migration script handles empty tables gracefully

Property-Based Tests

Property-based testing is appropriate for this feature because the core operations (upsert, count queries, data migration) have clear input/output behavior with universal properties that hold across a wide input space.

Library: fast-check (JavaScript PBT library)

Configuration: Minimum 100 iterations per property test.

Tag format: Feature: postgres-migration, Property {number}: {property_text}

Each correctness property (1-9) maps to a single property-based test:

• Property 1: Generate random findings, upsert each N times, verify exactly one row per ID
• Property 2: Generate findings with random state/bu_ownership, store and retrieve, verify equality
• Property 3: Generate random finding sets, insert, run count queries with random filters, verify accuracy
• Property 4: Generate random findings JSON with notes/overrides, run migration logic, verify merged output
• Property 5: Generate random table rows, copy via migration, verify count and data equivalence
• Property 6: Run migration logic N times on same input, verify final state equals single-run state
• Property 7: Checksum SQLite before/after migration, verify unchanged
• Property 8: Run schema DDL N times, verify no errors and same schema
• Property 9: Compare response shapes between SQLite and Postgres backends for same requests

Integration Tests

• Docker container health check (port 5433 accessible)
• Full sync cycle: trigger sync → verify rows created → verify counts endpoint
• Concurrent read during write: start sync, simultaneously query findings, verify no blocking
• Performance: GET /findings < 500ms, GET /counts < 100ms with 6000+ rows
• Cutover simulation: stop/start backend, verify API responds correctly

Development Isolation

• Test backend runs on port 3003 with DATABASE_URL pointing to Postgres
• Production backend continues on port 3001 with SQLite (no DATABASE_URL set)
• Switching is controlled by presence of DATABASE_URL environment variable
• All work on feature/multi-tenancy branch