-
Type:
Task
-
Resolution: Unresolved
-
Priority:
High
-
Affects Version/s: None
-
Sprint 60
-
Platform
-
Dev
What this is
Create the consent tables. They are new tables starting with cm_ inside the existing db_identity database.
Steps
- Create these 12 tables:
Table What it stores cm_purpose List of data uses: purpose_code, version, name, legal_basis, is_mandatory, is_withdrawable, validity_days cm_notice Notice versions: notice_code, version_no, context (REGISTRATION / HELPLINE ...), is_material, status, approved_by cm_notice_translation Notice text per language: notice_version_id, language_code, body, text_hash, audio_ref, helpline_script cm_consent One row per consent event: consent_id (UUID), beneficiary_reg_id, event_type (NEW / UPDATE / RENEWAL / WITHDRAWAL), consenting_party (SELF / GUARDIAN), notice_version_id, language, channel, mode, captured_by, facility, service_line, device_id, call_id, captured_at, sync_status cm_consent_purpose Decision per purpose: consent_id, beneficiary_reg_id, purpose_code, decision, status (GRANTED / DENIED / WITHDRAWN / EXPIRED / RECONSENT_REQUIRED / SUPERSEDED), version_no, is_current, valid_until cm_consent_evidence Proof: evidence_id, consent_id, type, file_ref, sha256, witness_name (encrypted), otp_status, call_id cm_guardian_link Guardian for a child or person with disability: beneficiary_reg_id, guardian name/phone (encrypted), relationship, verification_method, verified_at, valid_until (18th birthday) cm_access_grant Doctor access and emergency access: beneficiary_reg_id, grant_type (CONSENTED / BREAK_GLASS), grantee, purposes, reason, status, valid_until, reviewed_by cm_rights_request Data requests and complaints: reference_no, type (ACCESS / CORRECTION / ERASURE / GRIEVANCE), status, sla_due_at, assigned_to cm_propagation_outbox Messages sent to partners who receive data: event_type, processor, status, acked_at cm_audit_log Record of every consent action: action, beneficiary_reg_id, purpose, status, initiator, source_ip, prev_hash, entry_hash cm_data_access_log Who viewed or changed which patient record: user_id, beneficiary_reg_id, service, action, purpose, decision, time - Never edit or delete a consent row. Every change adds a new version.
- Allow only one current decision per patient per purpose (unique index on is_current).
- Encrypt name, phone, address, ID numbers and guardian/witness details.
- Do not allow the application database user to update or delete the audit log.
- Store evidence files outside the database. Save only the link and the hash in the table.
Done when
- All 12 tables are created in db_identity.
- A second current decision for the same patient and purpose is rejected.
- The application cannot update or delete audit log rows.
Waiting on: Retention period for each data type (Legal decision, section 13).
Source: DPDPA Compliance Proposal v14, section 5