DATABASE.md

Database

This document describes the data architecture used by the CeloHT ecosystem.

Although CeloHT is built on the Celo blockchain, not all application data belongs on-chain. The platform uses a hybrid architecture that combines blockchain transparency with secure off-chain storage.

---

Philosophy

CeloHT follows the principle:

Store on-chain only what benefits from decentralization, immutability, and public verification. Store off-chain only what improves performance, privacy, and user experience.

---

Architecture

                    Users
                      │
                      ▼
              CeloHT Web Application
                      │
        ┌─────────────┴─────────────┐
        │                           │
        ▼                           ▼
 Smart Contracts             Backend Services
        │                           │
        ▼                           ▼
 Celo Blockchain            PostgreSQL Database
                                     │
                                     ▼
                              Analytics & Reports

---

On-Chain Data

Stored on the Celo blockchain:

• Smart contract state
• Governance proposals
• Voting results
• Treasury transactions
• Certificate verification hashes
• Agent verification records
• Environmental impact proofs

---

Off-Chain Data

Stored in the application database:

• User profiles
• Course metadata
• Educational content
• Images
• Videos
• Notifications
• Audit logs
• Analytics
• Search indexes
• Cached data

Sensitive personal information should never be stored on-chain.

---

Database Engine

Recommended production database:

• PostgreSQL

Development options:

• PostgreSQL
• SQLite

---

Data Models

Main entities include:

• Users
• Agents
• Courses
• Certificates
• Programs
• Communities
• Events
• Reforestation Campaigns
• Governance Proposals
• Partners
• Donations
• Reports

---

Security

Database security includes:

• Encryption at rest
• Encryption in transit
• Role-based access control
• Daily backups
• Audit logging
• Automatic updates
• Input validation

---

Backups

Recommended backup policy:

• Daily backups
• Weekly full backups
• Monthly archive backups
• Disaster recovery testing

Backups should be encrypted and stored in multiple secure locations.

---

Privacy

CeloHT is committed to protecting user privacy.

Personal information should:

• Be collected only when necessary.
• Be securely stored.
• Never be sold.
• Be deleted upon legitimate request, where applicable.

---

Performance

Performance optimizations include:

• Indexed queries
• Query caching
• Pagination
• Connection pooling
• Read optimization
• Efficient schema design

---

Analytics

Analytics may include:

• Active users
• Course completion
• Agent activity
• Community growth
• Reforestation statistics
• Governance participation
• Platform performance

Analytics should never compromise user privacy.

---

Logging

Application logs may include:

• Authentication events
• API requests
• Errors
• Security events
• Smart contract interactions
• System health

Logs should be retained according to the project's retention policy.

---

Disaster Recovery

Recovery procedures should include:

• Automated backups
• Backup verification
• Database replication
• Recovery testing
• Incident documentation

---

Future Improvements

Planned enhancements include:

• Read replicas
• High availability
• Distributed caching
• Data warehouse
• Real-time analytics
• AI-powered reporting
• IPFS integration for decentralized file storage

---

Guiding Principle

The CeloHT data architecture is designed to balance:

• Security
• Transparency
• Privacy
• Scalability
• Reliability
• Performance

This hybrid approach enables CeloHT to deliver a secure, efficient, and community-focused platform while leveraging the strengths of both blockchain and traditional database technologies.