Skip to content

DATABASE_ARCHITECTURE.md

Latest commit

 

History

History
685 lines (530 loc) · 19.6 KB

DATABASE_ARCHITECTURE.md

File metadata and controls

685 lines (530 loc) · 19.6 KB
Raw

🗄️ KeyBridge Database Architecture

╔════════════════════════════════════════════════════════════════╗
║                                                                ║
║     🔐 POSTGRESQL DATABASE WITH ACID COMPLIANCE 🔐            ║
║                                                                ║
║     Enterprise-Grade | Cryptographically Secure | Scalable     ║
║                                                                ║
╚════════════════════════════════════════════════════════════════╝

PostgreSQL ACID Compliant Encryption

📊 Database Overview

KeyBridge uses PostgreSQL 16 as its primary database, designed with military-grade security, full ACID compliance, and optimized for high-performance cryptographic operations.

flowchart TD
    A[Client Application] -->|TLS 1.3| B[Connection Pool]
    B --> C[(PostgreSQL 16)]
    
    C --> D[Users Table]
    C --> E[Sessions Table]
    C --> F[Files Table]
    C --> G[Audit Logs]
    C --> H[Verification Records]
    
    C --> I[Row-Level Security]
    C --> J[ACID Transactions]
    C --> K[Encryption at Rest]
    C --> L[WAL Logging]
    
    style A fill:#e1f5ff,stroke:#333,stroke-width:2px
    style C fill:#ff6b6b,stroke:#333,stroke-width:2px
    style I fill:#51cf66,stroke:#333,stroke-width:2px
    style J fill:#51cf66,stroke:#333,stroke-width:2px
    style K fill:#51cf66,stroke:#333,stroke-width:2px
    style L fill:#51cf66,stroke:#333,stroke-width:2px
Loading

🏗️ Database Schema Architecture

🎯 Core Tables Structure

┌─────────────────────────────────────────────────────────────────────┐
│                         📊 DATABASE SCHEMA                          │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  👤 USERS                    🔗 SESSIONS                           │
│  ┌──────────────┐            ┌──────────────────┐                   │
│  │ id (UUID)    │◄───────────┤ sender_id (FK)   │                   │
│  │ email        │            │ receiver_id (FK) │                   │
│  │ password_hash│            │ session_id (UUID)│                   │
│  │ role         │            │ status           │                   │
│  │ is_active    │            │ encrypted_key    │                   │
│  │ created_at   │            │ expires_at       │                   │
│  │ last_login_at│            │ metadata (JSONB) │                   │
│  └──────────────┘            └──────────────────┘                   │
│         │                             │                             │
│         │                    ┌────────┴────────┐                    │
│         │                    │                 │                    │
│         │              📁 FILES         📋 AUDIT_LOGS              │
│         │              ┌──────────┐    ┌──────────────┐             │
│         │              │ file_id  │    │ action       │             │
│         │              │ session_id│   │ user_id      │             │
│         │              │ encrypted│    │ resource_type│             │
│         │              │ size     │    │ timestamp    │             │
│         │              │ uploaded │    │ ip_address   │             │
│         │              └──────────┘    └──────────────┘             │
│         │                                                           │
│         │              🔐 VERIFICATION_RECORDS                      │
│         └──────────────►┌─────────────────────┐                     │
│                         │ session_id          │                     │
│                         │ vrh_hash            │                     │
│                         │ sender_public_key   │                     │
│                         │ receiver_public_key │                     │
│                         │ is_compromised      │                     │
│                         └─────────────────────┘                     │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

🔐 ACID Compliance Implementation

⚛️ Atomicity

BEGIN TRANSACTION;
  -- Step 1: Create Session
  INSERT INTO sessions (...);
  
  -- Step 2: Upload Files
  INSERT INTO files (...);
  
  -- Step 3: Log Audit
  INSERT INTO audit_logs (...);
COMMIT;
-- All succeed or all rollback

