# Phase 2A.5 — Master Data Integration & Security Audit

Audit date: 2026-09-26. Baseline: `1481733` (`feat: add technical service taxonomy`).

## Executive Summary

Phase 2A passes the freeze gate after fixing one demonstrated MEDIUM admin lifecycle defect. A stale metadata form could silently restore a master record's previous active flag after a lifecycle operation had deactivated it. Two failing HTTP tests reproduced this before the fix. Catalog and taxonomy admin edits now persist only editable model fields through the existing validated partial-save path.

Final PostgreSQL regression: **381 total, 381 passed, 0 failed, 0 skipped**. The previous **367 tests remain unchanged and passed**; this audit adds 14 tests. No unresolved CRITICAL, HIGH, or MEDIUM finding remains. No models, migrations, Phase 1 authorization code, production settings, or business features changed. Phase 2B has not begun.

## Scope

Reviewed catalog models, lifecycle services, identification services, taxonomy models, applicability services, SQL lookup helpers, admin registrations/forms/actions, relevant existing tests, migrations, Git history, settings, and tracked-file hygiene.

Covered Brand, ProductCategory, ProductModel, ProductVariant, DeviceIdentificationPolicy, ServiceCategory, ComplaintSymptom, FaultDiagnosis, RootCause, RepairAction, and all four explicit category-mapping models. Existing Phase 1 tests were rerun without alteration. This is an audit of supported application paths and the stated database boundary, not a claim that arbitrary privileged SQL is safe.

## Catalog Integrity

- All nine coded masters normalize supported whitespace and case before validation. Added attacks cover lowercase, surrounding spaces, tabs, CR/LF and nonbreaking spaces, equivalent duplicates, and direct database writes containing trailing newlines, embedded controls, zero-width characters, non-ASCII letters, and blank codes. Application normalization and PostgreSQL canonical constraints passed these attacks.
- PostgreSQL enforces global code uniqueness for Brand, ProductCategory and all five service masters; ProductModel uniqueness is `(brand, code)` and ProductVariant uniqueness is `(product_model, code)`. Existing scope/duplicate and lower-level constraint tests passed unchanged.
- Supported saves reject active models under inactive brands/categories and active variants under inactive models. These are cross-row application invariants supported by locks, not PostgreSQL CHECK constraints.
- ProductModel brand, ProductVariant model and identification-policy model ownership remain stable through supported saves/admin. ProductModel category correction remains permitted subject to active-parent validation. An added integration test confirms correction preserves the policy, does not rewrite taxonomy mappings, and removes the model from its former category's subsequent cascade.

## Identification Policy

The one-to-one model relation and database uniqueness prevent two policies for one ProductModel. Requirement choices and the IMEI dependency have database constraints as well as application validation. Existing tests exercise the requirement combinations and lower-level invalid writes: IMEI2 OPTIONAL/REQUIRED with IMEI1 NOT_APPLICABLE is rejected; explicit all-NOT_APPLICABLE is valid.

`get_identification_policy()` returns a policy object or `None`. A missing policy remains distinguishable from explicit all-NOT_APPLICABLE configuration. It reads current persisted state rather than relying on an instance's reverse-relation cache. Inactive models retain their policies: this configuration lookup is not a selectability or authorization decision. Ownership, first-creation and competing-update tests passed unchanged.

## Service Taxonomy Integrity

ServiceCategory and the four applicability vocabularies remain independent global master data. Complaint does not imply Diagnosis; Diagnosis neither embeds nor requires RootCause or RepairAction. There are no new mandatory links among these vocabularies and no organization ownership added.

Canonical codes, uniqueness, active-state selection, explicit lifecycle operations and protective deletion remain intact. Deactivation preserves records and mappings; reactivation restores existing applicability without manufacturing mappings.

## Applicability Integrity

