Skip to content

DATABASE_ARCHITECTURE.md

Jump to bottom
CeloHT edited this page Aug 10, 2026 · 1 revision

CeloHT Database Architecture

Version: 1.0 Project: CeloHT Primary Database Model: Relational Recommended Technology: PostgreSQL

1. Purpose

This document defines the conceptual database architecture for CeloHT applications.

The database stores application information that does not need to exist on the public blockchain.

Blockchain data and application data must remain conceptually separate.

2. Database Architecture

                         CeloHT Applications
                                │
                                ▼
                            API Layer
                                │
                                ▼
                         Service Layer
                                │
                                ▼
                       Data Access Layer
                                │
                                ▼
                         PostgreSQL DB
                                │
          ┌─────────────────────┼─────────────────────┐
          ▼                     ▼                     ▼
       Users                 Agents                Programs
          │                     │                     │
          └─────────────────────┼─────────────────────┘
                                ▼
                           Impact Data

3. Database Responsibilities

The database may store:

  • User profiles
  • Agent profiles
  • Educational progress
  • Program information
  • Content metadata
  • Application configuration
  • Impact records
  • Blockchain indexing metadata

The database should not store:

  • Seed phrases
  • Private keys
  • Wallet passwords
  • Sensitive secrets

4. Conceptual Schema

USERS
 │
 ├── PROFILES
 │
 ├── EDUCATION_PROGRESS
 │
 └── AGENT_RELATIONSHIPS

AGENTS
 │
 ├── AGENT_OPERATIONS
 │
 └── COMMUNITIES

PROGRAMS
 │
 ├── EDUCATION
 └── REFORESTATION

BLOCKCHAIN
 │
 ├── TRANSACTIONS
 ├── CONTRACTS
 └── EVENTS

IMPACT
 │
 ├── TRAINING
 ├── TRANSACTIONS
 ├── AGENTS
 └── TREES

5. Users

A conceptual user record may contain:

User
├── id
├── wallet_address
├── created_at
├── updated_at
└── status

Only information required by the application should be stored.

6. Agents

Potential agent information:

Agent
├── id
├── wallet_address
├── status
├── community_id
├── created_at
└── updated_at

Sensitive operational information should be protected.

7. Education

Potential education records:

Course
├── id
├── title
├── description
└── status

Enrollment
├── id
├── user_id
├── course_id
├── progress
└── completed_at

8. Communities

Community records may contain:

Community
├── id
├── name
├── region
├── status
└── created_at

Sensitive geographic information should not be unnecessarily exposed.

9. Reforestation

Potential records:

ReforestationProgram
├── id
├── community_id
├── start_date
├── status
└── description

PlantingActivity
├── id
├── program_id
├── quantity
├── date
└── verification_status

10. Blockchain Data

Blockchain-related database records should complement—not replace—the blockchain.

Example:

BlockchainTransaction
├── id
├── tx_hash
├── network
├── from_address
├── to_address
├── asset
├── amount
├── status
├── block_number
└── timestamp

The transaction hash should remain the primary external reference.

11. Smart Contracts

A contract registry may contain:

Contract
├── id
├── name
├── address
├── network
├── version
├── verification_status
├── deployed_at
└── repository

12. Blockchain Events

Indexed events may contain:

BlockchainEvent
├── id
├── tx_hash
├── contract_address
├── event_name
├── block_number
├── payload
└── timestamp

The event record should preserve enough information to trace the original blockchain transaction.

13. Impact Metrics

Impact metrics should use explicit definitions.

Examples:

ImpactMetric
├── id
├── metric_name
├── value
├── unit
├── period
├── source
├── verification_status
└── created_at

14. Database Security

Security controls include:

  • Strong authentication
  • Least privilege
  • Encryption
  • Backups
  • Network restrictions
  • Monitoring
  • Audit logs

15. Row-Level Security

Where supported and appropriate, row-level security can restrict which users or roles can access specific records.

Conceptually:

User
 │
 ▼
Authorization
 │
 ▼
Row-Level Policy
 │
 ├── Allowed
 └── Denied

16. Indexing

Indexes should support common query patterns.

Potential indexes:

  • Wallet address
  • Transaction hash
  • Agent ID
  • Community ID
  • Course ID
  • Timestamp
  • Status

Indexes should be introduced based on measured query requirements.

17. Data Integrity

The database should use:

  • Primary keys
  • Foreign keys
  • Unique constraints
  • Check constraints
  • Transactions
  • Appropriate validation

Application validation should complement database constraints.

18. Migrations

Database schema changes should be version-controlled.

Migration
   │
   ▼
Review
   │
   ▼
Test
   │
   ▼
Staging
   │
   ▼
Production

Destructive migrations require special review.

19. Backup Architecture

Production Database
        │
        ▼
Automated Backup
        │
        ▼
Protected Storage
        │
        ▼
Restore Test

Backups should be tested periodically.

20. Data Retention

Data should be retained only as long as required.

Sensitive information should have documented retention policies.

Blockchain records are generally immutable and therefore require careful consideration before being written on-chain.

21. Database Monitoring

Monitor:

  • Database availability
  • Query latency
  • Connection count
  • Storage usage
  • Failed queries
  • Backup status
  • Replication status where applicable

22. Database Recovery

Recovery should support:

  • Backup restoration
  • Point-in-time recovery where available
  • Schema reconstruction
  • Application reconnection
  • Data integrity validation

23. Database Design Principles

  1. Keep blockchain and application data separate.
  2. Minimize sensitive data.
  3. Use relational integrity.
  4. Encrypt sensitive data where appropriate.
  5. Use least privilege.
  6. Version schema changes.
  7. Test backups.
  8. Monitor production performance.
  9. Avoid unnecessary duplication.
  10. Preserve blockchain transaction references.

Status

Document: Database Architecture Version: 1.0 Recommended Database: PostgreSQL

© 2026 CeloHT - Open Source. Global Impact. Licensed under Apache.

CeloHT

Community-powered Web3 for real-world impact.

CeloHT is an open-source community initiative building practical solutions around Web3, financial inclusion, education, decentralized services, and environmental impact.

Learn. Build. Participate. Impact.

Clone this wiki locally