Features:

  • ✅ Transaction context managers
  • ✅ Foreign key cascades
  • ✅ Savepoint support
  • ✅ Atomic stored procedures

🎯 Consistency

-- Email validation
CHECK (email ~* '^[A-Za-z0-9._%+-]+@...')

-- Logical constraints
CHECK (created_at <= updated_at)
CHECK (sender_id != receiver_id)

-- Status transitions
CHECK (
  (status = 'PENDING' AND activated_at IS NULL)
  OR (status = 'ACTIVE' AND activated_at IS NOT NULL)
)

Features:

  • ✅ CHECK constraints
  • ✅ Foreign key constraints
  • ✅ Validation triggers
  • ✅ Consistency functions

🔒 Isolation

-- Row-level locking
SELECT * FROM sessions
WHERE session_id = $1
FOR UPDATE;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL 
  SERIALIZABLE;

Features:

  • ✅ READ COMMITTED (default)
  • ✅ REPEATABLE READ
  • ✅ SERIALIZABLE
  • ✅ Advisory locks

💾 Durability

WAL (Write-Ahead Logging)
┌─────────────────────┐
│ 1. Write to WAL     │
│ 2. Flush to disk    │
│ 3. Acknowledge      │
│ 4. Apply to DB      │
└─────────────────────┘

Features:

  • ✅ PostgreSQL WAL
  • ✅ Synchronous commits
  • ✅ Point-in-time recovery
  • ✅ Crash recovery

📋 Detailed Table Specifications

👤 Users Table

CREATE TABLE users (
    -- Identity
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    
    -- Authentication
    password_hash TEXT NOT NULL,  -- Argon2id hashed
    
    -- Profile
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    profile_picture_url TEXT,
    
    -- Access Control
    role user_role DEFAULT 'NORMAL_USER',
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    
    -- Tracking
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMPTZ,
    
    -- Constraints
    CONSTRAINT email_lowercase CHECK (email = LOWER(email)),
    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@...'),
    CONSTRAINT password_strong CHECK (LENGTH(password_hash) >= 60)
);

Key Features:

  • 🔑 UUID primary keys for security
  • 📧 Email normalization and validation
  • 🔐 Argon2id password hashing
  • ⏰ Automatic timestamp tracking
  • 🎭 Role-based access control

🔗 Sessions Table

CREATE TABLE sessions (
    -- Identity
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Relationships
    sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_id UUID REFERENCES users(id) ON DELETE CASCADE,
    sender_email VARCHAR(255),
    receiver_email VARCHAR(255),
    
    -- Session Info
    status session_status DEFAULT 'PENDING',
    approach VARCHAR(20) DEFAULT 'oob',  -- 'oob' or 'server'
    message TEXT,
    
    -- File Details
    file_name VARCHAR(500),
    file_size BIGINT,
    
    -- Cryptography
    metadata JSONB,  -- Contains encrypted keys, shares
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    activated_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL,
    
    -- Constraints
    CONSTRAINT sender_not_receiver CHECK (sender_id != receiver_id),
    CONSTRAINT expires_after_creation CHECK (expires_at > created_at),
    CONSTRAINT valid_status_transition CHECK (...)
);

Session Status Flow:

PENDING → ACTIVE → COMPLETED
   ↓        ↓          ↓
EXPIRED  FAILED   SUSPENDED

📁 Files Table

CREATE TABLE files (
    -- Identity
    file_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions ON DELETE CASCADE,
    
    -- File Data
    encrypted_content BYTEA NOT NULL,  -- AES-256-GCM encrypted
    encrypted_key TEXT NOT NULL,
    size BIGINT NOT NULL,
    
    -- Metadata
    original_name VARCHAR(500),
    mime_type VARCHAR(100),
    hash_sha256 VARCHAR(64),
    
    -- Tracking
    uploaded_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT size_positive CHECK (size > 0),
    CONSTRAINT key_not_empty CHECK (LENGTH(encrypted_key) > 0)
);