- Global applies to active ProductCategories only, with no explicit mappings.
- Restricted applies only through its explicit active-category mappings; restricted with no mappings applies nowhere.
- A global flag plus explicit mappings is rejected by supported service/model/admin paths. This cross-row invariant is application/service enforced, not a database CHECK. Query helpers also exclude contradictory global-plus-mapped records created by unsupported writes.
- Mapping pairs are database-unique and foreign-key protected; endpoints are immutable through supported saves. Duplicate service inputs are deduplicated.
- Every public applicability replacement service was subjected to a failure after old mappings had been removed. Atomic rollback restored the complete previous mapping set, including identities and timestamps. Existing invalid-input and competing replacement tests also passed.
- Category deactivation preserves mapping rows and their timestamps while making the category operationally inapplicable. Category reactivation restores effectiveness where the master remains active. Tests combine this with catalog cascades and policy preservation across two independent catalog trees.

## Lifecycle Safety

Brand, category and model cascades use existing atomic services; descendant reactivation remains explicit. Category cascade integration confirms that unrelated trees remain active and policies/mappings survive. An injected failure at the final category update rolls back the preceding model and variant updates. Existing Brand/model cascade and lifecycle tests passed.

All relevant catalog, policy and taxonomy-mapping parent foreign keys use `PROTECT`. Added tests isolate policy protection of its model and mapping protection of configuration parents. There is no new cascading deletion. Deactivation is not deletion: unreferenced masters may still be deleted with the appropriate native permission, individual policy deletion returns that model to unconfigured, and applicability replacement intentionally removes discarded mappings. This foundation does not promise immutable historical configuration or soft deletion.

## Concurrency / Locking

Reviewed the existing write protocols:

- Catalog child writes coordinate with parent locks; model saves take Brand/Category shared locks before their model lock, and variant saves coordinate with the model before the variant.
- Cascades lock their root, then affected model rows in deterministic order, and update descendants within one transaction. They include inactive model rows when coordinating descendant state. They do not reacquire parent locks in the child-update loop.
- Identification writes lock the ProductModel before its policy, serializing first creation as well as updates.
- Applicability replacements lock the taxonomy master before replacing mappings, preventing supported global/restricted or restricted/restricted writers from interleaving partial configurations. Direct supported mapping saves coordinate through that same master.
- Model partial-save validation uses effective persisted values for fields omitted from the write; the admin fix reuses that behavior.

Existing PostgreSQL concurrency tests passed, including policy creation/update, catalog lifecycle/child writes and competing applicability configurations. A new separate-connection HTTP admin race uses the existing blocking observer (`pg_blocking_pids`) and verifies an edit waits for Brand deactivation, updates metadata, and leaves the record inactive.

Passing these schedules does **not** prove formal deadlock freedom for all possible callers or transaction compositions. Supported service operations serialize their own invariants; they do not provide optimistic versioning for every editable field. Concurrent metadata edits retain last-writer semantics, and callers must refresh stale instances. A concurrent validation conflict can require a retry; this audit does not add conflict-resolution UI.

## Admin Safety

Native model permissions, CSRF protection and the established lifecycle actions remain in use. Existing catalog/policy/taxonomy admin tests and new attacks cover readonly activation/ownership, invalid identification combinations, applicability configuration, view-only denial, and missing-CSRF rejection. Forged ProductVariant parent input is ignored and cannot change ownership.

**Fixed:** inherited full-row metadata saves could persist a stale readonly active flag. Both shared admin bases now intersect concrete editable model fields with form fields and call the existing `save(update_fields=...)` validation path for edits. Creation retains the original path. Applicability's custom form fields remain outside this model-field set and are handled by the existing transactional service path. No lifecycle logic was duplicated and no unsafe bulk update was introduced.

Evidence: `StaleAdminAuditTests` produced **2 failures before remediation**, then **2 passes**. The complete 14-test audit module and full 381-test suite passed after remediation. The MEDIUM finding has been explicitly reviewed against the failure, fix and regression evidence and is closed for this gate.

