Hybrid Database Implementation Plan

Implementation plan for space-per-database + universe-per-collection architecture

🎯 Hybrid Database Implementation Plan

Architecture: ONE Database per Space + Collections per Universe
Status: ✅ APPROVED
Ready to Implement: YES

🏗️ Implementation Phases

Phase 1: Storage System Enhancement (4 hours)

1.1 Add Space/Universe Scoping to GgStorage

File: libs/storage-system/src/lib/services/gg-storage.service.ts

New Methods:

// Space-scoped (shared assets)
async putInSpace(spaceId: string, key: string, value: any)
async getFromSpace(spaceId: string, key: string)
async listInSpace(spaceId: string, prefix?: string)

// Universe-scoped (isolated data)
async putInUniverse(spaceId: string, universeId: string, key: string, value: any)
async getFromUniverse(spaceId: string, universeId: string, key: string)
async listInUniverse(spaceId: string, universeId: string, prefix?: string)

// Cross-universe queries
async queryUniverseData(spaceId, universeId, collection, filter)
async queryAcrossUniverses(spaceId, collection, filter)

1.2 Update Backend Space Service

File: apis/backend-api/src/app/spaces/spaces.service.ts

Enhancement:

async createSpace(spaceData) {
  // 1. Create space record
  const space = await this.spacesModel.create(spaceData);
  
  // 2. Create MongoDB database: space_{spaceId}
  const dbName = `space_${space.id}`;
  await this.createSpaceDatabase(dbName);
  
  // 3. Initialize collections
  await this.initializeSpaceCollections(dbName);
  
  return space;
}

private async createSpaceDatabase(dbName: string) {
  // Create MongoDB database with collections
  const collections = [
    'space_metadata',
    'team_members',
    'billing',
    'space_templates',
    'space_flows',
    'space_templates',
    'audit_logs'
  ];
  
  for (const collection of collections) {
    await this.mongoClient.db(dbName).createCollection(collection);
  }
}

1.3 Update Backend Universe Service

File: apis/backend-api/src/app/universes/universes.service.ts (or create new service)

Enhancement:

async createUniverse(spaceId, universeData) {
  // 1. Create universe record
  const universe = await this.universesModel.create({
    ...universeData,
    spaceId
  });
  
  // 2. Create universe collections in space database
  const dbName = `space_${spaceId}`;
  await this.initializeUniverseCollections(dbName, universe.id);
  
  return universe;
}

private async initializeUniverseCollections(dbName: string, universeId: string) {
  const prefix = `universe_${universeId}_`;
  const collections = [
    `${prefix}metadata`,
    `${prefix}pages`,
    `${prefix}widgets`,
    `${prefix}flows`,
    `${prefix}modules`,
    `${prefix}deployments`
  ];
  
  for (const collection of collections) {
    await this.mongoClient.db(dbName).createCollection(collection);
    // Create indexes for performance
    await this.mongoClient.db(dbName).collection(collection)
      .createIndex({ createdAt: 1 });
  }
}

Deliverable: Storage system supports hybrid model

Phase 2: Space Management App (6 hours)

2.1 Create NX App

npx nx g @nx/angular:app space-management \
  --routing=true \
  --style=scss \
  --standalone=true

2.2 App Structure

apps/space-management/
├── src/
│   ├── app/
│   │   ├── layout/
│   │   │   ├── main-layout.component.ts
│   │   │   └── universe-switcher.component.ts
│   │   │
│   │   ├── dashboard/
│   │   │   ├── universes-dashboard.component.ts
│   │   │   ├── universe-card.component.ts
│   │   │   └── quick-stats.component.ts
│   │   │
│   │   ├── data-browser/
│   │   │   ├── data-browser.component.ts
│   │   │   ├── collections-list.component.ts
│   │   │   ├── data-table.component.ts
│   │   │   ├── record-editor.component.ts
│   │   │   └── query-builder.component.ts
│   │   │
│   │   ├── deployment-manager/
│   │   │   ├── deployment-history.component.ts
│   │   │   └── deployment-details.component.ts
│   │   │
│   │   └── analytics/
│   │       ├── analytics-dashboard.component.ts
│   │       └── charts/
│   │
│   └── ...
└── ...

