# Phase 3D audit — reporting integrity, security and performance

## Executive result

**Verified — ready to freeze.** One MEDIUM reporting defect was reproduced and
fixed. No unresolved CRITICAL/HIGH/MEDIUM finding or frozen-architecture conflict
was identified. The complete post-code-change regression passed **2,082 tests,
0 failures, 0 skips**, in **4,802.018 seconds** (80 minutes 2.018 seconds).

This is an audit of the existing Phase 3D implementation, not a new functional phase.
Repository baseline: `master`, `58c9768`, with unstaged Phase 3D implementation;
1,986 frozen tests plus 62 reporting tests = 2,048 pre-audit tests.

All reporting source modules, templates, integration changes, the permission-anchor
migration, 62 implementation tests and metric documentation were inspected. Frozen
authorization, inventory position, settlement and lifecycle services were read to
verify reporting against their actual semantics.

## Findings

| Severity | Finding | Resolution |
| --- | --- | --- |
| CRITICAL | None identified | No changes required |
| HIGH | None identified | No changes required |
| MEDIUM | D3-01: diagnosis/complaint co-occurrence narrowed the case cohort by selected ComplaintSymptom but displayed all retained complaints on those cases | Reproduced with two complaints and one completed finding. Narrowed the same SQL complaint join used for grouping to the selected symptom. No domain, taxonomy, migration or baseline-test change. The reproduction failed before the fix and passed afterward. |
| LOW | None identified | No changes required |
| INFO | Multi-query pages use normal committed reads, not a common transactional snapshot | Retained and explicitly documented. Statement-level concurrency tests verify consistent authoritative projections and nonblocking reads; different cards can reflect different commit instants. |
| INFO | Query-count checks are synthetic local measurements | They do not establish production latency, throughput, index selectivity or infrastructure readiness. No production infrastructure was tested. |

The fix is confined to `apps/reporting/service_analytics.py`. Existing reporting tests
are unchanged. Dedicated tests are in the three `tests/test_phase3d_*.py` files.

## Metric reconciliation

Independent expected values are calculated from synthetic records created through
supported frozen services, rather than by calling the reporting aggregation again.

| Area | Evidence and checks |
| --- | --- |
| Intake/current state | Case records, received-date cohorts, exact current status, terminal exclusions, cancellation, center isolation and stable paginated case IDs. Completed histories are not counted as extra current cases. |
| Engineer workload | Current unended assignment only; reassignment replaces queue attribution; unassignment removes the case from the queue. Historical event attribution uses its recorded assignment. |
| Complaints | Retained ComplaintSymptom evidence; actual serviced device model category. Applicability joins and ServiceCategory inference are absent. Selected complaint grouping has a regression test. |
| Diagnosis/cause | Independent counters over completed retained findings, abandoned assessment exclusion, explicit NULL bucket, filtered screen → drill-down → CSV identity reconciliation. |
| Actions | Independent sets of performed-action IDs per grouping in the execution's referenced assessment. Planned actions excluded, equivalent finding groups deduplicated. Overlapping group totals are not unique-action totals. |
| Repair/QC | Failed repair followed by success; abandoned diagnosis/repair/QC, failed QC followed by rework and pass. Attempt/outcome counters, first completed QC denominator and linked completion duration. |
| Delivery/closure | Immutable handover and closure events; closed case removed from current open cases while its payment, reversal and due-release evidence remains reportable. |
| Inventory | Independent ledger delta counters, current positions, posted receipt and transfer evidence, no draft movements, dispatch counted once, internal signed deltas net correctly, adjustments and reconciled count variance. Serialized-unit and nonserialized totals reconcile. |
| Consumption | CONSUMED and RETURNED dispositions counted separately; unused return never reduces historical consumption. Reservation events retain current status. Defective customer-component recovery does not create replacement stock. |
| Commercial | Invoice active-line totals and explicit payer splits, quotation active-line value and event counts, draft exclusion, manual mixed-payer allocation, COMPANY and WARRANTY zero-customer-due workflows. |
| Settlement | Independent gross payment, reversal and valid-payment sums; partial payment, reversal, retained receipts, due-release and closed history. Due-release leaves debt unchanged. Invoice values are not called recognized revenue. |

The two approved semantics remain authoritative:

1. ComplaintSymptom is the complaint dimension. Product Category is a device/product
   dimension. There is no Complaint → ServiceCategory relationship or fabricated
   Complaint Category.
2. Repair-action/diagnosis/root-cause reporting is **assessment-level co-occurrence,
   not causal attribution**. NULL RootCause means **Unknown / Unconfirmed**, without
   a synthetic master. Counts overlap; the unique performed-action total is separate.

