T&E Database Framework

A fully parameterised, idempotent PostgreSQL database framework for Test & Evaluation (T&E) programme management — covering TEMP documents, VCRM traceability, test execution, defect reporting, and multi-environment deployment, with a built-in SQL test suite.

What This Is

This project provides a production-grade SQL framework to stand up a T&E management database from scratch — on a single PostgreSQL server or across multiple environments. It is designed to support the full T&E lifecycle as practised in Australian acquisition:

Program management — test programs, TEMP versioning, DT&E / AT&E / OT&E phases
Requirement traceability — system requirements linked to test cases via a VCRM (Verification Cross Reference Matrix)
Test execution — events, results, verdicts, and evidence artefacts
Defect reporting — deficiency reports (DRs) linked directly to failed results
Multi-environment isolation — separate databases, schemas, and users for Dev, Test, Staging, and Prod
Automated data testing — 142 assertions across 5 SQL test suites, all written in pure PostgreSQL
Data-driven evals — 23 offline CSV validator scenarios plus PostgreSQL-backed idempotency and full-suite checks
Web UI + REST API — FastAPI backend (api/) and React frontend (frontend/) for CSV upload, preview, and browsing — no direct DB access from the browser

All names (database, schema, users, every table) are controlled by a single \set configuration block at the top of each environment file. Rename anything in one place and the entire script updates automatically.

Who Is This For?

Role	How you use this
T&E Engineers / Analysts	Understand the data model — VCRM, TEMP versioning, DR lifecycle
Database Administrators	Deploy and maintain the schema across isolated environments
DevOps / Platform Engineers	Plug `deploy_all.sh` and `run_tests.sh` into CI/CD pipelines; manage repos with Terraform
Students / Learners	Study parameterised SQL, idempotent DDL patterns, SQL-native testing, and Terraform IaC

Repository Structure

The project is organised into three categories: build/ (everything that ships), tests/ (correctness coverage), evals/ (data-driven black-box scenarios). See ARCHITECTURE.md for the rationale.

PostgreDataMigrationApp/
│
├── api/                              ← FastAPI backend (CSV pipeline REST API)
│   ├── main.py                       ← app entrypoint, CORS, health, lifespan
│   ├── config.py                     ← env-var driven Settings, TE_TABLES list
│   ├── db.py                         ← psycopg2 pool + Conn context manager
│   ├── auth.py                       ← optional X-API-Key dependency
│   ├── routers/
│   │   ├── csv_routes.py             ← /api/csv/preview, /upload, /files, /tables/{n}/rows, /files/{id}
│   │   └── te_routes.py              ← /api/te/tables (fixed 12-table row counts)
│   ├── services/
│   │   ├── csv_parse.py              ← pure-Python CSV parser + type inference
│   │   ├── dynamic_loader.py         ← creates csv_<hash> tables from any CSV
│   │   └── te_loader.py              ← loads a CSV into one of the fixed T&E tables
│   └── requirements.txt              ← fastapi, uvicorn, psycopg2-binary, pydantic
│
├── frontend/                         ← React 19 + TanStack Start UI
│   ├── src/routes/                   ← file-based routes (SSR)
│   ├── src/lib/csv.functions.ts      ← fetch client for the FastAPI backend
│   ├── vite.config.ts                ← dev server pinned to port 5173
│   ├── .env                          ← VITE_API_URL, VITE_API_KEY
│   └── package.json
│
├── scripts/
│   ├── start-api.ps1                 ← Terminal 1 — FastAPI on http://localhost:8000
│   └── start-frontend.ps1            ← Terminal 2 — Vite on http://localhost:5173
│
├── build/                             ← everything that ships
│   ├── te_core_schema.sql             ← PostgreSQL master schema (legacy entry point)
│   ├── te_seed_data.sql               ← Seed data
│   │
│   ├── adapters/                      ← Engine-specific deployment adapters
│   │   ├── adapter_postgresql.sh
│   │   ├── adapter_mariadb.sh
│   │   ├── adapter_sqlite.sh
│   │   ├── adapter_influxdb.sh
│   │   ├── adapter_redis.sh
│   │   └── adapter_teradata.sh
│   │
│   ├── csv/                           ← Python validator + per-engine loaders
│   │   ├── validator.py
│   │   ├── validator.sh
│   │   └── loader_<engine>.sh
│   │
│   ├── schema/                        ← Engine-specific DDL and seed data
│   │   ├── postgresql/
│   │   │   └── te_core_schema.sql
│   │   ├── mariadb/
│   │   │   └── te_core_schema.sql
│   │   ├── sqlite/
│   │   │   └── te_core_schema.sql
│   │   ├── influxdb/
│   │   │   └── te_seed_data.lp
│   │   ├── redis/
│   │   │   └── te_seed_data.sh
│   │   └── teradata/
│   │       ├── te_core_schema.sql
│   │       └── te_seed_data.sql
│   │
│   ├── environments/                  ← PostgreSQL per-environment launchers
│   │   ├── env_dev.sql
│   │   ├── env_test.sql
│   │   ├── env_staging.sql
│   │   └── env_prod.sql
│   │
│   ├── terraform-github-repos/        ← GitHub repos as Infrastructure as Code
│   │
│   ├── setup.sh                       ← Interactive multi-database configuration wizard
│   └── deploy_all.sh                  ← Multi-engine deployment router
│
├── tests/                             ← correctness coverage for build/
│   ├── framework/
│   │   └── test_framework.sql         ← Assertion library + results table
│   ├── suites/
│   │   ├── test_01_organisations_personnel.sql
│   │   ├── test_02_programs_phases.sql
│   │   ├── test_03_requirements_vcrm.sql
│   │   ├── test_04_execution_defects.sql
│   │   └── test_05_schema_and_business_rules.sql
│   ├── run_all_tests.sql              ← Master SQL test orchestrator
│   ├── run_tests.sh                   ← Bash wrapper (reads config.local.env)
│   ├── run_python_tests.ps1           ← Windows test runner (CI)
│   ├── test_csv_validator.py          ← unittest for build/csv/validator.py
│   └── test_evals_runner.py           ← unittest for evals/runner.py
│
├── evals/                             ← data-driven black-box scenarios
│   ├── PLAN.md  USAGE.md  FAILURE_MODES.md  README.md  HANDOFF.md
│   ├── runner.py                      ← Scenario discovery, diff engine, JSON reports
│   ├── datasets/tier_p/               ← 23 offline CSV validator scenarios
│   ├── datasets/tier_i/               ← Idempotency scenarios (needs PG)
│   ├── datasets/tier_s/               ← SQL suite integration scenarios
│   ├── expected/tier_*/               ← Expected outcomes
│   └── reports/                       ← Runtime output (gitignored)
│
├── ARCHITECTURE.md                    ← The three-layer model
├── README.md  LICENSE  .gitignore