2.3 Core Services

Files to Create:

apps/space-management/src/app/services/
├── space-data.service.ts       # Query space database
├── universe-data.service.ts    # Query universe collections
├── data-export.service.ts      # Export functionality
└── analytics.service.ts        # Cross-universe analytics

Deliverable: Space management app operational

Phase 3: Data Browser Component (4 hours)

3.1 Create Library

npx nx g @nx/angular:library data-browser \
  --directory=libs/data-browser \
  --standalone=true

3.2 Components

libs/data-browser/src/lib/components/
├── data-browser-dashboard/
│   └── data-browser-dashboard.component.ts
│
├── collections-list/
│   └── collections-list.component.ts      # List all collections with counts
│
├── data-table/
│   ├── data-table.component.ts            # Paginated table view
│   ├── data-table-cell.component.ts       # Cell renderer
│   └── data-table-actions.component.ts    # Row actions
│
├── record-editor/
│   ├── record-editor.component.ts         # Edit single record
│   └── field-editor.component.ts          # Type-specific editors
│
├── query-builder/
│   ├── query-builder.component.ts         # Visual MongoDB queries
│   ├── filter-builder.component.ts        # Add filters visually
│   └── query-preview.component.ts         # Show query JSON
│
└── data-export/
    └── data-export.component.ts           # Export to CSV/JSON/Excel

3.3 Key Features

DataBrowserDashboard:
✅ Show all collections in universe
✅ Display record counts
✅ Quick search across collections
✅ Recent queries

DataTable:
✅ Paginated view (50 records per page)
✅ Sortable columns
✅ Inline editing
✅ Bulk actions (delete, export)
✅ Real-time updates

RecordEditor:
✅ Form-based editing
✅ Type-specific inputs (date picker, etc.)
✅ Validation
✅ Save/cancel
✅ Delete confirmation

QueryBuilder:
✅ Visual filter builder
✅ AND/OR logic
✅ Comparison operators (=, !=, >, <, contains, etc.)
✅ Date range selectors
✅ Query preview (MongoDB syntax)
✅ Save queries for reuse

Deliverable: Complete data management UI

Phase 4: Database Management Blocks (3 hours)

4.1 New Practical Blocks

File: libs/singularity-lib/src/lib/templates/database-management-blocks.ts

export const QUERY_COLLECTION_TEMPLATE: BlockTemplate = {
  id: 'query_collection',
  type: 'function',
  name: 'Query Collection',
  title: 'Query Database',
  description: 'Query records from a collection with filters',
  category: 'database',
  
  inputTemplates: [
    { name: 'collection', type: 'input', dataType: 'string', required: true, description: 'Collection name' },
    { name: 'filter', type: 'input', dataType: 'object', required: false, description: 'MongoDB filter' },
    { name: 'limit', type: 'input', dataType: 'number', required: false, description: 'Max results' }
  ],
  outputTemplates: [
    { name: 'records', type: 'output', dataType: 'array', required: true, description: 'Query results' },
    { name: 'count', type: 'output', dataType: 'number', required: false, description: 'Total found' }
  ],
  
  defaultColor: '#8b5cf6',
  icon: 'database',
  defaultSize: { width: 220, height: 120 },
  
  executionFunction: 'executeQueryCollection',
  version: '1.0.0',
  tags: ['database', 'query', 'mongodb'],
  popularity: 90,
  featured: true
};

