Non-Billing CI Regression QA

ScopeCopy Anchor Link

This guide covers Identity, Clinical, Audit, and UHI. Billing checks belong to a separate work session. A passing subset does not mean that full backend CI passes.

The changes prevent patient-ID counter reuse, exclude arrived and completed appointments from next-visit dates, preserve the requested Audit export format, and reject blank specialty codes. The RBAC inventory records current persona and row-scope checks. It does not grant new permissions.

Patient-ID allocation fails without advancing the counter if the existing-ID query fails. Do not use a lower counter as a fallback when existing IDs cannot be checked.

Shared Storage PreflightCopy Anchor Link

Migration 20261014150000_repair_active_storage_attachment_record_ids.rb repairs public.active_storage_attachments.record_id. The column must accept complete UUIDs and legacy numeric IDs. The old bigint column truncated UUIDs. The earlier migration version in a schema snapshot does not prove that its column type is correct.

Before staging or production deployment:

• Stop attachment writers and background attachment jobs.
• Take and verify a database backup. Record the candidate revision.
• Run the checks below with the deployment owner's database access.
• Review every distinct record_type. The migration covers the six listed UUID record types. Investigate any additional UUID-keyed model before rollout.
• Record attachment and blob counts. Keep file keys and patient data out of public logs and issue comments.

SELECT data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'active_storage_attachments'
  AND column_name = 'record_id';

SELECT record_type, count(*)
FROM public.active_storage_attachments
GROUP BY record_type ORDER BY record_type;

SELECT record_type, count(*) AS invalid_uuid_links
FROM public.active_storage_attachments
WHERE record_type IN (
  'Clinical::DiagnosticReport', 'Clinical::Document',
  'Clinical::PatientPhotoRecord', 'Clinical::TranscriptionSession',
  'Identity::Patient', 'Patient::Document'
)
AND record_id::text !~*
  '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
GROUP BY record_type ORDER BY record_type;

SELECT count(*) AS attachment_count FROM public.active_storage_attachments;
SELECT count(*) AS blob_count FROM public.active_storage_blobs;

The invalid-link query must return no rows. If it returns rows, stop. The migration also stops without changing links. A truncated integer does not contain enough information to restore its UUID. Do not guess an owner, attach one file to multiple patients, delete blobs, or bypass the migration guard.

Have the data owner approve a verified re-link from a trusted backup or a separate controlled quarantine procedure. Preserve the original evidence. Request a new upload when the correct link cannot be proved. Repeat preflight before retrying the migration. This implementation does not automate recovery of previously damaged links.

Migration VerificationCopy Anchor Link

• Apply the normal deployment migrations, not a manual production type cast.
• Confirm migration 20261014150000 is recorded as applied.
• Confirm record_id is character varying or text and remains non-null.
• Confirm the attachment uniqueness index still exists.
• Repeat the invalid-link query. It must return no rows.
• Confirm attachment and blob counts are unchanged while writers are stopped.
• Confirm legacy numeric links keep the same decimal ID as text.
• Resume writers only after the storage checks below pass.

The migration holds an exclusive table lock. Lock and statement timeouts stop it from waiting without a bound. Schedule it under the deployment team's normal maintenance procedure. A lock timeout requires a retry, not a weaker guard.

Manual QACopy Anchor Link

Use synthetic patients and files. Do not expose real patient data in evidence.

• Configure a patient-ID prefix and start value. Register two patients. Their IDs must differ and increase. Repeat from another staff session.
• Change the prefix through Facility Settings. Confirm the configured start value and existing IDs under that prefix are respected.
• Give a patient future booked, arrived, and completed appointments. The next visit must use a future proposed, pending, or booked appointment only.
• Repeat next-visit checks across two facilities. The selected facility must not show another facility's appointment.
• Export Audit logs as CSV and JSON. File contents, media type, and extension must agree. JSON contains data and meta, with export counts in meta.
• Request an unsupported Audit format. Confirm HTTP 422 and no download.
• Confirm Audit tenant isolation and commission-resource exclusions remain.
• Upload distinct photos for two patients. Reload and confirm each patient sees only the correct file. Delete one photo; the other must remain attached.
• Check a diagnostic-report PDF, a patient document, an Identity patient photo, and transcription audio where those features are enabled. Verify ownership and tenant access with a second authorized user.
• Verify a signed spouse invite for a missing appointment returns 404. Missing, invalid, revoked, and wrong-role tokens must still be rejected.
• Verify linked facility vitals keep their reference when their display name changes to a supported standard name. Non-matching names disassociate.
• Check UHI with known and unknown specialty codes. Blank input must not produce an empty code. Existing mappings must not change.

Automated VerificationCopy Anchor Link

These are local results for this fix batch, not a full backend CI result:

• Complete Identity, Clinical, Audit, and UHI suites, plus the test-mode configuration guard: 7,301 tests, 27,362 assertions, no failures or errors, and three skips. CI eager loading and the normal RLS test grants were enabled. The seed was 20261014.
• Final patient-ID and storage safety checks: 58 tests, no failures, errors, or skips. The seed was 22961.
• Fresh-schema storage checks: 41 tests, no failures, errors, or skips.
• Related Connect authorization and focused RBAC checks: 531 tests, no failures, errors, or skips, before the final patient-ID failure-path guard.
• Patient document API checks: three tests, no failures, errors, or skips.
• Changed Ruby files pass lint. Schema guards and both RLS posture audits pass. The schema diff contains only the attachment column and migration version.
• CodeRabbit review finding resolved. A follow-up review of the correction, new storage migration, safety tests, and guides reported no findings.

The first review identified an unsafe patient-ID error fallback. The correction removes that fallback instead of allocating an ID without checking existing IDs. Its regression test failed before the correction and passed afterwards.

Full backend CI must run after the separate Billing fixes are included. Staging preflight and the manual QA checkboxes above remain open. No staging or production database was changed during these checks.

RollbackCopy Anchor Link

The storage migration is irreversible. Do not cast the column back to bigint: that loses UUIDs again. An application rollback must retain the string column. If a database restore is required, stop writers and restore the verified backup under the data owner's recovery procedure. Do not delete attachment rows or blobs as an unreviewed rollback shortcut.

Local VerificationCopy Anchor Link

Use a dedicated test database. Do not reset a development database or a test database that another session uses. Load the schema after all schema edits, then provision the normal RLS test role. Rails can reload a stale test schema at test startup. That reload removes database grants. Apply the grants again after a reload and confirm that the RLS checks do not skip.

cd backend
CI=true DATABASE_URL=postgresql://localhost/kulcare_test_nonbilling_example \
  PARALLEL_WORKERS=1 bundle exec rails test \
  domains/identity/test domains/clinical/test domains/audit/test domains/uhi/test \
  domains/shared/test/config/test_environment_test.rb --seed 22961

Use SCHEMA=/tmp/nonbilling-test-structure.sql when a local migration command would otherwise rewrite the tracked schema dump. Commit only the intended schema change and migration version. Keep the CI failure ceiling at zero.