Prerequisites

Requirement	Version	Notes
PostgreSQL	13+	Extensions used: `uuid-ossp`, `pg_trgm`, `dblink`
psql client	Matching server	Must support `\set`, `\if`, `\i` metacommands
bash	4.0+	For `deploy_all.sh`, `setup.sh`, and `run_tests.sh`
Superuser access	—	Required to create databases and roles
Terraform	1.5+	For GitHub repo management (`terraform-github-repos/`)
GitHub PAT	—	Required by Terraform — scope: `repo`
MariaDB / MySQL	10.6+ / 8.0+	For `DB_ENGINE=mariadb` — requires `mysql` CLI on PATH
SQLite	3.35+	For `DB_ENGINE=sqlite` — requires `sqlite3` CLI on PATH
InfluxDB	2.x	For `DB_ENGINE=influxdb` — requires `influx` CLI v2 on PATH
Redis	7.x	For `DB_ENGINE=redis` — requires `redis-cli` on PATH
Teradata	Vantage 17+	For `DB_ENGINE=teradata` — requires `bteq` (TTU) on PATH

Windows users: Use WSL2 or Git Bash to run the shell scripts. The .sql files work natively on any platform via psql.

Verify Before You Deploy

Before running any deployment — local or Azure — confirm your environment is ready. This repository has evolved over time, so always verify which files and tools are actually present rather than assuming.

1. Confirm the required tools are installed

az version          # Azure CLI (only needed for Azure deployment)
terraform version   # Terraform 1.5+
psql --version      # PostgreSQL client
python --version    # Python 3.10+

If any command is not recognised, install that tool before continuing. On Windows, add psql to PATH permanently:

setx PATH "$($env:PATH);C:\Program Files\PostgreSQL\17\bin"

2. Locate the deployment scripts actually present in your copy

Get-ChildItem -Recurse -Filter "deploy-all.ps1" | Select-Object FullName
Get-ChildItem -Recurse -Filter "main.tf"        | Select-Object FullName
Get-ChildItem -Recurse -Filter "deploy_all.sh"  | Select-Object FullName

Use the paths these return — do not assume a folder name. Azure automation may live under azure-automation/ or infra/ depending on your version.

3. Confirm PostgreSQL is reachable (before DB-backed steps)

