Workspace Multi-Tenancy

Core Principle

All metadata is stored in SchemaStack's database. Customer databases remain pure.

SchemaStack never writes its own tables, columns, or metadata into customer databases. The customer's database contains only the customer's data — nothing else.

Two-Tier Database Architecture

┌─────────────────────────────────┐
│  SchemaStack Database           │
│  (dynamicdb)                    │
│                                 │
│  - Organisations                │
│  - Workspaces                   │
│  - Views, Columns, Constraints  │
│  - Memberships & Roles          │
│  - Presets, Relationships       │
│  - Database connection configs  │
│  - Audit logs                   │
└─────────────────────────────────┘
          │
          │ metadata describes
          ▼
┌─────────────────────────────────┐
│  Customer Databases             │
│  (one per workspace)            │
│                                 │
│  - Customer's own tables        │
│  - Customer's own data          │
│  - Flyway-managed schemas       │
│  - No SchemaStack metadata      │
└─────────────────────────────────┘

Hierarchy

Organisation
└── Workspace (has database connection config)
    └── View (maps to a database table/entity)
        └── Column (maps to a database column)

Each workspace is connected to one external database. The workspace stores the connection configuration (host, port, database, credentials) in SchemaStack's metadata database. The actual data lives in the customer's database.

Workspace Scoping

All API calls and data access are scoped to a workspace:

• REST API endpoints include the workspace identifier
• The processor creates per-workspace schemas using Flyway
• SSE events are filtered by workspace
• Permissions are evaluated per workspace (and per view within a workspace)

Data Flow

Schema Import

SchemaImportService handles the initial import:

1. Loads workspace and database config from metadata DB (Hibernate Reactive, on event loop)
2. Decrypts connection credentials via EncryptionUtil
3. Switches to worker thread (JDBC is blocking)
4. Connects to customer DB, reads schema via DatabaseMetaData.getTables() and getColumns()
5. Extracts foreign key relationships
6. Returns to event loop, persists Views and ColumnMetadata (merge strategy — preserves existing UUIDs)
7. Generates ViewColumns for new columns via ViewColumnGeneratorService
8. Configures relationship columns for foreign keys
9. Updates stored schema hash

Schema Sync Orchestration

Schema sync is managed by SchemaSyncOrchestrator with a state machine, progress tracking, and checkpoint/resume:

INITIATED → SCHEMA_EXTRACTED → ENTITIES_MERGED → RELATIONSHIPS_MERGED
    → VIEW_COLUMNS_GENERATED → HASH_UPDATED → COMPLETED

Any state can transition to FAILED. Failed operations can be resumed (up to 3 retries) — the orchestrator saves JSON checkpoint data at each step.

Five services in the pipeline:

Service	Responsibility
SchemaSyncOrchestrator	State machine, concurrency prevention, checkpoint/resume
SchemaImportService	JDBC schema extraction, entity persistence
SchemaSyncService	Merge strategy (update existing, add new, remove orphans)
SchemaDriftDetectionService	Compares stored metadata vs live schema (tables/columns added/removed/modified)
SmartSchemaSyncService	Generates default ViewColumns for views without them

Sync types: FULL_SYNC (all tables), INCREMENTAL_SYNC (filtered by table pattern, e.g. customer%), RESET (destructive wipe and re-import).

Concurrency: Only one sync operation per workspace at a time. The SyncOperation entity tracks progress (entitiesExtracted, entitiesMerged, relationshipsMerged, migrationsQueued/completed/failed).

Drift Detection

SchemaDriftDetectionService compares stored metadata against the live database schema:

• Tables added (in DB but not in metadata)
• Tables removed (in metadata but not in DB)
• Tables modified — with per-column detail: columns added/removed, type changes, nullable changes, default value changes, primary key changes, unique constraint changes

Data Access (ViewData Service)

ViewDataService orchestrates the two-database query:

1. Loads View + ViewColumns from metadata DB (Hibernate Reactive)
2. Validates permissions (JWT-based RBAC, workspace membership)
3. If a preset is active, loads preset and applies column overrides (position, hidden, displayName, width)
4. Builds SQL dynamically from ViewColumn metadata via MetadataQueryBuilder
5. Executes the query against the customer DB (Vert.x reactive SQL client — not JDBC)
6. Runs value transformations via ValueTransformService
7. Returns paginated results with count (parallel count + data fetch)

Operations: queryViewData (paginated list), querySingleRow, editCell, editCellsBatch (atomic multi-cell), bulkEdit, fillColumn, insertRow.

Auto-generation: UUID columns (APP_UUID), timestamps (APP_TIMESTAMP_INSERT, APP_TIMESTAMP_UPDATE, APP_TIMESTAMP_BOTH) are auto-populated on insert/update.

Guest access: queryViewDataAsGuest skips workspace membership checks — authorization is via guest token.

Supported Databases

• PostgreSQL
• MySQL

The DatabaseVendor enum only contains POSTGRESQL and MYSQL. SQL Server is not currently supported despite being mentioned in some design docs.

Workspace Access Modes

Mode	Description
Active	Full read/write access to data and schema
Schema Locked	Data read/write, but schema changes blocked
Read Only	Data read only, no writes
Design	Schema editing only, no data access
Maintenance	No access — workspace is being migrated or maintained