Touli Schema Delta Specification (Proposal Only)

Baseline

Baseline schema reference:

• docs/touli-erd-postgres.sql

This document proposes additive changes for AI verification, deterministic points controls, blockchain anchoring, and compliance auditing.

Delta Summary

New domains

1. Config snapshoting for deterministic economy.
2. Verification attempts, features, and labels.
3. Points ledger hardening and idempotency.
4. Blockchain anchoring metadata and proofs.
5. Compliance audit trails and retention actions.

Existing table adjustments

• Extend deed_submission, ai_evaluation, wallet_transaction, user_consent, and points_wallet.

Proposed Additions

1) Config and Versioning

config_market

Purpose:

• market-level runtime flags and rollout modes (launch, steady, region constraints).

Key columns:

• id, market_code, mode, is_active, effective_from, effective_to, created_at.

Owner:

• Product + Platform.

Retention:

• full history retained.

config_snapshot

Purpose:

• immutable economy config bundle used by points engine.

Key columns:

• id, market_id, version, snapshot_json, hash, created_by, created_at.

Owner:

• Product operations.

Retention:

• permanent audit retention.

2) Verification Detail and Labels

verification_run

Purpose:

• track each verification attempt and stage transitions.

Key columns:

• id, submission_id, attempt_no, status, started_at, finished_at, actor, trace_id.

Owner:

• Verification worker.

Retention:

• long-term audit retention.

verification_feature

Purpose:

• store structured rule/vision/trust feature outputs per run.

Key columns:

• id, verification_run_id, stage, feature_json, score, reason_codes, created_at.

Owner:

• Verification worker.

Retention:

• long-term; support model governance and disputes.

verification_label

Purpose:

• capture training/evaluation labels from auto/LLM/human outcomes.

Key columns:

• id, submission_id, verification_run_id, verdict, reviewer_type, reason_code, created_at.

Owner:

• Trust and safety.

Retention:

• long-term governed retention.

3) Points and Ledger Hardening

points_ledger

Purpose:

• append-only economic event stream (source of truth).

Key columns:

• id, user_id, wallet_id, submission_id, event_type, points_delta, balance_after, config_snapshot_id, idempotency_key, reason_codes, created_at.

Constraints:

• unique idempotency_key
• non-zero points_delta

Owner:

• Points worker.

Retention:

• permanent audit retention.

ledger_correction

Purpose:

• link reversal/correction events to originals.

Key columns:

• id, original_ledger_id, correction_ledger_id, correction_type, created_at.

Owner:

• Support and risk operations.

Retention:

• permanent.

4) Blockchain Anchoring

anchor_leaf

Purpose:

• staged per-ledger proof records before batch root submission.

Key columns:

• id, ledger_id, leaf_hash, subject_rotation_id, batch_id, created_at.

Owner:

• Anchor worker.

Retention:

• long-term.

anchor_batch

Purpose:

• merkle batch metadata and lifecycle state.

Key columns:

• id, status, batch_window_start, batch_window_end, merkle_root, leaf_count, submitted_at, confirmed_at, created_at.

Owner:

• Anchor worker.

Retention:

• permanent.

anchor_tx

Purpose:

• on-chain submission receipts and replay attempts.

Key columns:

• id, batch_id, attempt_no, tx_hash, chain_id, block_number, confirmation_count, status, error_code, created_at.

Owner:

• Anchor worker.

Retention:

• permanent.

5) Compliance and Retention Audit

retention_action_log

Purpose:

• evidence deletion and lifecycle actions for privacy compliance.

Key columns:

• id, subject_type, subject_id, action_type, policy_version, executed_at, result, trace_id.

Owner:

• Compliance worker.

Retention:

• legal audit retention.

consent_audit

Purpose:

• immutable parental/consent capture records.

Key columns:

• id, user_id, consent_type, consent_version, ip_hash, captured_at, captured_by.

Owner:

• Auth service.

Retention:

• account lifetime + statutory period.

Proposed Alterations to Existing Tables

deed_submission add columns

• pillar (enum)
• hours_claimed (numeric)
• beneficiary_flag (enum)
• verification_outcome (enum)
• decision_version (int)
• processing_trace_id (text)

ai_evaluation add columns

• verification_run_id (fk)
• model_provider
• model_name
• model_version
• request_id

wallet_transaction adjustments

• Add nullable points_ledger_id to bridge migration path.
• Deprecate as source-of-truth once points_ledger is adopted.

user_consent / audit path

• keep existing table
• add immutable write-through into consent_audit.

Indexing Proposal

• verification_run(submission_id, attempt_no desc)
• verification_label(submission_id, created_at desc)
• points_ledger(user_id, created_at desc)
• points_ledger(idempotency_key unique)
• anchor_leaf(batch_id, ledger_id)
• anchor_tx(batch_id, attempt_no desc)
• retention_action_log(subject_type, subject_id, executed_at desc)

Ownership and Retention Matrix

Domain	Owner	Retention class
Config snapshots	Product Ops	Permanent audit
Verification runs/features/labels	Verification + Trust	Long-term audit
Points ledger/corrections	Finance-equivalent platform records	Permanent audit
Anchor metadata	Platform integrity	Permanent audit
Consent + retention logs	Compliance + Legal	Statutory/legal

Migration Strategy (Future Implementation)

1. Additive tables first.
2. Dual-write points events to existing and new ledger during transition.
3. Backfill historical mappings and snapshot ids.
4. Cut read paths to new ledger.
5. Freeze legacy event writes.