# Audit tooling

Raw inputs in `../raw/` are dumps from an isolated PostgreSQL 16 instance that
had every migration in `apps/backend/migrations` applied in version order, plus
an AST parse of the Go router.

## Producing the raw inputs

```bash
podman run -d --name km-audit-pg -e POSTGRES_USER=audit \
  -e POSTGRES_PASSWORD=auditpw -e POSTGRES_DB=km \
  -p 127.0.0.1:55433:5432 docker.io/library/postgres:16-alpine

cd apps/backend/migrations
for f in $(ls [0-9]*.sql | sort); do
  podman exec -i km-audit-pg psql -U audit -d km \
    -v ON_ERROR_STOP=1 -q --single-transaction < "$f"
done
```

`--single-transaction` is mandatory: migration 000058 relies on a
`TEMP TABLE ... ON COMMIT DROP` (see finding F-09).

### Introspection queries

| File | Query |
|---|---|
| `raw/columns_full.csv` | `pg_attribute` + `format_type` + `pg_attrdef`, `relkind='r'`, ordered by table, attnum |
| `raw/constraints.csv` | `pg_constraint` joined to `pg_class`, with `pg_get_constraintdef` |
| `raw/indexes.csv` | `pg_indexes` where `schemaname='public'` |
| `raw/tables.txt` | `information_schema.tables` where `table_type='BASE TABLE'` |
| `raw/api_ops.json` | parsed `apps/backend/docs/openapi.yaml` (method, path, tag, operationId, security, body schema, params, responses) |
| `raw/routes_mounted.json` | AST dump of the mounted routes + middleware gates (`cmd/server/zz_audit_dump_test.go`, removed after use) |
| `raw/ui_routes.json` | parsed ERP router, POS router and B2B page tree |

## Generators

```bash
python3 build_inventories.py   # database/api/route inventories
python3 build_coverage.py      # coverage-matrix.csv
```

Both are idempotent and write into `docs/audit/`.

## Permission-reachability queries (findings F-22/F-23)

```sql
-- rows per role after all migrations
SELECT role, count(*) FROM role_permissions GROUP BY 1 ORDER BY 1;

-- holders of one key, expanding globs the way internal/auth does
SELECT DISTINCT role
FROM role_permissions
WHERE permission = 'masterdata.products.create'
   OR (right(permission, 2) = '.*'
       AND 'masterdata.products.create' LIKE left(permission, length(permission) - 1) || '%');

-- keys held by nobody (feed the catalogue list in from packages/permissions/src/permissions.txt)
```

At the audited commit the first query returns 304 rows across 14 roles and the
third returns 76 unheld keys. Before migration 000061 the same database has 860
rows.