Reconcile File Contents Against Database Results

Does the file we delivered agree with the authoritative database?

A report can exist, be fresh, and still contain the wrong business result. This verifier compares evidence from the delivered file with evidence from the system of record and verifies that they agree.

Configuration

Environment: REPORT_FILE, DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_COUNT_QUERY

The CSV example compares parsed data-record count with a database count returned as result. The read-only query requires mysql2 and Node.js 24 or later.

Recipe structure

The top-level executable entry file sits beside a same-named directory that owns the canonical implementation and its dependency manifest. Run npm install once in that directory and retain the generated package-lock.json:

verifiers/
├── reconcile-file-against-database.js
└── reconcile-file-against-database/
    ├── package.json
    └── reconcile-file-against-database.js

Top-level executable entry file

verifiers/reconcile-file-against-database.js

#!/usr/bin/env node
'use strict';

const verifier = require('./reconcile-file-against-database/reconcile-file-against-database');

module.exports = verifier;

if (require.main === module) {
    void runStandalone(verifier);
}

async function runStandalone(standaloneVerifier) {
    let timeoutHandle;

    try {
        const timeout = standaloneVerifier.schedule?.timeout ?? 30;

        if (!Number.isInteger(timeout) || timeout <= 0) {
            throw new Error('schedule.timeout must be a positive integer');
        }

        const timeoutPromise = new Promise((_, reject) => {
            timeoutHandle = setTimeout(() => {
                const error = new Error(
                    `Standalone verifier execution timed out after ${timeout} seconds.`
                );
                error.code = 'BRS_TIMEOUT';
                reject(error);
            }, timeout * 1000);
        });
        const result = await Promise.race([
            Promise.resolve().then(() => standaloneVerifier.verify()),
            timeoutPromise
        ]);

        validateResult(result);
        await writeStream(
            process.stdout,
            `${JSON.stringify(result, null, 2)}\n`
        );
        process.exit(result.steps.some((step) => !step.success) ? 1 : 0);
    } catch (error) {
        const summary = error?.code === 'BRS_TIMEOUT'
            ? error.message
            : error?.code === 'BRS_INVALID_RESULT'
                ? 'Standalone verifier returned a malformed result.'
                : 'Standalone verifier execution failed unexpectedly.';

        await Promise.all([
            writeStream(
                process.stdout,
                `${JSON.stringify({ summary, steps: [] }, null, 2)}\n`
            ),
            writeStream(process.stderr, `${sanitize(error?.message)}\n`)
        ]);
        process.exit(1);
    } finally {
        clearTimeout(timeoutHandle);
    }
}

function validateResult(result) {
    const names = new Set();
    const valid = result &&
        typeof result === 'object' &&
        !Array.isArray(result) &&
        typeof result.summary === 'string' &&
        result.summary.trim() !== '' &&
        Array.isArray(result.steps) &&
        result.steps.length > 0 &&
        result.steps.every((step) => {
            const uniqueName = step &&
                /^[a-z][a-z0-9_]*$/.test(step.name) &&
                !names.has(step.name);

            if (uniqueName) names.add(step.name);

            return uniqueName &&
                typeof step.label === 'string' &&
                step.label.trim() !== '' &&
                typeof step.success === 'boolean' &&
                Number.isFinite(step.durationSeconds) &&
                step.durationSeconds >= 0 &&
                typeof step.message === 'string' &&
                step.message.trim() !== '' &&
                typeof step.checkedAt === 'string' &&
                Number.isFinite(Date.parse(step.checkedAt));
        }) &&
        (result.observations === undefined ||
            Array.isArray(result.observations));

    if (!valid) {
        const error = new Error('verifier result is malformed');
        error.code = 'BRS_INVALID_RESULT';
        throw error;
    }
}

function writeStream(stream, value) {
    return new Promise((resolve, reject) => {
        stream.write(value, (error) => error ? reject(error) : resolve());
    });
}

function sanitize(value) {
    return String(value || 'Unknown error')
        .split(/\r?\n/, 1)[0]
        .replace(/[\u0000-\u001f\u007f]/g, ' ')
        .slice(0, 500);
}

Verifier implementation

verifiers/reconcile-file-against-database/reconcile-file-against-database.js

'use strict';

const fs = require('node:fs/promises');
const mysql = require('mysql2/promise');
const path = require('node:path');

