Database Dave

A centralized database management system for multiple bots using Supabase.

Overview

Database Dave helps you manage your database schemas across multiple bots. It provides:

• Table creation and management for bots
• A central registry of all tables, columns, and their relationships
• Schema analysis tools to inspect existing databases
• Documentation generation for your schemas
• Integration with Supabase's built-in event system
• Automated conflict resolution for table names
• CI/CD integration for database schema management

Getting Started

Prerequisites

• Node.js (v14+)
• Supabase account and project

Installation

1. Clone the repository:

   git clone https://github.com/your-username/database-dave.git
   cd database-dave

2. Install dependencies:

   npm install

3. Create a .env file by copying .env.example:

   cp .env.example .env

4. Update the .env file with your Supabase credentials:

   SUPABASE_URL=https://your-project.supabase.co
   SUPABASE_KEY=your-supabase-key
   REGISTRY_SCHEMA=registry
   LOG_LEVEL=debug

Setup

1. Initialize the Supabase functions needed by Database Dave:

   npm run init-supabase

   If this fails, you will need to create the functions manually in the Supabase SQL Editor (see SQL Setup section below).

2. Initialize the registry schema and tables:

   npm run setup

Importing Bot Database Schemas

The most powerful way to manage bot tables is through the schema import system:

1. Create a db-schema.json file defining your bot's database requirements:

   {
     "bot_name": "YourBot",
     "version": "1.0.0",
     "tables": [
       {
         "name": "items",
         "columns": [
           {
             "name": "name",
             "dataType": "TEXT",
             "isNullable": false
           }
         ]
       }
     ]
   }

2. Import the schema using the CLI:

   npm run import-schema -- --source path/to/db-schema.json --output path/to/save/mappings.json

3. The command creates all necessary tables and returns a mapping between logical and physical table names.

For detailed documentation on the schema format, see Schema Definition Guide.

Creating Tables Manually

You can also create tables directly using the CLI:

npm run create-table -- --bot <bot-name> --table <table-name> --schema <schema-name> --columns '[{"name":"column1","dataType":"TEXT"},{"name":"column2","dataType":"INTEGER"}]'

Options:

• --bot <name>: Name of the bot (required)
• --table <name>: Name of the table to create (required)
• --schema <name>: Schema name (defaults to 'public')
• --description <text>: Description of the table
• --shared: Whether the table is shared between multiple bots
• --columns <json>: JSON string containing column definitions

Analyzing a Database

If you have existing database tables, you can analyze and register them with Database Dave:

npm run analyze -- --bot <bot-name> --schema <schema-name>

Options:

• --bot <name>: Name of the bot (required)
• --description <text>: Description of the bot
• --version <version>: Version of the bot
• --schema <schema>: Database schema to analyze (defaults to 'public')

CI/CD Integration

For automated database schema management, integrate Database Dave into your CI/CD pipeline:

# GitHub Actions example
name: Deploy Bot

on:
  push:
    branches: [ main ]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      # ... other steps ...
      
      - name: Update Database Schema
        run: |
          git clone https://github.com/your-org/database-dave.git
          cd database-dave
          npm install
          npm run import-schema -- --source ../db-schema.json --output ../db-mappings.json
          
      # ... continue deployment ...

This automatically creates and updates database tables whenever you deploy your bot.

SQL Setup

Setting Up SQL Functions

If the automatic setup fails, you can manually create the required SQL functions in the Supabase SQL Editor:

1. Navigate to your Supabase project dashboard
2. Go to the SQL Editor section
3. Create a new query
4. Copy and paste the contents of setup-functions.sql into the editor
5. Run the query to create the functions
6. Create another query with the contents of setup-schema.sql
7. Run the query to create the registry schema and tables

Architecture

Database Dave uses a registry pattern to track all database schemas:

• registry.bots: Tracks all bots in the system
• registry.tables: Tracks all database tables
• registry.columns: Tracks all columns in all tables
• registry.relationships: Tracks relationships between tables
• registry.events: Tracks event triggers
• registry.event_subscriptions: Tracks bot subscriptions to events

Integration with Supabase

Database Dave leverages Supabase features:

• Supabase Auth: For secure access to the registry
• PostgreSQL Database: For storing the registry data
• PostgreSQL Functions: For schema and table management
• Supabase Realtime: For event notifications between bots
• RLS Policies: For granular access control to registry data

Development

Build the project:

npm run build

Run in development mode:

npm run dev

Run tests:

npm test

Future Enhancements

• Admin dashboard for visualizing database schemas
• Schema migration tools
• Advanced relationship detection
• Schema optimization recommendations
• Enhanced integration with Supabase Realtime

License

MIT