搵技能.../

Api Dual Database Architecture | Skills Pool

技能檔案

Api Dual Database Architecture

Work within the dual-database backend (local SQLite + shared PostgreSQL) including migrations, shared route gating, and audit/RBAC expectations. Use when adding features that touch SQLite migrations, shared-migrations, requireSharedDb, installation setup, or dual-mode (solo/shared) planning.

andres-sumihe0 星標2026年2月9日

職業
分類: SQL 數據庫

技能內容

Workspace Organizer uses two persistence layers:

Local SQLite: personal/workspace data
Shared PostgreSQL: team data (users, RBAC, audit logs, scripts, Control-M)

When to Use This Skill

“Add a migration”, “change DB schema”, “new table”
“Feature should be shared/team only”
“requireSharedDb”, “installation wizard”, “shared DB unavailable handling”
“Audit log every change”, “RBAC permissions”
“Dual-mode solo/shared” discussions or implementation

Key References (repo-local)

docs/technical-overview.md (DB clients, gating, installation service)
docs/database-schema.md
docs/architecture/dual-mode-implementation-plan.md
docs/architecture/dual-mode-implementation-guide.md

Rules of the Road

SQLite migrations live in and run on startup.

相關技能

快速安裝

Api Dual Database Architecture

npx skills add andres-sumihe/workspace-organizer

下載 Skill 打開源碼倉庫

作者: andres-sumihe
星標: 0
更新時間: 2026年2月9日
職業

本頁內容

01When to Use This Skill

apps/api/src/db/migrations/

Shared Postgres migrations live in apps/api/src/db/shared-migrations/ and run during installation/startup when configured.

Team features must be guarded:

requireSharedDb returns 503 with NOT_CONFIGURED or SHARED_DB_UNAVAILABLE when needed.
Protected endpoints must also use authMiddleware + RBAC (requirePermission).

Shared resource mutations must be audited via the audit service (when the resource is in Postgres).

Step-by-Step Workflow: Add a SQLite Migration

Create a new migration file under apps/api/src/db/migrations/.
Make it idempotent and safe for existing installs (avoid destructive changes unless explicitly planned).
Ensure foreign keys/indexes are included.
Run pnpm dev:api and confirm migrations apply cleanly.

Step-by-Step Workflow: Add a Shared (Postgres) Migration

Add a migration file under apps/api/src/db/shared-migrations/.
Keep migration IDs strictly increasing and deterministic.
Update any shared feature repositories/services to use the new table/column.
Ensure installation flow still works:
- installationService.configure() must run migrations successfully.
- installationService.initializeOnStartup() must tolerate missing config and report status.

Step-by-Step Workflow: Add a Shared-Only Feature

Put types into packages/shared if the UI consumes them.
Add shared migration(s) if needed.
Implement repository/service/controller.
Mount router under v1 and gate it:
- requireSharedDb at router level
- authMiddleware + RBAC at endpoint level
Add audit logging for create/update/delete.
Add API integration tests for:
- 503 when not configured
- 503 when disconnected
- 401/403 for auth/RBAC

Troubleshooting

Getting NOT_CONFIGURED: the shared DB connection string hasn’t been stored in local settings yet.
Getting SHARED_DB_UNAVAILABLE: config exists but connection is down or not initialized.
Breaking changes: update packages/shared first and run pnpm typecheck to catch cross-app drift.

Notes for Dual-Mode (Solo → Shared)

Keep HTTP boundary stable (web talks to API over HTTP).
Prefer mode-aware providers/middleware instead of duplicating routes.
Solo should remain fully functional offline; Shared is optional.

02

Key References (repo-local)

03Rules of the Road

04Step-by-Step Workflow: Add a SQLite Migration

05Step-by-Step Workflow: Add a Shared (Postgres) Migration

06Step-by-Step Workflow: Add a Shared-Only Feature

07Troubleshooting

08Notes for Dual-Mode (Solo → Shared)

資料庫架構師

Postgres Patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

Postgres Patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on Supabase best practices.

Database Migrations

Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate). Use when planning or implementing database schema changes.

Postgres Patterns

쿼리 최적화, 스키마 설계, 인덱싱, 보안을 위한 PostgreSQL 데이터베이스 패턴. Supabase 모범 사례 기반.

Postgres Patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

Jpa Patterns

Spring Boot中的JPA/Hibernate模式，用于实体设计、关系处理、查询优化、事务管理、审计、索引、分页和连接池。

資料庫架構師