Every event query was checked for its documented timestamp. Current stock, workload,
workflow, open-case aging, clearance and outstanding debt intentionally ignore date
bounds. Independent boundary tests cover single-day, leap-month and year ends,
microsecond endpoints, and 23/25-hour New York DST days. Local midnight boundaries
are converted to aware instants and queried as half-open intervals.

## Authorization, filters and security

Reporting reuses frozen SQL authorization. Company, Region, ServiceCenter and
Department-only/center+Department paths retain the existing containment rules.
Native Django permission/Group/staff status does not manufacture business scope.
Inactive organization paths are denied; active superusers retain the frozen explicit
bypass, including historical inactive-center visibility. Database-fresh inactive-user
checks remain in place.

Foreign-company/center/case IDs and incompatible product IDs intersect scope and yield
empty data. Report/export permissions intersect per row, not merely at the entry
point. Drill-downs reauthorize through the same endpoint. Unsupported/repeated keys,
malformed UUIDs/dates/statuses, forged report names and excessive pages fail safely.
There is no arbitrary sorting, SQL expression or relationship path input.

HTTP tests exercise HTML escaping of stored taxonomy labels, CSV formula escaping,
UTF-8 BOM and Bangla data, deterministic headers, authorized filtering, login CSRF
and external-redirect rejection. Error-path checks with DEBUG=False disclose neither
tracebacks nor SQL. The login route is the only reporting state-changing entry point;
report routes reject POST. The application configuration's deployment choices are
not represented as production infrastructure validation.

SQL execution guards verify that report generation, GET requests and complete CSV
iteration execute only SELECT statements and do not take operational row locks.
Existing implementation tests additionally compare before/after operational record
snapshots. No counters, timestamps, assignments, stock or commercial records are
changed by reporting.

## Performance and bounded reads

Pages project values and paginate at 50 rows. Stable timestamp/UUID or group keys
prevent tie-driven page duplication. A 55-row case export is reconciled to both
screen pages. Existing 52-case and inventory/diagnosis/engineer/commercial population
growth checks are retained. New payment population growth from 1 to 20 leaves page
query counts constant.

CSV uses an uncached QuerySet iterator with 1,000-row chunks. The header performs no
row query; iterating 20 payment records performs one query and does not populate the
QuerySet result cache. All report tables and their streaming exports are exercised
under a read-only SQL guard. No operational histories are aggregated in Python by
production reporting code; Python counters/sets are independent test oracles only.

Measured local request query counts (including authentication, session and scope):

| Request | Queries |
| --- | ---: |
| Dashboard case page | 17 |
| Engineer workload | 17 |
| Complaint frequency | 8 |
| Root-cause frequency | 9 |
| Repair/diagnosis/root-cause co-occurrence | 9 |
| Diagnosis drill-down | 9 |
| Inventory positions | 5 |
| Quotation revision drill-down | 6 |
| Invoice drill-down | 6 |
| Payment drill-down | 6 |
| Outstanding invoice drill-down | 6 |
| Payment CSV, including iteration | 4 |

The existing implementation budgets also passed (dashboard/engineer queue 17,
complaints 8, diagnoses 9, positions 5, invoice/outstanding 6). These are SQL query
counts, not production latency guarantees.

## PostgreSQL concurrency

Five dedicated tests coordinate separate connections with the operational writer
held after its changes but before commit. Reporting uses a five-second statement
timeout to detect unintended blocking, and the writer has bounded lock/statement
timeouts. Tests require PostgreSQL rather than skipping on another database.

- Payment posting: settlement sees the prior committed balance, then the new balance.
- Payment reversal: one statement never combines a void payment with valid allocation
  settlement; the original receipt remains counted.
- Serialized stock movement: a single position projection reconciles unit location
  and immutable ledger before and after movement; company identity remains correct.
- Diagnosis start: current workflow remains one consistent case while its transition
  is uncommitted, then changes after commit; no operational read lock is required.
- Assignment revocation: an already constructed lazy reporting QuerySet denies rows
  after revocation commits, proving scope is not copied into a stale Python ID list.

These are deliberate committed-read schedules, not a claim of formal whole-dashboard
snapshot consistency or a production concurrency/load benchmark.

## Migration and repository verification

`reporting.0001_initial` contains only deterministic state for unmanaged `ReportAccess`
with six custom permissions and no default CRUD permissions. It creates no operational
or reporting fact table, so no new fact-table constraints or indexes are needed.
Existing tests verify the absent physical table and exact permission inventory.