## Authorization Boundary

Catalog, policy, taxonomy and category mappings remain global configuration managed using native Django model permissions. No fake Company fields, scope adapters, business-role seeds or altered `has_perm()` semantics were introduced.

An added test covers every Phase 2 model as an unsupported Phase 1 business-authorization target. Even an active superuser with native Django model permission receives denial/empty filtering from the scope engine for these unsupported target types. This preserves the existing explicit adapter boundary. Applicability is configuration filtering, not organizational authorization. Future transactional Service records will need organization-scoped authorization separately.

## Query Correctness / Efficiency

All four list helpers use lazy SQL filtering with active master/category checks and `Exists` applicability conditions. Results are deduplicated and ordered by code and UUID. Boolean helpers return booleans; list helpers return QuerySets. Existing invalid/unsaved input and stale-instance coverage passed.

Added tests construct queries before deactivation and evaluate afterward: the new database state is respected. Already evaluated QuerySets/lists are snapshots and must be rebuilt for a later decision; there is no promise that materialized objects automatically refresh.

Measured on PostgreSQL with 12 global records plus one mapped result per vocabulary:

| Operation | Queries |
| --- | ---: |
| Construct each applicable-master QuerySet | 0 |
| Evaluate each list, 13 results | 1 |
| Each point applicability check | 1 |
| Identification lookup, policy present | 2 |
| Identification lookup, policy absent | 2 |

No per-result query growth occurs inside the tested taxonomy list operation. Calling the single-model identification helper in a caller-owned loop still costs two queries per model; it is not a bulk lookup API. Accessing additional lazy related objects is outside the measured list operation. No speculative query optimization was added.

## Database / Migrations

PostgreSQL server version reported `180006` (18.6). `makemigrations --check` found no pending model changes; `migrate` found nothing to apply; `showmigrations` showed all migrations applied. `MigrationLoader.check_consistent_history()` passed and conflict detection returned `{}`. No empty project migrations were found.

Each of the eight project migrations matches its introduction/checkpoint Git blob and the working-tree contents (allowing checkout newline normalization):

| Migration | Checkpoint |
| --- | --- |
| accounts.0001_initial | `f0b12d6` |
| organization.0001_initial | `f7e1e61` |
| organization.0002_userorganizationassignment | `b2604c1` |
| access.0001_initial | `8b61d1c` |
| catalog.0001_initial | `d90403e` |
| catalog.0002_deviceidentificationpolicy | `9678f85` |
| service_catalog.0001_initial | `8005fd7` |
| service_catalog.0002_technical_taxonomy | `1481733` |

Available Git migration history records additions, with no subsequent migration modifications. Django's migration table records names/application status, not source-content checksums: it cannot prove which exact bytes were used when a database was originally migrated. Git evidence is bounded by the available repository history.

The full test command created, migrated, exercised and destroyed an isolated PostgreSQL test database, providing an actual fresh-schema check. The normal developer database was not reset. No historical migration was edited and no new migration was needed.

## Secret Hygiene

Reviewed the tracked tree and audit additions using credential-pattern checks, artifact/path checks and local sensitive-value comparisons without printing values. No committed credential or database dump was identified. Exact-value coincidences were inspected as framework schema labels and test HTTP parameter keys, not embedded connection credentials. This is a bounded working-tree review, not a forensic audit of every historical Git object or an external secret-scanning service.

`.env` and the virtual environment remain ignored and untracked. `.env.example` is tracked with placeholders for secret fields. No tracked environment, generated database dump, temporary secret file or generated runtime artifact was found. No secret values are included in this report.

Production deployment check used a temporary generated key and nonsecret test host/origin in the process environment. It returned only the previously documented HSTS subdomain/preload warnings (`security.W005`, `security.W021`). Settings were not changed and checks were not suppressed.

## Findings

