Consent-API: create consent database schema in db_identity

XMLWordPrintable

    • Type: Task
    • Resolution: Unresolved
    • Priority: High
    • 3.14
    • Affects Version/s: None

      What this is
      Create the consent tables. They are new tables starting with cm_ inside the existing db_identity database.

      Steps

      1. 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
      2. Never edit or delete a consent row. Every change adds a new version.
      3. Allow only one current decision per patient per purpose (unique index on is_current).
      4. Encrypt name, phone, address, ID numbers and guardian/witness details.
      5. Do not allow the application database user to update or delete the audit log.
      6. 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

              Assignee:
              Deep Shikha
              Reporter:
              Shivani Garg
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: