5-Tier Database Implementation Guide

This guide provides detailed implementation guidance for the 5-tier database architecture, building upon the concepts outlined in the 5-Tier Database Architecture Guide.

Cross-Tier Integration Patterns

Data Flow Without Foreign Keys

async function sendEmail(emailData: EmailData) {
    const transaction = new DatabaseTransaction();

    try {
        // 1. Store metadata in OLTP
        const emailRecord = await transaction.insertOLTP('emails', {
            tenant_id: emailData.tenantId,
            recipient: emailData.recipient,
            subject: emailData.subject,
            content_storage_key: emailData.storageKey
        });

        // 2. Store content in Content DB
        await transaction.insertContent('content_objects', {
            storage_key: emailData.storageKey,
            content_text: emailData.body,
            tenant_id: emailData.tenantId
        });

        // 3. Queue analytics job
        await transaction.insertQueue('job_queue', {
            job_type: 'track_email_sent',
            payload: { email_id: emailRecord.id },
            scheduled_at: new Date()
        });

        await transaction.commit();

    } catch (error) {
        await transaction.rollback();
        await cleanupOrphanedData(emailData.storageKey);
        throw error;
    }
}

Integration Best Practices

1. Opaque Storage Keys: Always use opaque keys for cross-tier references

2. Transaction Management: Implement proper transaction boundaries

3. Cleanup Mechanisms: Add background jobs for orphaned data cleanup

4. Consistency Checks: Regular verification of cross-tier data integrity

Operational Considerations

Performance Optimization

Query Optimization:

-- OLTP: B-tree indexes for lookups
CREATE INDEX idx_emails_tenant_status ON emails(tenant_id, status);
CREATE INDEX idx_campaigns_tenant_dates ON campaigns(tenant_id, created_at, status);

-- Content DB: Expiration and deduplication
CREATE INDEX idx_content_expires ON content_objects(expires_at) WHERE expires_at IS NOT NULL;
CREATE INDEX idx_content_hash ON content_objects(content_hash) WHERE content_hash IS NOT NULL;

-- Notifications: Fast unread queries
CREATE INDEX idx_notifications_unread ON notifications(user_id, created_at DESC) WHERE is_read = FALSE;

Connection Pooling:

const pools = {
    oltp: new Pool({
        connectionString: process.env.OLTP_DB_URL,
        max: 100,
        idleTimeoutMillis: 30000,
        connectionTimeoutMillis: 2000,
    }),
    content: new Pool({
        connectionString: process.env.CONTENT_DB_URL,
        max: 50,
        idleTimeoutMillis: 60000,
    }),
    olap: new Pool({
        connectionString: process.env.OLAP_DB_URL,
        max: 20,
        idleTimeoutMillis: 120000,
    })
};

Failure Scenarios and Recovery

OLTP Outage: Core functionality degraded, but notifications and external logging continue independently.

Content DB Outage: OLTP metadata available, show user-friendly errors, log to external systems for debugging.

OLAP Outage: Analytics dashboards unavailable, but operational systems continue normal function.

Queue Outage: New background jobs queued, existing processing continues, system degrades gracefully.

Notifications Outage: System continues operating, admin events logged separately.

Monitoring and Alerting

Key Metrics to Monitor:

• Query response times per tier

• Connection pool utilization

• Queue depth and processing rates

• Cross-tier consistency checks

• Tenant-specific performance metrics

Critical Alerts:

• Any tier exceeding 80% capacity

• Cross-tier consistency failures

• Connection pool exhaustion

• Queue job failures exceeding thresholds

Getting Started

Environment Setup

export OLTP_DB_URL="postgresql://localhost:5432/penguinmails_oltp"
export CONTENT_DB_URL="postgresql://localhost:5433/penguinmails_content"
export OLAP_DB_URL="postgresql://localhost:5434/penguinmails_olap"
export NOTIFICATIONS_DB_URL="postgresql://localhost:5435/penguinmails_notifications"
export QUEUE_REDIS_URL="redis://localhost:6379"

Database Initialization

psql $OLTP_DB_URL -f schemas/oltp/schema.sql
psql $CONTENT_DB_URL -f schemas/content/schema.sql
psql $OLAP_DB_URL -f schemas/olap/schema.sql
psql $NOTIFICATIONS_DB_URL -f schemas/notifications/schema.sql

Row-Level Security Setup

ALTER TABLE campaigns ENABLE ROW LEVEL SECURITY;
ALTER TABLE emails ENABLE ROW LEVEL SECURITY;
ALTER TABLE content_objects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON campaigns
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Development Guidelines

1. Always specify tenant_id: Every multi-tenant operation must include tenant isolation

2. Use opaque keys for content: Never expose Content DB relationships directly

3. Implement consistency checks: Add background jobs to verify cross-tier integrity

4. Monitor query performance: Set up query performance monitoring per tier

5. Test failure scenarios: Verify graceful degradation when any tier is unavailable

Code Review Checklist

• Tier Assignment: Is data stored in the appropriate tier?

• Tenant Isolation: Does every query respect RLS policies?

• Cross-Tier References: Are opaque keys used instead of foreign keys?

• Error Handling: How does this fail when each tier is unavailable?

• Performance: Are queries optimized for the target tier’s access patterns?

• Monitoring: Are appropriate metrics and logs emitted?

• Retention: Is there a plan for data lifecycle management?

---

Related Documentation:

• 5-Tier Database Architecture Guide - Core architectural concepts

• OLTP Database Documentation - Operational database implementation

• Content Database Documentation - Content storage patterns

• Queue System Documentation - Background job processing

• Database Infrastructure Overview - Complete ecosystem

Keywords: database implementation, cross-tier integration, operational patterns, performance optimization, deployment, monitoring