psql -c '\l'

If this fails, local deploys and Tiers I + S of the eval suite will skip — the database must be installed and running first.

4. Confirm no secrets are tracked

git ls-files | Select-String -Pattern "config.local.env$|\.tfvars$|\.pgpass"

This should return nothing. If it lists a file, remove it from tracking before pushing.

Quick Start

1. Clone the repository

git clone https://github.com/amar-python/PostgreDataMigrationApp.git
cd PostgreDataMigrationApp

2. Run the interactive setup wizard

cd build
chmod +x setup.sh
./setup.sh

The wizard prompts you to select a database engine and configure all settings, then writes config.local.env:

  ╔══════════════════════════════════════════════════════════════╗
  ║       PostgreDataMigrationApp — Database Setup Wizard        ║
  ║   Supports: PostgreSQL · MariaDB · SQLite · InfluxDB         ║
  ║             Redis · Teradata                                  ║
  ╚══════════════════════════════════════════════════════════════╝

  1)  postgresql  — PostgreSQL 15  (relational, ACID, recommended)
  2)  mariadb     — MariaDB 10.x   (relational, MySQL-compatible)
  3)  mysql       — MySQL 8.x      (relational, MySQL protocol)
  4)  sqlite      — SQLite 3       (embedded, file-based, no server)
  5)  influxdb    — InfluxDB 2.x   (time-series, metrics & events)
  6)  redis       — Redis 7.x      (in-memory key-value / cache)
  7)  teradata    — Teradata Vantage (enterprise data warehouse)

Or skip the wizard and accept all defaults:

./setup.sh --defaults                 # use all defaults
./setup.sh --engine teradata          # pre-select engine
./setup.sh --engine sqlite --env dev  # pre-select engine + environment

3. Deploy an environment

# Dev only (includes realistic seed data)
psql -U postgres -f build/environments/env_dev.sql

# Or deploy all 4 environments at once
chmod +x build/deploy_all.sh
./build/deploy_all.sh

3. Run the test suite

chmod +x tests/run_tests.sh
./tests/run_tests.sh dev        # test Dev
./tests/run_tests.sh            # test all 4 environments

4. Connect and explore

psql -U te_dev_user -d te_mgmt_dev

-- VCRM coverage for CYB9131
SELECT r.req_identifier, r.title, COUNT(v.tc_id) AS tc_mapped
FROM   te_dev.requirements   r
LEFT   JOIN te_dev.vcrm_entries v ON v.req_id = r.req_id
GROUP  BY r.req_identifier, r.title
ORDER  BY r.req_identifier;

CSV Loader

csv_loader.sh validates CSV files, derives the target table name from the filename unless --table is supplied, writes accepted/skipped row outputs under csv/logs/, and routes valid rows to the selected engine-specific loader.

# Load into the engine from config.local.env
./csv_loader.sh data/customers.csv

# Specify engine/environment
./csv_loader.sh data/orders.csv --engine postgresql --env dev

# Validate only
./csv_loader.sh data/products.csv --engine sqlite --dry-run

# Override the target table name
./csv_loader.sh data/export_2025.csv --engine mariadb --table invoices

CSV inputs must have a header row, use comma delimiters, and be UTF-8 encoded with or without a BOM. The shared Python validator skips empty rows and row/header column-count mismatches, warns on duplicate headers, preserves quoted commas/newlines, and writes rejected rows with an _skip_reason column.

Supported loader backends are PostgreSQL, MariaDB/MySQL, SQLite, InfluxDB, Redis, and Teradata. PostgreSQL uses COPY, MariaDB/MySQL uses LOAD DATA LOCAL INFILE, SQLite uses Python csv + sqlite3, InfluxDB writes line protocol via the influx CLI, Redis writes hashes through redis-cli, and Teradata uses BTEQ/FastLoad tooling.

Load any CSV

The loader is schema-agnostic — drop any CSV file in front of it and a matching table is auto-created in the target environment's database. Every CSV-loaded table is tagged with two marker columns: _csv_row_id BIGSERIAL PRIMARY KEY and _loaded_at TIMESTAMPTZ. All other columns start as TEXT; ALTER TABLE afterwards if you need stricter types.

Three sample CSVs ship under build/csv/samples/ (customers.csv, orders.csv, inventory.csv) — deliberately off-domain from the T&E schema to demonstrate that any shape is accepted.

# Single-command happy-path proof (loads all three samples into dev, lists them)
make csv-demo

# Load any CSV
make csv-load FILE=path/to/anything.csv          # ENV defaults to dev
make csv-load FILE=path/to/anything.csv ENV=test ENGINE=postgresql