export const INSERT_RECORD_TEMPLATE: BlockTemplate = {
  id: 'insert_record',
  type: 'action',
  name: 'Insert Record',
  title: 'Create Record',
  description: 'Insert new record into collection',
  category: 'database',
  
  inputTemplates: [
    { name: 'collection', type: 'input', dataType: 'string', required: true, description: 'Collection name' },
    { name: 'data', type: 'input', dataType: 'object', required: true, description: 'Record data' }
  ],
  outputTemplates: [
    { name: 'id', type: 'output', dataType: 'string', required: true, description: 'Inserted record ID' },
    { name: 'record', type: 'output', dataType: 'object', required: false, description: 'Complete record' }
  ],
  
  defaultColor: '#10b981',
  icon: 'plus-circle',
  defaultSize: { width: 220, height: 120 },
  
  executionFunction: 'executeInsertRecord',
  version: '1.0.0',
  tags: ['database', 'create', 'insert'],
  popularity: 95,
  featured: true
};

export const UPDATE_RECORD_TEMPLATE: BlockTemplate = {
  id: 'update_record',
  type: 'action',
  name: 'Update Record',
  title: 'Update Record',
  description: 'Update existing record in collection',
  category: 'database',
  
  inputTemplates: [
    { name: 'collection', type: 'input', dataType: 'string', required: true, description: 'Collection name' },
    { name: 'id', type: 'input', dataType: 'string', required: true, description: 'Record ID' },
    { name: 'data', type: 'input', dataType: 'object', required: true, description: 'Updated data' }
  ],
  outputTemplates: [
    { name: 'success', type: 'output', dataType: 'boolean', required: true, description: 'Update success' },
    { name: 'record', type: 'output', dataType: 'object', required: false, description: 'Updated record' }
  ],
  
  defaultColor: '#f59e0b',
  icon: 'edit',
  defaultSize: { width: 220, height: 120 },
  
  executionFunction: 'executeUpdateRecord',
  version: '1.0.0',
  tags: ['database', 'update', 'edit'],
  popularity: 90,
  featured: true
};

export const DELETE_RECORD_TEMPLATE: BlockTemplate = {
  id: 'delete_record',
  type: 'action',
  name: 'Delete Record',
  title: 'Delete Record',
  description: 'Remove record from collection',
  category: 'database',
  
  inputTemplates: [
    { name: 'collection', type: 'input', dataType: 'string', required: true, description: 'Collection name' },
    { name: 'id', type: 'input', dataType: 'string', required: true, description: 'Record ID' }
  ],
  outputTemplates: [
    { name: 'success', type: 'output', dataType: 'boolean', required: true, description: 'Delete success' }
  ],
  
  defaultColor: '#ef4444',
  icon: 'trash',
  defaultSize: { width: 220, height: 120 },
  
  executionFunction: 'executeDeleteRecord',
  version: '1.0.0',
  tags: ['database', 'delete', 'remove'],
  popularity: 75,
  featured: false
};

export const AGGREGATE_DATA_TEMPLATE: BlockTemplate = {
  id: 'aggregate_data',
  type: 'function',
  name: 'Aggregate Data',
  title: 'Aggregate',
  description: 'Run aggregation pipeline on collection',
  category: 'database',
  
  inputTemplates: [
    { name: 'collection', type: 'input', dataType: 'string', required: true, description: 'Collection name' },
    { name: 'pipeline', type: 'input', dataType: 'array', required: true, description: 'Aggregation pipeline' }
  ],
  outputTemplates: [
    { name: 'results', type: 'output', dataType: 'array', required: true, description: 'Aggregated results' }
  ],
  
  defaultColor: '#8b5cf6',
  icon: 'chart-bar',
  defaultSize: { width: 220, height: 120 },
  
  executionFunction: 'executeAggregateData',
  version: '1.0.0',
  tags: ['database', 'aggregate', 'analytics'],
  popularity: 80,
  featured: false
};

Backend Endpoints:

POST /api/spaces/:spaceId/universes/:universeId/data/query
POST /api/spaces/:spaceId/universes/:universeId/data/insert
PUT /api/spaces/:spaceId/universes/:universeId/data/update
DELETE /api/spaces/:spaceId/universes/:universeId/data/delete
POST /api/spaces/:spaceId/universes/:universeId/data/aggregate
POST /api/spaces/:spaceId/universes/:universeId/data/export

// Cross-universe analytics
POST /api/spaces/:spaceId/analytics/query

Phase 2: Backend Space Database Service (3 hours)

Create: apis/backend-api/src/app/spaces/space-database.service.ts

@Injectable()
export class SpaceDatabaseService {
  constructor(
    @InjectConnection() private connection: Connection
  ) {}

  async createSpaceDatabase(spaceId: string): Promise<void> {
    const dbName = `space_${spaceId}`;
    
    // MongoDB automatically creates database on first write
    // Create initial collections
    const collections = [
      'space_metadata',
      'team_members',
      'billing',
      'space_templates',
      'space_flows',
      'space_templates',
      'audit_logs'
    ];

    for (const collectionName of collections) {
      await this.connection.db(dbName).createCollection(collectionName);
      
      // Create indexes
      await this.connection.db(dbName)
        .collection(collectionName)
        .createIndex({ createdAt: 1 });
      
      await this.connection.db(dbName)
        .collection(collectionName)
        .createIndex({ updatedAt: 1 });
    }

    // Initialize space metadata
    await this.connection.db(dbName)
      .collection('space_metadata')
      .insertOne({
        _id: spaceId,
        created: new Date(),
        status: 'active',
        universes: []
      });
  }

  async createUniverseCollections(spaceId: string, universeId: string): Promise<void> {
    const dbName = `space_${spaceId}`;
    const prefix = `universe_${universeId}_`;
    
    const collections = [
      `${prefix}metadata`,
      `${prefix}pages`,
      `${prefix}widgets`,
      `${prefix}flows`,
      `${prefix}modules`,
      `${prefix}deployments`
    ];

    for (const collectionName of collections) {
      await this.connection.db(dbName).createCollection(collectionName);
      
      // Universe-specific indexes
      await this.connection.db(dbName)
        .collection(collectionName)
        .createIndex({ universeId: 1 });
    }

    // Initialize universe metadata
    await this.connection.db(dbName)
      .collection(`${prefix}metadata`)
      .insertOne({
        _id: universeId,
        spaceId,
        created: new Date(),
        deployed: false,
        deploymentUrl: null
      });
  }
}

Phase 3: Data Browser Library (5 hours)

Create: libs/data-browser

Key Component: data-browser-dashboard.component.ts

@Component({
  selector: 'lib-data-browser',
  standalone: true,
  template: `
    <div class="data-browser">
      <!-- Universe Selector -->
      <div class="universe-selector">
        <select [(ngModel)]="selectedUniverse">
          @for (universe of universes(); track universe.id) {
            <option [value]="universe.id">{{ universe.name }}</option>
          }
        </select>
      </div>

      <!-- Collections List -->
      <div class="collections-panel">
        <h3>Collections</h3>
        @for (collection of collections(); track collection.name) {
          <div class="collection-item" 
               (click)="selectCollection(collection.name)"
               [class.active]="selectedCollection() === collection.name">
            <i class="fas fa-database"></i>
            <span>{{ collection.name }}</span>
            <span class="count">{{ collection.count }}</span>
          </div>
        }
      </div>

      <!-- Data Table -->
      <div class="data-panel">
        @if (selectedCollection()) {
          <lib-data-table
            [spaceId]="spaceId()"
            [universeId]="selectedUniverse()"
            [collection]="selectedCollection()"
            (recordEdit)="editRecord($event)"
            (recordDelete)="deleteRecord($event)">
          </lib-data-table>
        }
      </div>

      <!-- Record Editor Modal -->
      @if (editingRecord()) {
        <lib-record-editor
          [record]="editingRecord()"
          [collection]="selectedCollection()"
          (save)="saveRecord($event)"
          (cancel)="cancelEdit()">
        </lib-record-editor>
      }
    </div>
  `
})
export class DataBrowserComponent {
  readonly spaceId = input.required<string>();
  readonly universeId = input.required<string>();

  private readonly spaceData = inject(SpaceDataService);

  readonly selectedUniverse = signal<string>('');
  readonly selectedCollection = signal<string | null>(null);
  readonly editingRecord = signal<any>(null);

  readonly universes = signal<Array<{ id: string; name: string }>>([]);
  readonly collections = signal<Array<{ name: string; count: number }>>([]);

  async ngOnInit() {
    await this.loadUniverses();
    await this.loadCollections();
  }

  private async loadUniverses() {
    // Load all universes for this space
    const universes = await this.spaceData.getUniverses(this.spaceId());
    this.universes.set(universes);
    
    if (universes.length > 0) {
      this.selectedUniverse.set(this.universeId() || universes[0].id);
    }
  }

  private async loadCollections() {
    const universeId = this.selectedUniverse();
    const collections = await this.spaceData.getCollections(
      this.spaceId(),
      universeId
    );
    this.collections.set(collections);
  }

  async selectCollection(name: string) {
    this.selectedCollection.set(name);
  }

  async editRecord(record: any) {
    this.editingRecord.set(record);
  }

  async saveRecord(record: any) {
    await this.spaceData.updateRecord(
      this.spaceId(),
      this.selectedUniverse(),
      this.selectedCollection()!,
      record
    );
    this.editingRecord.set(null);
    await this.loadCollections(); // Refresh
  }
}

Deliverable: Full-featured data browser

Phase 4: Backend Data API (2 hours)

Create: apis/backend-api/src/app/data/data.controller.ts

@Controller('api/spaces/:spaceId/universes/:universeId/data')
@UseGuards(BackendAuthGuard)
export class DataController {
  constructor(
    private readonly spaceDatabaseService: SpaceDatabaseService
  ) {}

  @Post('query')
  async queryData(
    @Param('spaceId') spaceId: string,
    @Param('universeId') universeId: string,
    @Body() query: { collection: string; filter?: any; limit?: number; sort?: any }
  ) {
    const dbName = `space_${spaceId}`;
    const collectionName = `universe_${universeId}_${query.collection}`;
    
    const results = await this.connection.db(dbName)
      .collection(collectionName)
      .find(query.filter || {})
      .limit(query.limit || 50)
      .sort(query.sort || { createdAt: -1 })
      .toArray();

    return {
      success: true,
      data: results,
      count: results.length
    };
  }

  @Post('insert')
  async insertRecord(
    @Param('spaceId') spaceId: string,
    @Param('universeId') universeId: string,
    @Body() body: { collection: string; data: any }
  ) {
    const dbName = `space_${spaceId}`;
    const collectionName = `universe_${universeId}_${body.collection}`;
    
    const result = await this.connection.db(dbName)
      .collection(collectionName)
      .insertOne({
        ...body.data,
        createdAt: new Date(),
        updatedAt: new Date()
      });

    return {
      success: true,
      id: result.insertedId,
      data: body.data
    };
  }

  // Similar for update, delete, aggregate, export
}

Deliverable: Backend API for data operations

📊 Implementation Timeline

Week 1

Day 1-2: Storage system enhancement
Day 3-4: Backend space database service
Day 5: Testing & integration

Week 2

Day 1-2: Data browser library
Day 3-4: Space management app
Day 5: Database management blocks

Estimated: 10-12 working days

✅ Success Criteria

v1.1 Complete When:

Space-builder creates space → auto-creates database
Space-builder creates universe → auto-creates collections
Storage-system supports space + universe scoping
Data browser shows collections and records
Space-management app operational
Database management blocks functional
Backend APIs working
End-to-end tested

🎯 Ready to Implement

Architecture: ✅ Approved (Hybrid)
Documentation: ✅ Updated
Plan: ✅ Detailed

Next: Begin Phase 1 implementation! 🚀