Database Architecture

The astro-basics database architecture uses a modular abstraction layer (v2.0.0) that provides a clean, unified API for database operations while supporting multiple providers. The architecture implements the delegation pattern for improved maintainability and scalability.

Overview

The database abstraction layer provides:

Provider Independence: Write code once, run on any supported database
Modular Design: Feature-specific modules for messages, events, and users
Type Safety: Full TypeScript support with strict mode
Zero Breaking Changes: Backward compatible with existing code
Scalable Architecture: Easy to add new features or providers

🏗️ Modular Structure

11 focused modules replace monolithic 1,464-line file. Each module has a single responsibility.

🔄 Delegation Pattern

Provider classes delegate operations to specialized feature classes for clean separation of concerns.

🎯 Single API

Same code works with both Supabase (PostgreSQL) and Turso (LibSQL) - no provider-specific logic needed.

🚀 Easy Extension

Add new features by creating feature-specific modules. No risk to existing operations.

Architecture Diagram

src/libs/database/
├── index.ts                    # Public API
├── factory.ts                  # Provider detection
├── shared/
│   └── field-validators.ts    # Shared utilities (DRY)
└── providers/
    ├── turso/
    │   ├── index.ts            # TursoDatabase (delegation)
    │   ├── messages.ts         # Message operations
    │   ├── events.ts           # Event operations
    │   ├── users.ts            # User operations
    │   └── converters.ts       # Type conversions
    └── supabase/
        └── (same structure)

Basic Usage

Getting Database Instance

import { getDatabase } from '#libs/database'

// Automatically detects and uses configured provider
const db = getDatabase()

// Check which provider is active
console.log(db.getProviderName()) // 'turso' or 'supabase'

Client Operations

import type { ClientData } from '#libs/database-types'

// Create client
const clientData: ClientData = {
  nickname: 'John Doe',
  gender: 'prefer_not_to_say',
  email: 'john@example.com',
}

const clientId = await db.insertClient(clientData)

// Query clients
const clients = await db.getClients({
  limit: 10,
})

// Fetch one back
const client = await db.getClientById(clientId)

Event Operations

import type { EventData } from '#libs/database'

// Create event
const eventData: EventData = {
  name: 'Team Meeting',
  event_date: '2025-12-15',
  start_time: '14:00',
  duration: 60,
  location: 'Conference Room A',
  created_by: userId,
}

const eventId = await db.insertEvent(eventData)

// Query upcoming events
const upcomingEvents = await db.getEvents({
  from_date: new Date().toISOString().split('T')[0],
  limit: 50,
})

Key Concepts

Provider Independence

Write your code once using the Database interface. The abstraction layer handles provider-specific details:

// Same code works with both providers
const clients = await db.getClients({ limit: 10 })

// LibSQL-specific implementation (hidden from you)
const result = await tursoClient.execute({
  sql: 'SELECT * FROM clients LIMIT ?',
  args: [10],
})

// PostgreSQL-specific implementation (hidden from you)
const { data } = await supabaseClient
  .from('clients')
  .select('*')
  .limit(10)

Delegation Pattern

Provider classes delegate operations to specialized feature modules:

export class TursoDatabase implements Database {
  private clientOps: TursoClientOperations
  private eventOps: TursoEventOperations
  private userOps: TursoUserOperations

  async insertClient(data: ClientData): Promise<string> {
    return await this.clientOps.insertClient(data) // Delegation!
  }

  async insertEvent(data: EventData): Promise<string> {
    return await this.eventOps.insertEvent(data) // Delegation!
  }
}

Benefits:

Provider class stays small (128 lines)
Business logic isolated in feature modules
Easy to test individual features
No risk of breaking unrelated code

Shared Utilities

Common validation and security logic is centralized in shared/ directory:

// Single source of truth for allowed event fields
export const EVENT_ALLOWED_FIELDS: Set<keyof Event> = new Set([
  'id', 'name', 'event_date', 'location', // ... all valid fields
])