# Use loaded data — companion script: build/csv_utilise.sh (PostgreSQL only)
./build/csv_utilise.sh list                       # all CSV-loaded tables in the env
./build/csv_utilise.sh describe customers         # columns + row count
./build/csv_utilise.sh peek orders --limit 5      # first N rows
./build/csv_utilise.sh export inventory dump.csv  # round-trip back to CSV
./build/csv_utilise.sh drop customers --yes       # remove a CSV-loaded table

csv_utilise.sh only sees tables that carry the marker columns, so it cannot accidentally touch the rigid te_core_schema tables.

Web UI + REST API

The api/ and frontend/ folders provide a browser-based CSV migration UI backed by a FastAPI REST layer. The browser never talks to Postgres directly — every read and write goes through the API. This is the merged replacement for the earlier Supabase-based flow.

Two-terminal dev setup

Both scripts are Windows-first PowerShell (Git Bash equivalents .sh are on the roadmap).

▶ Terminal 1 — FastAPI backend on http://localhost:8000:

pip install -r api\requirements.txt      # first run only
.\scripts\start-api.ps1
# Interactive docs: http://localhost:8000/docs
# Health:            http://localhost:8000/api/health

The script defaults to local PG 18 (PGHOST=localhost, PGPORT=5433, PGDATABASE=te_mgmt_dev, PGUSER=postgres) and prompts for PGPASSWORD if unset. Override any of those before invoking the script. If API_KEY is unset, every endpoint is unauthenticated (fine for localhost).

▶ Terminal 2 — React frontend on http://localhost:5173:

.\scripts\start-frontend.ps1              # runs npm install on first launch