Django `check` reports no issues; `makemigrations --check` reports no changes.
`showmigrations` shows every migration applied, including `reporting.0001_initial`
and unchanged `service.0006_handover_closure`. These commands were repeated after
the full regression and passed. The final `python manage.py test --noinput` run
replaced the retained focused-test database, created and migrated a fresh PostgreSQL
test database, passed all tests, then destroyed that database successfully (exit 0).
No historical migration or frozen-domain tracked file changed.

A bounded scan of all 33 changed/new candidate files found no private-key material,
common provider/access-key patterns, credential-bearing database URLs, database
dumps, CSV/customer-data exports or generated artifacts. Manual review confirms
new test records are synthetic. `.env` is ignored and absent from `git ls-files`;
its contents were not read. This is a bounded source/artifact review, not a claim
that all repository history or deployed secrets have been audited.

## Limitations

- Reports are near-current committed reads, not historical as-of ledgers or a common
  multi-statement snapshot. A stream observes its query's committed data; it is not a
  continuously reauthorized view of each already-fetched cell.
- Global labels without immutable snapshots remain current master labels. Invoice and
  receipt display snapshots are retained. Identical historical model snapshot labels
  share a documented label group; center grouping also carries authoritative IDs.
- Current-state date exclusions, received-cohort payment netting of later reversals,
  case-cohort responsibility filters and nonadditive co-occurrence are intentional,
  documented semantics, not accounting/revenue recognition.
- Synthetic query budgets and local PostgreSQL tests do not prove production scale,
  infrastructure security, backup/restore, deployment settings or operational monitoring.

## Verification totals and file inventory

The final full regression passed **2,082 tests**: 1,986 frozen tests + 62 existing
reporting tests + **34 dedicated audit tests**, including all five PostgreSQL concurrency
tests. One complete `python manage.py test --noinput` run after the last code/test
change returned **OK**, **0 failures**, **0 skips**, in **4,802.018 seconds**.
Only documentation was updated afterward. The first focused run passed 92 of
93 tests; its sole error was the new QC fixture attempting resubmission from
QC_PENDING. The fixture was corrected to use the supported begin operation. A
subsequent focused run passed all seven corrected/additional tests in 17.201 seconds.
No failed/interrupted run is counted as the final regression.

Compared with the initial audit working tree, this audit changes only
`apps/reporting/service_analytics.py`, `docs/REPORTING_ANALYTICS.md`, and adds the
three dedicated test files plus this document. Existing baseline and 62 implementation
tests remain unchanged.

Relative to Git HEAD, the only modified tracked files are:

- `config/settings/base.py`
- `config/urls.py`

There are 31 untracked new files (normal `git diff --stat` omits them):

- `apps/reporting/__init__.py`
- `apps/reporting/admin.py`
- `apps/reporting/apps.py`
- `apps/reporting/commercial_analytics.py`
- `apps/reporting/exports.py`
- `apps/reporting/filters.py`
- `apps/reporting/inventory_analytics.py`
- `apps/reporting/management_reports.py`
- `apps/reporting/migrations/0001_initial.py`
- `apps/reporting/migrations/__init__.py`
- `apps/reporting/models.py`
- `apps/reporting/permissions.py`
- `apps/reporting/query.py`
- `apps/reporting/scope.py`
- `apps/reporting/service_analytics.py`
- `apps/reporting/service_dashboard.py`
- `apps/reporting/templates/reporting/error.html`
- `apps/reporting/templates/reporting/login.html`
- `apps/reporting/templates/reporting/report.html`
- `apps/reporting/tests/__init__.py`
- `apps/reporting/tests/test_boundaries.py`
- `apps/reporting/tests/test_cooccurrence.py`
- `apps/reporting/tests/test_reports.py`
- `apps/reporting/tests/test_scenarios.py`
- `apps/reporting/urls.py`
- `apps/reporting/views.py`
- `docs/REPORTING_ANALYTICS.md`
- `docs/PHASE_3D_AUDIT.md`
- `tests/test_phase3d_audit.py`
- `tests/test_phase3d_concurrency.py`
- `tests/test_phase3d_security_audit.py`

No staging, commit, push, baseline-test edits, frozen-domain edits or historical
migration edits were performed. `git diff --cached --name-only` is empty, as is
the tracked diff under `apps/` and `tests/`. `.env` remains ignored and untracked.
`git diff --check` passed; a separate scan of all 31 untracked files also found no
trailing whitespace.

Final Git state: branch `master`, two modified tracked configuration files and the
31 untracked files listed above; nothing staged. Normal `git diff --stat` reports:

```text
 config/settings/base.py | 1 +
 config/urls.py          | 3 ++-
 2 files changed, 3 insertions(+), 1 deletion(-)
```

The full new-file inventory above is separate because this statistic excludes
untracked files. No next functional phase was begun.

**PHASE 3D VERIFIED — READY TO FREEZE**