Encryption Details:

  • 🔐 Algorithm: AES-256-GCM
  • 🔑 Key Size: 256 bits
  • 🎲 IV: 96 bits (unique per file)
  • ✅ Authentication: Built-in GMAC

🔐 Verification Records Table

CREATE TABLE verification_records (
    -- Identity
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID UNIQUE REFERENCES sessions,
    
    -- Cryptographic Hashes
    vrh_hash TEXT NOT NULL,  -- Virtual Rendezvous Hash
    sender_public_key TEXT NOT NULL,
    receiver_public_key TEXT,
    
    -- MITM Detection
    is_compromised BOOLEAN DEFAULT FALSE,
    failed_validations INTEGER DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_verified_at TIMESTAMPTZ,
    
    -- Constraints
    CONSTRAINT vrh_not_empty CHECK (LENGTH(vrh_hash) > 0),
    CONSTRAINT validations_positive CHECK (failed_validations >= 0)
);

MITM Detection Mechanism:

Step 1: Generate VRH Hash (sender + receiver keys)
Step 2: Store in database
Step 3: Validate on every access
Step 4: Flag if hash mismatch
Step 5: Alert admin + log audit trail

📋 Audit Logs Table

CREATE TABLE audit_logs (
    -- Identity
    id BIGSERIAL PRIMARY KEY,
    
    -- Event Details
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id TEXT,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- Context
    details JSONB,
    ip_address INET,
    user_agent TEXT,
    
    -- Timestamp
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT timestamp_recent CHECK (
        timestamp >= '2025-01-01' AND 
        timestamp <= CURRENT_TIMESTAMP + INTERVAL '1 minute'
    )
);

Tracked Actions:

  • 🔐 USER_LOGIN / USER_LOGOUT
  • 📝 SESSION_CREATED / SESSION_JOINED / SESSION_COMPLETED
  • 📁 FILE_UPLOADED / FILE_DOWNLOADED
  • ⚠️ UNAUTHORIZED_ACCESS / MITM_DETECTED
  • 👤 USER_CREATED / USER_DELETED

🚀 Performance Optimizations

📊 Indexes

-- Unique Indexes (Enforcing Constraints)
CREATE UNIQUE INDEX idx_users_email ON users(LOWER(email));
CREATE UNIQUE INDEX idx_sessions_id ON sessions(session_id);

-- Performance Indexes
CREATE INDEX idx_sessions_active ON sessions(status) 
    WHERE status IN ('PENDING', 'ACTIVE');
    
CREATE INDEX idx_sessions_expires ON sessions(expires_at)
    WHERE status IN ('PENDING', 'ACTIVE');
    
CREATE INDEX idx_audit_timestamp ON audit_logs(timestamp DESC);

-- Composite Indexes
CREATE INDEX idx_sessions_user_status ON sessions(sender_id, receiver_id, status);
CREATE INDEX idx_files_session ON files(session_id, uploaded_at);

Index Strategy:

  • ✅ Unique indexes on frequently queried columns
  • ✅ Partial indexes for active sessions (reduces size)
  • ✅ Descending indexes for recent-first queries
  • ✅ Composite indexes for multi-column queries

🔄 Connection Pooling

# psycopg2 Connection Pool
connection_pool = SimpleConnectionPool(
    minconn=1,      # Minimum connections
    maxconn=10,     # Maximum connections
    dsn=database_url
)

Benefits:

  • ⚡ Reuse existing connections
  • 🎯 Reduce connection overhead
  • 📈 Handle concurrent requests
  • 🛡️ Prevent connection exhaustion

🛡️ Security Features

🔐 Encryption at Rest

┌─────────────────────────────────────────┐
│         Data Storage Layers             │
├─────────────────────────────────────────┤
│                                         │
│  Application Layer: AES-256-GCM         │
│         ↓                               │
│  PostgreSQL Layer: Transparent DE       │
│         ↓                               │
│  Filesystem Layer: LUKS/BitLocker       │
│         ↓                               │
│  Hardware Layer: SED (Self-Encrypting)  │
│                                         │
└─────────────────────────────────────────┘