The frontend reads VITE_API_URL (defaults to http://localhost:8000) and, when set, VITE_API_KEY (sent as the X-API-Key header). Both live in frontend/.env.

API surface (v1 — CSV pipeline core)

Method	Path	Purpose
`GET`	`/api/health`	DB reachability + Postgres version
`POST`	`/api/csv/preview`	Parse a CSV payload, infer types, suggest a T&E table match
`POST`	`/api/csv/upload`	Load a CSV — `mode: "dynamic"` (new `csv_<hash>` table) or `mode: "te"` (into a fixed T&E table)
`GET`	`/api/csv/files`	List all uploaded CSVs (from the `csv_uploads.csv_files` registry)
`GET`	`/api/csv/tables/{table_name}/rows`	Preview rows of a dynamically loaded table (whitelisted to `csv_uploads.*`)
`DELETE`	`/api/csv/files/{file_id}`	Drop a dynamically loaded table + registry row (gated by `API_ALLOW_DESTRUCTIVE`)
`GET`	`/api/te/tables`	Existence + row counts for the 12 fixed T&E tables

Backend data model

Two coexisting modes share one registry (csv_uploads.csv_files):

Dynamic mode — each uploaded CSV becomes its own table csv_uploads.csv_<sha256[:16]> with typed columns plus _id, _row_hash, _created_at metadata. Types come from a whitelist: int8 | numeric | date | timestamptz | boolean | text.
T&E mode — validates that the CSV columns are a subset of one of the 12 fixed te_dev.* tables and inserts row-by-row with SAVEPOINT/ROLLBACK TO SAVEPOINT so partial failures don't abort the batch.

All dynamic SQL uses psycopg2.sql.Identifier() / sql.SQL() — no f-string interpolation of table or column names (project rule from CLAUDE.md).

Env vars the API reads

Variable	Default	Notes
`PGHOST` / `PGPORT` / `PGUSER` / `PGPASSWORD` / `PGDATABASE`	`localhost` / `5433` / `postgres` / (empty) / `te_mgmt_dev`	Standard libpq
`CSV_UPLOADS_SCHEMA`	`csv_uploads`	Where dynamic tables + the file registry live
`TE_SCHEMA`	`te_dev`	Where the 12 fixed T&E tables live
`CORS_ORIGINS`	`http://localhost:5173,http://localhost:3000`	Comma-separated allowed origins
`MAX_UPLOAD_BYTES`	`52428800` (50 MB)	Reject `POST /preview` and `/upload` above this
`API_ALLOW_DESTRUCTIVE`	`true`	Set `false` in shared/prod to block `DELETE /api/csv/files/{id}`
`API_KEY`	(unset)	If set, every endpoint requires `X-API-Key: <value>`

How Parameterisation Works

Every environment file contains only a \set configuration block followed by \i te_core_schema.sql. All logic lives in the core schema — the environment file is pure configuration.

-- environments/env_dev.sql — the ONLY file you edit for Dev
\set env_label          DEV
\set db_name            te_mgmt_dev       ← rename the database here
\set schema_name        te_dev            ← rename the schema here
\set app_user           te_dev_user
\set app_password       Dev@Local#2025!
\set tbl_test_cases     test_cases        ← rename any table here
\set include_seed_data  true              ← toggle seed data on/off

\i te_core_schema.sql                     ← unchanged core logic

psql variable syntax used throughout the core schema:

Syntax	Expands to	Used for
`:'varname'`	`'quoted string'`	String literals, WHERE clauses, DO blocks
`:"varname"`	`"quoted identifier"`	Table and schema names in DDL/DML

Environment Comparison

Setting	Dev	Test	Staging	Prod
Database	`te_mgmt_dev`	`te_mgmt_test`	`te_mgmt_staging`	`te_mgmt_prod`
Schema	`te_dev`	`te_test`	`te_staging`	`te_prod`
App User	`te_dev_user`	`te_test_user`	`te_stg_user`	`te_prod_user`
Connection Limit	10	15	20	50
Seed Data	✅ Full	✅ Full	❌ Empty	❌ Empty

Each environment is fully isolated. All four can run on the same PostgreSQL instance.

To deploy to a remote host:

PGHOST=my-db-server PGPORT=5432 PGUSER=postgres ./build/deploy_all.sh staging

Schema Reference — 12 Tables

organisations ──< personnel
      │
      └──< test_programs ──< temp_documents
                  │
                  └──< test_phases ──< test_cases ──< vcrm_entries >── requirements
                              │
                              └──< test_events ──< test_results ──< evidence_artifacts
                                                         │
                                                         └──< defect_reports

Table	Purpose
`organisations`	agencies, prime contractors, test units
`personnel`	T&E workforce with clearance levels and roles
`test_programs`	Top-level programmes (e.g. CYB9131, LAND 400 Ph3)
`temp_documents`	Versioned TEMP documents (draft → approved → superseded)
`test_phases`	DT&E, AT&E, OT&E and other phase types within a program
`requirements`	System requirements subject to T&E verification
`test_cases`	Individual test cases with steps and expected results
`vcrm_entries`	VCRM — maps requirements ↔ test cases (many-to-many)
`test_events`	Scheduled/completed test events (lab, field trial, TTX)
`test_results`	Execution outcomes — one row per test case run per event
`defect_reports`	Deficiency Reports (DRs) raised against failed results
`evidence_artifacts`	Logs, screenshots, reports attached to test results

Key enumerated values

-- personnel.te_role
'test_director' | 'test_manager' | 'test_engineer' |
'te_analyst' | 'safety_engineer' | 'config_manager' | 'observer'

-- personnel.clearance  (Australian security clearance levels)
'baseline' | 'NV1' | 'NV2' | 'PV'

-- test_programs.classification  (ISM-aligned)
'UNCLASSIFIED' | 'PROTECTED' | 'SECRET' | 'TOP SECRET'

-- test_phases.phase_type
'DT&E' | 'AT&E' | 'OT&E' | 'IOT&E' | 'LFT&E' | 'FOLLOW_ON'

-- test_results.verdict
'pass' | 'fail' | 'blocked' | 'not_run' | 'inconclusive'

-- defect_reports.severity
'critical' | 'major' | 'minor' | 'observation'

Seed Data (Dev & Test Only)

Realistic Australian T&E data is loaded automatically when include_seed_data is true.

Table	Records	Highlights
`organisations`	5	CASG, DST Group, Leidos, BAE Systems, JSTF
`personnel`	6	Roles from test_director to safety_engineer; NV1–PV clearances
`test_programs`	2	CYB9131 (PROTECTED), LAND 400 Ph3 (SECRET)
`temp_documents`	3	Approved v1.0 + draft amendment for CYB9131; draft for LAND 400
`test_phases`	3	CYB9131 DT&E (completed), OT&E (active), LAND400 AT&E (planned)
`requirements`	8	6 × CYB9131 (security, performance, functional, compliance), 2 × LAND400
`test_cases`	8	Security, performance, acceptance TCs against CYB9131 OT&E
`vcrm_entries`	8	100% VCRM coverage for CYB9131; LAND400 intentionally uncovered
`test_events`	3	EV01 completed, EV02 in-progress, EV03 planned
`test_results`	7	4 pass, 2 fail, 1 inconclusive — realistic mix
`defect_reports`	3	DR-CYB-0001 (audit gap), 0002 (TLS 1.2), 0003 (session timeout)

Test Suite

Run it

# Against a single environment
./tests/run_tests.sh dev

# Against all environments
./tests/run_tests.sh

# Run Python tests
python -m unittest discover -s tests -p "test*.py" -v

# Run data-driven CSV validator evals
python evals/runner.py --tiers p

# Run all eval tiers; PostgreSQL-backed tiers skip cleanly if PG is unavailable
python evals/runner.py --tiers p,i,s

# Manually via psql
psql -U postgres -d te_mgmt_dev \
  --set schema_name=te_dev \
  --set tbl_organisations=organisations \
  --set tbl_personnel=personnel \
  --set tbl_test_programs=test_programs \
  --set tbl_temp_documents=temp_documents \
  --set tbl_test_phases=test_phases \
  --set tbl_requirements=requirements \
  --set tbl_test_cases=test_cases \
  --set tbl_vcrm_entries=vcrm_entries \
  --set tbl_test_events=test_events \
  --set tbl_test_results=test_results \
  --set tbl_defect_reports=defect_reports \
  --set tbl_evidence_artifacts=evidence_artifacts \
  -f tests/run_all_tests.sql

# Windows/PowerShell runner for Python tests
powershell -NoProfile -ExecutionPolicy Bypass -File "tests/run_python_tests.ps1"
# Optional: run a custom test path
powershell -NoProfile -ExecutionPolicy Bypass -File "tests/run_python_tests.ps1" -TestPath "tests/test_csv_validator.py"

Windows / Cursor AI notes

setup.sh, deploy_all.sh, and tests/run_tests.sh are bash scripts.
On Windows, run shell scripts via WSL2 or Git Bash.
The Python unit tests and Tier P evals are Windows-native and do not require WSL.
Tier I and Tier S evals require a reachable PostgreSQL instance and psql on PATH; if unavailable, they skip cleanly.
Required for Python tests and offline evals:
- Python on PATH (python --version)
- PowerShell available (pwsh or powershell)
Recommended Windows command:

powershell -NoProfile -ExecutionPolicy Bypass -File "tests/run_python_tests.ps1"
python evals\runner.py --tiers p

CI validation (Windows)

A GitHub Actions workflow is included for Windows validation of the Python tests:

Workflow file: .github/workflows/python-validator-tests.yml
Runner: windows-latest
Python version: 3.11
Command executed:

powershell -NoProfile -ExecutionPolicy Bypass -File "tests/run_python_tests.ps1"

Data-driven evals

The evals/ package complements the SQL and unit tests with scenario fixtures and expected JSON outputs.

Tier	What it validates	Database required?
P	`csv/validator.py` across 23 CSV edge cases, including malformed rows, BOM, CRLF, Unicode, quoted newlines, long fields, missing env vars, and invalid UTF-8 bytes	No
I	Dev deployment idempotency by deploying twice and comparing seed row counts	Yes
S	Fresh Dev deploy followed by the full SQL suite, expecting all 142 assertions to pass	Yes

Run examples:

python evals\runner.py                  # Tier P only
python evals\runner.py --tiers p,i,s    # all tiers; I/S skip if PostgreSQL is unavailable
python evals\runner.py --only 14_quoted_newline --tiers p

Each eval run writes a JSON report under evals/reports/<run_id>/summary.json; that folder is intentionally gitignored.

Coverage — 142 assertions across 5 suites

Suite	Assertions	What is tested
01 — Organisations & Personnel	17	Row counts, FK integrity, CHECK/UNIQUE/NOT NULL constraints
02 — Programs, TEMP & Phases	19	Date rules, classification markings, status enums
03 — Requirements & VCRM	21	100% VCRM coverage check, per-program gap detection
04 — Execution & Defects	28	Verdict counts, DR linkage to fail results, resolved_at logic
05 — Schema & Business Rules	20	Table/index existence, trigger firing, cross-table rules

Assertion functions

Function	Purpose
`assert_equals(suite, name, expected, actual)`	Exact value match
`assert_not_equals(suite, name, expected, actual)`	Values must differ
`assert_row_count(suite, name, query, n)`	COUNT of query = N
`assert_true(suite, name, sql_expr)`	Expression is TRUE
`assert_false(suite, name, sql_expr)`	Expression is FALSE
`assert_not_null(suite, name, query)`	Query returns a value
`assert_null(suite, name, query)`	Query returns NULL
`assert_raises(suite, name, query)`	Query must throw an exception

Sample output

============================================================
 T&E TEST SUITE   Schema: te_dev
============================================================

REPORT 1: Suite Summary
─────────────────────────────────────────────────────────────
 suite             total  passed  failed  pass_rate  status
 ──────────────── ──────  ──────  ──────  ─────────  ──────────────
 business_rules       8       8       0   100.0%     ✓ ALL PASS
 defect_reports      12      12       0   100.0%     ✓ ALL PASS
 organisations        8       8       0   100.0%     ✓ ALL PASS
 personnel            9       9       0   100.0%     ✓ ALL PASS
 programs            13      13       0   100.0%     ✓ ALL PASS
 requirements        11      11       0   100.0%     ✓ ALL PASS
 schema              20      20       0   100.0%     ✓ ALL PASS
 temp_documents       6       6       0   100.0%     ✓ ALL PASS
 test_cases           9       9       0   100.0%     ✓ ALL PASS
 test_events          8       8       0   100.0%     ✓ ALL PASS
 test_phases          6       6       0   100.0%     ✓ ALL PASS
 test_results         9       9       0   100.0%     ✓ ALL PASS
 vcrm                10      10       0   100.0%     ✓ ALL PASS

REPORT 4: Overall Result
─────────────────────────────────────────────────────────────
 total  passed  failed  pass_rate  overall
 ─────  ──────  ──────  ─────────  ───────────────────────
    85      85       0   100.0%    ✓ ALL TESTS PASSED

Running the Full Test Suite

Every test reports pass or fail. Nothing is skipped: an unavailable prerequisite (no PostgreSQL, no env_<env>.sql, no config.local.env, no deployed database, no bash) is a failure, not a silent skip.

One-time provisioning

Concrete environment launchers and config.local.env are gitignored, so a fresh clone has none of them. Create them all in one step:

bash scripts/provision_full_test_env.sh

This copies env_<env>.sql from the committed *.example.sql templates, writes a local config.local.env, and deploys all four environments. It needs a reachable PostgreSQL and PGUSER / PGHOST / PGPORT set.

Run with full accounting

python3 scripts/test_report.py --strict

Every run ends with a block accounting for all collected tests:

==================================================================
  FINAL RESULT — every test accounted for
==================================================================
  collected     : 54
  executed      : 54
  PASSED        : 54
  FAILED        : 0
  ERROR         : 0
  SKIPPED       : 0
  NOT RUN       : 0  (deselected by the marker filter)

  SKIPPED (0)
    none — no test was skipped
==================================================================
  RESULT: PASS
==================================================================

The SKIPPED section prints even when empty, so its absence is never ambiguous. NOT RUN names each test deselected by a marker filter — those are out of that invocation's scope, not skipped, and must run in another job. --strict exits non-zero if anything was skipped.

python3 scripts/test_report.py                                  # whole suite
python3 scripts/test_report.py --markers "unit or security"     # scoped

Expected results

Layer	Command	Expected
Python tests	`python3 scripts/test_report.py --strict`	54 / 54, 0 skipped
SQL assertions	`bash tests/run_tests.sh dev`	142 / 142, 100%
Evals P, I, S	`python3 evals/runner.py --tiers p,i,s`	25 / 25, 0 skipped
Lint	`bash scripts/lint.sh`	flake8 + bandit clean
Health	`python3 scripts/health_check.py`	all checks pass

Captured output for each of these is in test-artifacts/ — including a deliberate negative-control run proving that missing prerequisites fail rather than skip. See test-artifacts/00_SUMMARY.md.

CI

Workflow	Runner	Scope
`quality-gate.yml` — `free-tier`	ubuntu	`unit`, `regression`, `security`, `snapshot`
`quality-gate.yml` — `integration-postgres`	ubuntu + PG service	full suite, all four environments
`python-validator-tests.yml`	windows	database-free markers

Both workflows end with scripts/test_report.py --strict, so a skipped test fails the build. GitHub Actions service containers are Linux-only, so the Windows job cannot host PostgreSQL; it runs the database-free markers and prints the tests it does not run by name. The database-backed markers run in integration-postgres.

Document	Contents
`FIXES_APPLIED.md`	Every fix made during the audit, with evidence
`GAP_ANALYSIS.md`	Open gaps and the decisions they need
`VCRM_GAPS.md`	Business-requirement traceability, regenerated per run
`TEST_CONDITIONS.md`	Full catalogue of test conditions
`test-artifacts/00_SUMMARY.md`	Captured output from the verification run

Idempotency

The entire framework is safe to re-run against an existing database:

CREATE DATABASE / CREATE ROLE — wrapped in DO $$ IF NOT EXISTS $$ guards
CREATE TABLE — uses IF NOT EXISTS
CREATE INDEX — uses IF NOT EXISTS
CREATE EXTENSION — uses IF NOT EXISTS
Seed data — uses ON CONFLICT DO NOTHING
Triggers — DROP TRIGGER IF EXISTS before CREATE TRIGGER

Production Guidance

Never commit real passwords — use a secrets manager (Azure Key Vault, HashiCorp Vault, AWS Secrets Manager) and inject app_password at deploy time.
Staging and Prod have seed data disabled — load your own anonymised snapshot after deployment.
Connection limits per user are set conservatively by default — tune conn_limit to your workload.
The evidence_artifacts table is schema-only — wire it to your document store (SharePoint, S3, Azure Blob) via the file_path column.

Terraform — GitHub Repository Management

The terraform-github-repos/ folder manages this repository (and any future ones) as Infrastructure as Code. Instead of manually configuring repositories on GitHub, you define them in code and apply changes with a single command.

Prerequisites

Terraform 1.5+
A GitHub Personal Access Token (PAT) with repo scope — generate one here

Setup

cd terraform-github-repos

Set your token as an environment variable (never hard-code it):

# PowerShell (Windows)
$env:TF_VAR_github_token="ghp_yourtoken"

# Mac / Linux / Git Bash
export TF_VAR_github_token="ghp_yourtoken"

Run

terraform init     # download the GitHub provider (run once)
terraform plan     # preview what will change
terraform apply    # apply changes to GitHub

Add a new repository

Step 1 — Add an entry to variables.tf:

"MyNewProject" = {
  description = "Description of my new project"
  visibility  = "public"
  topics      = ["python", "automation", "devops"]
}

Step 2 — Add a resource block in main.tf:

resource "github_repository" "my_new_project" {
  name        = "MyNewProject"
  description = var.repos["MyNewProject"].description
  visibility  = var.repos["MyNewProject"].visibility
  has_issues  = true
  auto_init   = false
  lifecycle { prevent_destroy = true }
}

resource "github_repository_topics" "my_new_project" {
  repository = github_repository.my_new_project.name
  topics     = var.repos["MyNewProject"].topics
}

Step 3 — Apply:

terraform plan    # confirm what will be created
terraform apply   # create the repo on GitHub

Key features

prevent_destroy = true — protects repos from accidental terraform destroy
sensitive = true on the token — prevents it appearing in plan output or logs
terraform.tfvars is in .gitignore — credentials are never committed
All repo config lives in one place: variables.tf

Useful commands

terraform output          # print all repository URLs
terraform show            # show current managed state
terraform fmt             # auto-format .tf files
terraform validate        # check configuration for errors

Contributions are welcome. Please follow these steps:

Fork the repository
Create a feature branch: git checkout -b feature/your-feature-name
Make your changes and add or update tests in tests/suites/
Verify all 142 assertions still pass: ./tests/run_tests.sh dev
Open a Pull Request with a clear description of what changed and why

Guidelines:

Keep the framework idempotent — every change must be safe to re-run
Add at least one test assertion for any new table column or constraint
Follow the existing naming convention for tables (tbl_*), indexes (idx_*), and triggers (trg_*)
Do not commit passwords, real classified data, or environment-specific connection strings

License

MIT — see LICENSE for full text.

Acknowledgements

Built with PostgreSQL 13+. Schema design informed by Australian Defence T&E practice (ASDEFCON, ISM, MIL-STD-882).

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

T&E Database Framework

What This Is

Who Is This For?

Repository Structure

Prerequisites

Verify Before You Deploy

1. Confirm the required tools are installed

2. Locate the deployment scripts actually present in your copy

3. Confirm PostgreSQL is reachable (before DB-backed steps)

4. Confirm no secrets are tracked

Quick Start

1. Clone the repository

2. Run the interactive setup wizard

3. Deploy an environment

3. Run the test suite

4. Connect and explore

CSV Loader

Load any CSV

Web UI + REST API

Two-terminal dev setup

API surface (v1 — CSV pipeline core)

Backend data model

Env vars the API reads

How Parameterisation Works

Environment Comparison

Schema Reference — 12 Tables

Key enumerated values

Seed Data (Dev & Test Only)

Test Suite

Run it

Windows / Cursor AI notes

CI validation (Windows)

Data-driven evals

Coverage — 142 assertions across 5 suites

Assertion functions

Sample output

Running the Full Test Suite

One-time provisioning

Run with full accounting

Expected results

CI

Related documents

Idempotency

Production Guidance

Terraform — GitHub Repository Management

Prerequisites

Setup

Run

Add a new repository

Key features

Useful commands

License

Acknowledgements