module.exports = {
    id: 'file_database_reconciliation',
    label: 'File and Database Reconciliation',
    schedule: {
        interval: 900,
        timeout: 60
    },

    async verify() {
        loadLocalEnvironment();

        const steps = [];
        let fileCount = 0;
        let databaseCount = 0;
        let connection;
        const fileStart = Date.now();

        try {
            const text = await fs.readFile(required('REPORT_FILE'), 'utf8');
            fileCount = countCsvDataRows(text);
            steps.push(step(
                'read_report_file',
                'Read Report File',
                true,
                fileStart,
                'The report file was read and parsed successfully.'
            ));
        } catch (error) {
            logFailure('read_report_file', error);
            steps.push(step(
                'read_report_file',
                'Read Report File',
                false,
                fileStart,
                'The report file could not be read and parsed.'
            ));
        }

        const dbStart = Date.now();

        try {
            connection = await mysql.createConnection({
                host: required('DB_HOST'),
                user: required('DB_USER'),
                password: required('DB_PASSWORD'),
                database: required('DB_NAME')
            });
            const [rows] = await connection.execute(
                required('DB_COUNT_QUERY')
            );
            databaseCount = Number(rows[0]?.result);

            if (!Number.isFinite(databaseCount)) {
                throw new Error('Query must return a numeric result.');
            }

            steps.push(step(
                'read_database_count',
                'Read Database Count',
                true,
                dbStart,
                'The authoritative database count was read successfully.'
            ));
        } catch (error) {
            logFailure('read_database_count', error);
            steps.push(step(
                'read_database_count',
                'Read Database Count',
                false,
                dbStart,
                'The database count could not be verified.'
            ));
        } finally {
            if (connection) await connection.end();
        }

        const compareStart = Date.now();
        const agrees =
            steps.every((item) => item.success) &&
            fileCount === databaseCount;

        steps.push(step(
            'compare_file_to_database',
            'Compare File to Database',
            agrees,
            compareStart,
            agrees
                ? 'The file record count agrees with the database result.'
                : 'The file and database results do not agree.'
        ));

        return {
            summary: agrees
                ? 'The report file agrees with the authoritative database result.'
                : 'The report file does not agree with the authoritative database result.',
            steps
        };
    }
};

function countCsvDataRows(text) {
    let records = 0;
    let recordStarted = false;
    let inQuotes = false;

    for (let index = 0; index < text.length; index += 1) {
        const character = text[index];

        if (character === '"') {
            recordStarted = true;

            if (inQuotes && text[index + 1] === '"') {
                index += 1;
            } else {
                inQuotes = !inQuotes;
            }
        } else if (
            !inQuotes &&
            (character === '\n' || character === '\r')
        ) {
            if (recordStarted) records += 1;
            recordStarted = false;

            if (character === '\r' && text[index + 1] === '\n') {
                index += 1;
            }
        } else {
            recordStarted = true;
        }
    }

    if (inQuotes) throw new Error('CSV contains an unterminated quoted field.');
    if (recordStarted) records += 1;
    if (records === 0) throw new Error('CSV must contain a header row.');

    return records - 1;
}

function loadLocalEnvironment() {
    try {
        process.loadEnvFile(path.join(__dirname, '.env'));
    } catch (error) {
        if (!error || error.code !== 'ENOENT') throw error;
    }
}

function required(name) {
    const value = String(process.env[name] || '').trim();

    if (!value) throw new Error(`${name} is required`);
    return value;
}

function logFailure(name, error) {
    const detail = sanitize(error instanceof Error ? error.message : error);

    console.error(
        `verifier=file_database_reconciliation step=${name} error="${detail}"`
    );
}

function sanitize(value) {
    return String(value || 'Unknown error')
        .split(/\r?\n/, 1)[0]
        .replace(/[\u0000-\u001f\u007f]/g, ' ')
        .replace(/\\/g, '\\\\')
        .replace(/"/g, '\\"')
        .slice(0, 500);
}

function step(name, label, success, startedAt, message) {
    return {
        name,
        label,
        success,
        durationSeconds: Number(
            ((Date.now() - startedAt) / 1000).toFixed(3)
        ),
        message,
        checkedAt: new Date().toISOString()
    };
}

Dependency manifest

verifiers/reconcile-file-against-database/package.json

{
  "name": "brs-verifier-file-database-reconciliation",
  "version": "1.0.0",
  "private": true,
  "type": "commonjs",
  "scripts": {
    "verify": "node ../reconcile-file-against-database.js"
  },
  "dependencies": {
    "mysql2": "3.23.2"
  },
  "engines": {
    "node": ">=24"
  }
}

Run it directly

cd reconcile-file-against-database
npm install
npm run verify

A passing run writes validated structured JSON to standard output and exits 0. A business failure includes a failed step and exits 1. A timeout, malformed result, or unexpected execution error writes a runner-generated JSON result and exits 1.

Run it continuously with BRS-Core

This verifier does not require BRS-Core to execute. Direct execution is part of the verifier contract and makes development, troubleshooting, CI/CD use, and manual diagnostics straightforward.

BRS-Core can run this verifier alongside your other approved verifiers continuously as part of 24/7 business capability monitoring. Because every recipe presents the same canonical contract, Core can discover and schedule these verifiers together, run them independently, enforce their timeouts, record their results, and expose their signals to the monitoring environment.