| ID / Severity | Description and evidence | Impact | Remediation status |
| --- | --- | --- | --- |
| MD-01 / MEDIUM | Stale catalog/taxonomy metadata forms restored readonly activation state. Two HTTP reproductions failed before the change. | An administrator's ordinary edit could undo completed lifecycle deactivation and make master data selectable again. | **FIXED / REVIEWED.** Two small admin overrides preserve omitted readonly state. Reproductions, PostgreSQL race, focused audit and full regression pass. No unresolved MEDIUM remains. |
| MD-02 / LOW | Production deployment check reports existing HSTS subdomain/preload warnings W005/W021. | Deployment operators must decide policy for all subdomains and preload readiness. | **Documented existing limitation.** Unchanged from Phase 1, not caused by Phase 2A and not a master-data freeze blocker. |
| MD-03 / INFO | Cross-row hierarchy, stable ownership and contradictory applicability cannot all be represented by the existing same-row constraints. Source review and raw-write attack coverage establish the boundary. | Privileged raw/bulk writers can bypass application-only invariants. | **Documented supported-write contract.** Use services/validated saves; database constraints still enforce canonical forms, scoped/global uniqueness, policy enum/IMEI checks and foreign-key integrity. No triggers or redesign added. |
| MD-04 / INFO | Migration status contains no source checksums; materialized ORM data are snapshots; tested concurrency schedules are finite. | Neither migration status nor a passing test suite establishes stronger historical/freshness/deadlock guarantees. | **Documented evidence limits** in the relevant sections; no blocking defect demonstrated. |

No CRITICAL or HIGH finding was identified. No severity category was populated with a speculative defect.

### Raw/bulk write boundary

`QuerySet.update()`, `bulk_create()`, `bulk_update()` and raw SQL bypass model normalization/validation and service locking/lifecycle side effects. They can violate active-parent relationships, supported ownership rules and global-versus-mapped consistency unless callers deliberately implement the protocol. They cannot bypass PostgreSQL's actual unique, canonical, enum, IMEI and foreign-key constraints without separately disabling/changing database protections. PostgreSQL does not automatically normalize lowercase input; constrained lower-level writes must already be canonical.

Production bulk catalog updates are confined to coordinated lifecycle services; applicability removal is confined to the atomic replacement protocol. Arbitrary external writers are outside those guarantees. No unsupported bulk path was added by the fix.

## Verification Record

Commands use `.venv\Scripts\python.exe` from the project root:

| Command | Result |
| --- | --- |
| `python manage.py test tests.test_master_data_audit.StaleAdminAuditTests --noinput` before fix | 2 tests, 2 failures reproducing MD-01 |
| Same command after fix | 2 passed, 0 failed, 0 skipped |
| `python manage.py test tests.test_master_data_audit --noinput` | 14 passed, 0 failed, 0 skipped; 9.341s |
| `python manage.py check` | No issues |
| `python manage.py makemigrations --check` | No changes detected |
| `python manage.py migrate` | No migrations to apply |
| `python manage.py showmigrations` | All applied |
| `python manage.py check --deploy --settings=config.settings.production` with temporary process configuration | Exit 0; existing W005/W021 only |
| `python manage.py test --noinput` | **381 passed, 0 failed, 0 skipped; 127.849s** |

Git status/diff/stat and whitespace review confirm only the two admin modules, the new audit test module and this report changed. All 367 pre-existing tests and all historical migrations remain untouched. Nothing was staged, committed or pushed.

## Freeze Decision

The demonstrated MEDIUM defect is remediated and reviewed. There are no unresolved CRITICAL/HIGH/MEDIUM findings; the database is migration-clean, supported invariants and concurrency schedules pass, and all previous plus new tests pass. The documented LOW/INFO boundaries do not block freezing Phase 2A. This decision authorizes no new transactional domain work.

**PHASE 2A VERIFIED — READY TO FREEZE**