// Prevents SQL injection via field whitelisting
export function validateEventFields(fields?: (keyof Event)[]): string {
  const validFields = fields?.filter(f => EVENT_ALLOWED_FIELDS.has(f))
  return validFields?.join(', ') ?? '*'
}

Used by both providers: No code duplication, consistent security.

Provider Detection

The system automatically detects available providers:

import { detectDatabaseProviders } from '#libs/database'

const detection = await detectDatabaseProviders()
console.log(detection)
// {
//   provider: 'supabase',
//   available: ['turso', 'supabase'],
//   configured: ['supabase'],
//   recommended: 'supabase'
// }

Detection Priority:

Explicit DATABASE_PROVIDER environment variable
Supabase if configured
Turso if configured
Error if none configured

Adding New Features

The modular architecture makes it easy to add new features without touching existing code.

Example: Adding a “comments” feature

export class TursoCommentOperations {
  async insertComment(data: CommentData): Promise<number> {
    // Turso-specific implementation
  }

  async getComments(options?: CommentQueryOptions): Promise<Comment[]> {
    // Turso-specific implementation
  }
}

export class SupabaseCommentOperations {
  async insertComment(data: CommentData): Promise<number> {
    // Supabase-specific implementation
  }

  async getComments(options?: CommentQueryOptions): Promise<Comment[]> {
    // Supabase-specific implementation
  }
}

Step 2: Add Delegation

// providers/turso/index.ts (and supabase/index.ts)
import { TursoCommentOperations } from './comments'

export class TursoDatabase implements Database {
  private commentOps: TursoCommentOperations

  constructor() {
    // ... existing operations
    this.commentOps = new TursoCommentOperations()
  }

  async insertComment(data: CommentData): Promise<number> {
    return await this.commentOps.insertComment(data)
  }
}

Step 3: Update Interface

export interface Database {
  // ... existing operations
  insertComment(data: CommentData): Promise<number>
  getComments(options?: CommentQueryOptions): Promise<Comment[]>
}

Done! New feature added without modifying existing message or event code.

Best Practices

Always Use Abstraction Layer

// ✅ CORRECT
import { getDatabase } from '#libs/database'
const db = getDatabase()
const clients = await db.getClients()

// ❌ WRONG - Direct provider access
import { createClient } from '@supabase/supabase-js'
const client = createClient(url, key)

Use Path Aliases

// ✅ CORRECT
import { getDatabase } from '#libs/database'
import type { Event } from '#libs/database'

// ❌ WRONG - Relative imports
import { getDatabase } from '../../libs/database'

Handle Null Returns

// ✅ CORRECT
const client = await db.getClientById(clientId)
if (client) {
  console.log(client.nickname)
} else {
  console.log('Client not found')
}

// ❌ WRONG - Potential null reference
const client = await db.getClientById(clientId)
console.log(client.nickname) // Error if client is null

Use TypeScript Types

// ✅ CORRECT
import type { EventData, EventQueryOptions } from '#libs/database'

const eventData: EventData = {
  name: 'Meeting',
  event_date: '2025-12-10',
}

const options: EventQueryOptions = {
  from_date: '2025-12-01',
  limit: 50,
}

// ❌ WRONG - No type checking
const eventData = {
  name: 'Meeting',
  date: '2025-12-10', // Wrong field name, no error!
}

Performance

The modular architecture has no performance impact:

Same execution speed as monolithic version
Bundlers combine modules into single bundle
Tree shaking eliminates unused code
Negligible memory overhead

Benchmark (10,000 operations):

Operation	v1.x (monolithic)	v2.0 (modular)	Difference
insertEvent	1,543ms	1,537ms	-0.4%
getEvents	945ms	949ms	+0.4%

Next Steps

Events System - Learn about the events feature
Recurring Events - Implement recurring event patterns
Database Switching - Switch between providers

Technical References

Database API Reference - Complete API documentation
Modular Architecture - Technical deep dive
Migration History - Schema evolution