🔒 Row-Level Security (RLS)

-- Enable RLS on sensitive tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own data
CREATE POLICY user_self_access ON users
    FOR SELECT
    USING (id = current_user_id());

-- Policy: Admins can see all
CREATE POLICY admin_full_access ON users
    FOR ALL
    USING (is_admin());

📈 Database Monitoring

🔍 Health Checks

-- Check active connections
SELECT count(*) FROM pg_stat_activity;

-- Check database size
SELECT pg_database_size(current_database());

-- Check table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check index usage
SELECT 
    schemaname, tablename, indexname,
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

📊 Performance Metrics

-- Slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

🔄 Backup & Recovery

💾 Backup Strategy

# Full database backup
pg_dump keybridge_db > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump keybridge_db | gzip > backup_$(date +%Y%m%d).sql.gz

# Custom format (recommended)
pg_dump -Fc keybridge_db > backup_$(date +%Y%m%d).dump

# Restore from backup
pg_restore -d keybridge_db backup_20260104.dump

🔄 Point-in-Time Recovery (PITR)

1. Enable WAL archiving in postgresql.conf
2. Configure archive_command
3. Take base backup
4. Archive WAL segments continuously
5. Restore to any point in time

📚 Database Schema Scripts

All schema files are located in sql/schema/:

sql/schema/
├── 01_initialize_database.sql   # Database & extensions
├── 02_create_core_tables.sql    # Users, OTP codes
├── 03_create_session_tables.sql # Sessions, files
├── 04_create_audit_tables.sql   # Audit logs
├── 05_create_indexes.sql        # Performance indexes
├── 06_create_functions.sql      # Stored procedures
├── 07_create_triggers.sql       # Automatic updates
├── 08_create_views.sql          # Reporting views
├── 09_insert_seed_data.sql      # Initial data
└── 10_acid_compliance.sql       # ACID enhancements

🚀 Getting Started

1️⃣ Install PostgreSQL

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql-16 postgresql-contrib

# macOS (Homebrew)
brew install postgresql@16

# Windows
# Download from https://www.postgresql.org/download/windows/

2️⃣ Create Database

# Connect to PostgreSQL
sudo -u postgres psql

# Create database
CREATE DATABASE keybridge_db;

# Create user
CREATE USER keybridge_user WITH PASSWORD 'secure_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE keybridge_db TO keybridge_user;

3️⃣ Run Schema Scripts

# Execute all schema files in order
cd sql/schema
psql -U keybridge_user -d keybridge_db -f 01_initialize_database.sql
psql -U keybridge_user -d keybridge_db -f 02_create_core_tables.sql
psql -U keybridge_user -d keybridge_db -f 03_create_session_tables.sql
# ... continue for all files

4️⃣ Configure Connection

# .env file
DB_URL=postgresql://keybridge_user:secure_password@localhost:5432/keybridge_db
DB_POOL_MIN=1
DB_POOL_MAX=10

🎯 Best Practices

✅ DO's

  • ✅ Always use transactions for multi-step operations
  • ✅ Use prepared statements to prevent SQL injection
  • ✅ Index frequently queried columns
  • ✅ Monitor slow queries and optimize
  • ✅ Regular backups (daily minimum)
  • ✅ Use connection pooling
  • ✅ Enable query logging for debugging
  • ✅ Validate data before insertion

❌ DON'Ts

  • ❌ Never store passwords in plain text
  • ❌ Don't use SELECT * in production
  • ❌ Avoid N+1 query problems
  • ❌ Don't skip foreign key constraints
  • ❌ Never commit secrets to version control
  • ❌ Don't run migrations without backups
  • ❌ Avoid long-running transactions
  • ❌ Don't ignore database logs

🔗 Useful Resources

🎉 Database Architecture Complete!