Schema Map
This map documents future table-style storage for a database migration dry run. Production still uses JSON as the active source of truth.
| Dataset | Source JSON | Future table | Primary key | Important fields | Truth | Migration risks |
|---|---|---|---|---|---|---|
| Blocks | chain.json | blocks | hash | index, timestamp, previous_hash, nonce, difficulty, miner_address, hash | Source of truth | Never rewrite fields used by block hash calculation. |
| Confirmed transactions | chain.json | confirmed_transactions | tx_id plus block_index/transaction_index for legacy rows | sender_address, receiver_address, amount, timestamp, type, category, signature, sender_public_key, metadata | Source of truth | Legacy transactions may not have tx_id; preserve original transaction object. |
| Pending transactions | pending.json | pending_transactions | tx_id or pending sequence | sender_address, receiver_address, amount, timestamp, signature, sender_public_key, metadata | Source of truth for mempool | Keep separate from confirmed transactions until mined. |
| Peers | peers.json | peers | peer_url | url | App state | Stale peers should not affect chain validity. |
| Registry nodes | registry.json | registry_nodes | node_url | display_name, region, operator_wallet_address, last_seen, last_chain_height, reliability_score | App state | Heartbeat timestamps are mutable operational state. |
| Lending loans | lending.json | lending_loans | loan_id | requester_address, amount, repayment_amount, status, votes, due_block, status_history | App state | Nested votes and history need deterministic serialization. |
| Exchange offers | exchange.json | exchange_offers | offer_id | creator_address, acceptor_address, offer_type, amount, price, status, status_history | App state | Lifecycle transitions must keep public trade history intact. |
| Governance proposals | governance.json | governance_proposals | proposal_id | proposer_address, category, parameter, votes, status, voting_deadline, status_history | App state | Vote weights must match chain balance rules used at decision time. |
| Governance rule changes | governance.json | governance_rule_changes | rule_change_id | proposal_id, category, old_value, new_value, applied_block_height, status | App state | Rule history affects later behavior and must remain ordered. |
| Treasury proposals | treasury.json | treasury_proposals | proposal_id | proposer_address, requested_amount, recipient_address, votes, status, payout_tx_id | App state | Payout links must match confirmed chain transactions. |
| Treasury ledger | chain.json and indexes.json | treasury_ledger | ledger_id or tx_id/block tuple | type, amount, from_address, to_address, tx_id, block_index, proposal_id | Derived from chain and treasury state | Do not use indexes as authority; rebuild ledger from confirmed chain. |
| Price signals | price.json | price_signals | signal_id | address, amount, currency, timestamp, expires_at | App state | Expired signals may be pruned by current loaders. |
| Forum posts | forum.json | forum_posts | post_id | author_address, title, body, timestamp, vote_count, moderation_status | App state | Moderation state is mutable while post IDs remain stable. |
| Forum replies | forum.json | forum_replies | reply_id | post_id, author_address, body, timestamp, moderation_status | App state | Nested replies need parent post linkage. |
| Forum reports/moderation | forum.json and backend/data/reports.json | forum_reports_moderation | report_id or moderation_event_id | target_type, target_id, reason, status, moderator_note, timestamp | Operational moderation state | Reports must not expose secrets submitted by users. |
| Achievements | achievements.json | achievements | wallet_address plus achievement_id | wallet_address, achievement_id, earned_at, metadata | App state | Nested earned records need stable composite keys. |
| Profiles | profiles.json | profiles | wallet_address | display_name, bio, avatar, location, verified_wallet, reputation_score | App state | Profile content is public; do not add private key storage. |
| Faucet claims | faucet.json | faucet_claims | claim_id | wallet_address, amount, status, tx_id, requested_at, completed_at | App state | Claim state must match treasury transaction lifecycle. |
| Analytics events | backend/data/analytics.json | analytics_events | event_id | event_type, route, category, timestamp, anonymous_session_id, metadata | Operational logs | Retention pruning and privacy rules must carry over. |
| Incidents | backend/data/incidents.json | incidents | id | title, description, severity, status, affected_services, created_at, updated_at | Operational state | Resolved history should remain public and ordered. |
| Reports | backend/data/reports.json | reports | report_id | target_type, target_id, reported_by, reason, description, status, moderator_note | Operational moderation state | Sanitize unsafe text and secrets during any export. |
| Audit export metadata | Generated audit exports | audit_exports_metadata | export_id | export_type, chain_height, latest_block_hash, sha256, generated_at | Operational metadata | Keep export hashes tied to immutable chain snapshots. |
| Storage health snapshots | Health/readiness endpoints | storage_health_snapshots | snapshot_id | overall_status, chain_height, latest_block_hash, warnings_count, errors_count, checked_at | Operational metadata | Never expose full server paths or raw file contents. |
| Indexes | indexes.json | indexes_cache | schema_version plus chain height/hash | built_at, chain_height, latest_block_hash, indexes | Derived cache | Always rebuild after migration; never treat as source of truth. |
The PostgreSQL schema draft in the repository is preparation only and keeps raw JSONB columns for blocks and transactions so historical compatibility is preserved. See storage adapters, PostgreSQL readiness, and derived indexes for cache behavior.