Skip to content

0015-external-datasource-federation.md

Latest commit

 

History

History
867 lines (697 loc) · 36.4 KB

0015-external-datasource-federation.md

File metadata and controls

867 lines (697 loc) · 36.4 KB
Raw

ADR-0015: External Datasource Federation

Status: Proposed (2026-05-29) Deciders: ObjectStack Protocol Architects Builds on: ADR-0005 (one Zod source of truth per metadata type) Consumers: @objectstack/spec, @objectstack/runtime, @objectstack/objectql, @objectstack/plugins/driver-sql, @objectstack/services/service-ai, @objectstack/cli, ../objectui

0. Context

ObjectStack today owns its own database: the default datasource is a managed Postgres/SQLite where the framework freely runs DDL (createTable, alterTable, migrations from ISchemaDiffService). The protocol already includes:

  • Datasource (packages/spec/src/data/datasource.zod.ts) — connection
    • driver + capabilities (transactions, queryFilters, readOnly, …).
  • Object.datasource (packages/spec/src/data/object.zod.ts:432) — per-object routing key, defaulting to 'default'.
  • ISchemaDiffService (packages/spec/src/contracts/schema-diff-service.ts) — introspect → diff → migration plan.
  • ExternalLookup (packages/spec/src/data/external-lookup.zod.ts) — per-field REST/OData/GraphQL live lookup with caching, retry, JSONPath extraction. Not a table-level federation primitive.
  • DataSync (packages/spec/src/automation/sync.zod.ts) — batch/incremental replication between systems via connector instances.
  • AI surface: IDataEngine-backed query_data tool, SchemaRetriever, data-chat-agent, metadata-assistant-agent — all consume objects uniformly through IMetadataService + ObjectQL.

What is missing for the "plug ObjectStack into a mature warehouse and let the AI chat with it" use case:

  1. Ownership semantics. Nothing in Datasource distinguishes "ObjectStack owns this schema" from "this is somebody else's production database — never touch DDL." The capabilities.readOnly flag only hints at query pushdown; it does not gate migrations.
  2. Boot-time validation. No mechanism verifies that an externally bound object matches the remote table; declaring a wrong column type silently produces runtime errors at query time.
  3. Reverse introspection workflow. IDataEngine drivers can introspectSchema(), but there is no CLI / UI / service that turns that into an ObjectStack Object draft.
  4. Catalog persistence. Repeated introspection is expensive; we have no metadata type to cache the remote schema snapshot and surface drift.
  5. Write safety. Writes through external datasources are neither blocked nor explicitly opted into.
  6. AI prompt awareness. SchemaRetriever does not tell the LLM that a given object is external/read-only, so the model may attempt schema-mutating tool calls or generate unsafe writes.

This ADR proposes a single, layered solution that closes all six gaps without introducing a new metadata type for federated objects — keeping ADR-0005's "one Zod source per type" invariant intact.

1. Decision

Add External Datasource Federation as a first-class capability of the existing Datasource and Object protocols, governed by three runtime gates and supported by one new metadata type (external_catalog) plus one new service contract (IExternalDatasourceService).

A federated object is still an Object. Its remote-ness is expressed by:

  • the datasource it points to (schemaMode !== 'managed'), and
  • an optional object.external sub-record describing the remote binding (table name, schema, column map, writability).

All downstream consumers (views, ObjectQL, REST, AI tools, permissions, audit) continue to see a normal Object. Differences in behaviour are enforced inside the runtime gates and the driver layer.

2. Goals and Non-Goals

2.1 Goals

  • Configure mature external databases (Postgres, MySQL, Snowflake, BigQuery, MongoDB, …) through CLI and Studio UI.
  • Register existing remote tables as ObjectStack objects without touching the remote schema.
  • Fail-fast at registration / boot time when an object definition diverges from the remote table; emit an actionable diff.
  • Make all existing capabilities work transparently for federated objects: ObjectQL pushdown, REST/GraphQL auto-routes, Views, Dashboards, Reports, AI Data Chat, AI report agents, RBAC, audit, realtime (where the driver supports it).
  • Default to read-only; require two explicit opt-ins (datasource level + object level) to enable writes.
  • Coexist with ExternalLookup (per-field HTTP) and DataSync (batch replication) without overlap or ambiguity.

2.2 Non-Goals (v1)

  • Cross-datasource distributed transactions.
  • Reverse DDL push (ObjectStack will never alter the remote schema).
  • Replacing dedicated ETL — heavy batch ingestion remains the job of DataSync + connector plugins.
  • Multi-statement stored-procedure invocation through ObjectQL.
  • Cross-datasource joins (documented as a future workstream; users should mirror dimension tables via DataSync for now).

3. Conceptual Model

┌──────────────────────────────────────────────────────────────────────┐
│                        ObjectStack Environment                       │
│                                                                      │
│   ┌─────────────┐      ┌──────────────────┐      ┌───────────────┐   │
│   │ Datasource  │◀────▶│ ExternalCatalog  │◀────▶│ Federated     │   │
│   │ schemaMode= │      │ (remote schema   │      │ Object        │   │
│   │ 'external'  │      │  snapshot+drift) │      │ external: {…} │   │
│   └─────────────┘      └──────────────────┘      └───────────────┘   │
│         │                                                │           │
│         │ routed via Object.datasource                   │           │
│         ▼                                                ▼           │
│   ┌──────────────────────┐                  ┌──────────────────────┐ │
│   │ Mature External DB   │  ◀── ObjectQL ──│ AI / Views / REST    │ │
│   │ (Postgres, Snowflake │      pushdown    │ unchanged consumers  │ │
│   │  MySQL, BigQuery…)   │                  │                      │ │
│   └──────────────────────┘                  └──────────────────────┘ │
└──────────────────────────────────────────────────────────────────────┘

Three new protocol artefacts:

Artefact Kind Purpose
Datasource.schemaMode new field on existing Declare ownership (managed / external / validate-only).
Object.external new field on existing Per-object remote binding (table name, schema, writability, column overrides).
ExternalCatalog new metadata type Cached remote schema snapshot + last validation diff, surfaced in Studio.

One new service contract: IExternalDatasourceService (introspect, generate draft, refresh catalog, validate).

3.1 Why no external_object metadata type?

Introducing a parallel external_object Zod schema would force every downstream consumer (views, ObjectQL planner, permissions, AI tools, REST generator) to special-case two object shapes — violating ADR-0005's "one Zod source per metadata type" invariant and roughly doubling the protocol surface.

Instead, externality is a property of the binding, not a new metadata kind. A federated object is still an object in DEFAULT_METADATA_TYPE_REGISTRY; the difference is encoded by the datasource it points to and an optional external sub-record.

4. Spec Layer Changes

All schemas live in packages/spec/src/. Each change is additive and backward-compatible (defaults preserve current behaviour).

4.1 data/datasource.zod.ts

export const SchemaModeSchema = z.enum([
  'managed',        // ObjectStack owns this schema: DDL + migrations allowed.
  'external',       // Mature external DB: DDL forbidden; mismatch fails boot.
  'validate-only',  // Like external, but mismatches warn instead of fail.
]).describe('Schema ownership mode');

export const ExternalDatasourceSettingsSchema = z.object({
  label: z.string().optional()
    .describe('Display label, e.g. "Snowflake — ANALYTICS / PROD"'),
  allowedSchemas: z.array(z.string()).optional()
    .describe('Whitelist of remote schemas/databases that may be exposed.'),
  allowWrites: z.boolean().default(false)
    .describe('Global write gate. Individual objects must also opt in.'),
  validation: z.object({
    onMismatch: z.enum(['fail', 'warn', 'ignore']).default('fail'),
    checkOnBoot: z.boolean().default(true),
    checkIntervalMs: z.number().optional()
      .describe('Optional background drift-check interval.'),
  }).default({}),
  credentialsRef: z.string().optional()
    .describe('Reference into the secrets store; never inline credentials.'),
  queryTimeoutMs: z.number().default(30_000)
    .describe('Hard cap on per-query execution time.'),
});

export const DatasourceSchema = lazySchema(() => z.object({
  // …existing fields…
  schemaMode: SchemaModeSchema.default('managed'),
  external: ExternalDatasourceSettingsSchema.optional(),
}).superRefine((ds, ctx) => {
  if (ds.schemaMode !== 'managed' && !ds.external) {
    ctx.addIssue({
      code: 'custom',
      path: ['external'],
      message: `schemaMode='${ds.schemaMode}' requires 'external' settings.`,
    });
  }
  if (ds.schemaMode === 'managed' && ds.external) {
    ctx.addIssue({
      code: 'custom',
      path: ['external'],
      message: `'external' settings only apply when schemaMode != 'managed'.`,
    });
  }
}));

4.2 data/object.zod.ts

export const ObjectExternalBindingSchema = z.object({
  remoteName: z.string().optional()
    .describe('Remote table/view name. Defaults to object.name.'),
  remoteSchema: z.string().optional()
    .describe('Remote schema/database qualifier.'),
  writable: z.boolean().default(false)
    .describe('Per-object write opt-in (also requires datasource.external.allowWrites).'),
  columnMap: z.record(z.string(), z.string()).optional()
    .describe('Remote column name → local field name.'),
  introspectedAt: z.string().datetime().optional()
    .describe('Set by `os datasource introspect`; informational.'),
  ignoreColumns: z.array(z.string()).optional()
    .describe('Remote columns to skip during validation (dev convenience).'),
});

export const ObjectSchema = lazySchema(() => z.object({
  // …existing fields, including:
  // datasource: z.string().optional().default('default'),
  external: ObjectExternalBindingSchema.optional(),
}));

The cross-field invariant ("object.external only when its datasource has schemaMode !== 'managed'") is enforced at metadata-load time, not in the Zod schema, because the datasource may live in another artefact.

4.3 data/external-catalog.zod.ts (new)

export const ExternalColumnSchema = z.object({
  name: z.string(),
  sqlType: z.string(),
  nullable: z.boolean(),
  primaryKey: z.boolean().default(false),
  suggestedFieldType: z.string().optional(),
});

export const ExternalTableSchema = z.object({
  remoteSchema: z.string().optional(),
  remoteName: z.string(),
  columns: z.array(ExternalColumnSchema),
  indexes: z.array(z.object({
    name: z.string(),
    columns: z.array(z.string()),
    unique: z.boolean(),
  })).optional(),
  rowCountEstimate: z.number().optional(),
});

export const ExternalCatalogSchema = lazySchema(() => z.object({
  name: z.string().regex(/^[a-z_][a-z0-9_]*$/)
    .describe('Catalog id, conventionally `<datasource>_catalog`.'),
  datasource: z.string()
    .describe('Datasource.name this catalog snapshots.'),
  snapshotAt: z.string().datetime(),
  tables: z.array(ExternalTableSchema),
}));

Register in DEFAULT_METADATA_TYPE_REGISTRY with:

{ name: 'external_catalog', allowOrgOverride: false, schema: ExternalCatalogSchema }

4.4 shared/external-errors.ts (new)

export type SchemaDiffEntryKind =
  | 'missing_table' | 'missing_column' | 'type_mismatch'
  | 'nullability_mismatch' | 'unmapped_column' | 'pk_mismatch';

export interface SchemaDiffEntry {
  kind: SchemaDiffEntryKind;
  remoteSchema?: string;
  remoteName?: string;
  column?: string;
  expected?: string;
  actual?: string;
  severity: 'error' | 'warning';
}

export class ExternalSchemaMismatchError extends Error {
  readonly code = 'EXTERNAL_SCHEMA_MISMATCH';
  constructor(
    readonly datasource: string,
    readonly object: string,
    readonly diffs: SchemaDiffEntry[],
  ) { super(renderDiffMessage(datasource, object, diffs)); }
}

export class ExternalWriteForbiddenError extends Error {
  readonly code = 'EXTERNAL_WRITE_FORBIDDEN';
}

export class ExternalSchemaModeViolationError extends Error {
  readonly code = 'EXTERNAL_SCHEMA_MODE_VIOLATION';
}

4.5 contracts/external-datasource-service.ts (new)

export interface RemoteTable {
  schema?: string;
  name: string;
  columnCount: number;
  rowCountEstimate?: number;
}

export interface GenerateDraftOpts {
  remoteSchema?: string;
  rename?: Record<string, string>;   // remote col → field name
  primaryKey?: string[];             // override PK detection
  includeColumns?: string[];
  excludeColumns?: string[];
}

export interface SchemaValidationResult {
  ok: boolean;
  datasource: string;
  object: string;
  diffs: SchemaDiffEntry[];
}

export interface SchemaValidationReport {
  ok: boolean;
  results: SchemaValidationResult[];
}

export interface IExternalDatasourceService {
  listRemoteTables(datasource: string, opts?: { schema?: string }): Promise<RemoteTable[]>;
  generateObjectDraft(datasource: string, remoteName: string, opts?: GenerateDraftOpts): Promise<unknown>;
  refreshCatalog(datasource: string): Promise<ExternalCatalog>;
  validateObject(objectName: string): Promise<SchemaValidationResult>;
  validateAll(): Promise<SchemaValidationReport>;
}

4.6 Type compatibility matrix

A pure module packages/spec/src/data/type-compat.ts maps SQL types (text, varchar, numeric(10,2), timestamptz, jsonb, …) to ObjectStack field types, and exposes isCompatible(remoteSqlType, fieldType): boolean | 'lossy'. The matrix is dialect-aware (postgres, mysql, sqlite, snowflake, bigquery, mongo). New dialects extend the matrix; the matrix is unit-tested independently.

5. Runtime Gates

Three gates enforce the ownership semantics. Each gate is a single choke-point so the rules are auditable and cannot be bypassed by accident.

5.1 Gate 1 — DDL gate (driver layer)

Where: every driver method that mutates schema (createTable, alterTable, dropTable, plus the ISchemaDiffService.applyMigrations implementation).

Behaviour:

if (datasource.schemaMode !== 'managed') {
  throw new ExternalSchemaModeViolationError(
    `DDL is forbidden on datasource '${datasource.name}' (schemaMode=${datasource.schemaMode}).`,
  );
}

Concretely — packages/plugins/driver-sql/src/sql-driver.ts:1064 and :1084 (current createTable / alterTable call sites) gain a guard at the top. The applyMigrations implementation (forthcoming in service-migration per ADR-0008) also calls this guard.

5.2 Gate 2 — Boot validation (runtime layer)

Where: a new external-validation-plugin registered in @objectstack/runtime, subscribed to kernel:ready.

Behaviour:

const report = await externalDatasourceService.validateAll();
for (const r of report.results.filter(r => !r.ok)) {
  const ds = datasources.get(r.datasource)!;
  const mode = ds.external?.validation.onMismatch ?? 'fail';
  if (mode === 'fail') throw new ExternalSchemaMismatchError(r.datasource, r.object, r.diffs);
  if (mode === 'warn') logger.warn({ diffs: r.diffs }, 'external schema drift');
}

The validator walks every object whose datasource has schemaMode !== 'managed', runs IExternalDatasourceService.validateObject, and aggregates the report. Catalog snapshots are reused across validations within a boot to avoid duplicate introspection.

Background drift checks: if checkIntervalMs is set, the plugin also schedules a periodic re-validation that emits external.schema.drift events on the kernel event bus (consumed by audit and notification services).

5.3 Gate 3 — Write gate (data engine layer)

Where: IDataEngine.insert/update/delete (in packages/objectql/src/engine.ts).

Behaviour:

if (datasource.schemaMode !== 'managed') {
  const dsAllows = datasource.external?.allowWrites ?? false;
  const objAllows = object.external?.writable ?? false;
  if (!(dsAllows && objAllows)) {
    throw new ExternalWriteForbiddenError(
      `Writes blocked: datasource.external.allowWrites=${dsAllows}, object.external.writable=${objAllows}.`,
    );
  }
}

The double opt-in (datasource and object) is intentional: an administrator enabling writes on the warehouse must still annotate each writable object explicitly.

5.4 Failure surface

Gate Error Where surfaced
DDL ExternalSchemaModeViolationError Migration runner, dev pnpm dev startup, CI os migrate plan.
Boot validation ExternalSchemaMismatchError kernel:ready → process exits (mode=fail) or logs (mode=warn).
Write ExternalWriteForbiddenError REST 403, ObjectQL mutation, AI tool error, returned to caller.

All three error classes carry a stable code field and are mapped to the shared error envelope in packages/spec/src/shared/error-map.ts.

6. Service Layer

6.1 New service: @objectstack/services/service-external-datasource

Implements IExternalDatasourceService on top of the existing driver introspectSchema() contract:

  • listRemoteTables(ds) → driver.introspectSchema() filtered by datasource.external.allowedSchemas.
  • generateObjectDraft(ds, table, opts) → renders a TypeScript defineObject({ … }) source using the type-compat matrix; the draft carries an // REVIEW: comment on every column whose mapping is 'lossy'.
  • refreshCatalog(ds) → builds an ExternalCatalog record and writes it through IMetadataService.upsert('external_catalog', …).
  • validateObject(name) → loads the latest catalog (or refreshes if stale) and diffs against the object's field list using the type-compat matrix.
  • validateAll() → fans out across all federated objects, parallelised per datasource.

The service is registered as a kernel service so plugins, the CLI, the runtime plugin, and the REST layer can all consume it through DI.

6.2 REST routes

Added under /api/v1/datasources/:name/external/...:

GET    /tables                       → listRemoteTables
POST   /tables/:remote/draft         → generateObjectDraft (returns TS source)
POST   /refresh-catalog              → refreshCatalog
POST   /validate                     → validateAll for this datasource
GET    /catalog                      → latest ExternalCatalog

All routes require the metadata:write permission; reads require metadata:read. The Studio UI consumes these.

6.3 CLI commands (packages/cli)

os datasource add        --name --driver --schema-mode --url [--credentials-ref]
os datasource list       [--mode external]
os datasource list-tables <name> [--schema]
os datasource introspect <name> --table <remote> [--out path] [--rename a=b ...]
os datasource refresh    <name>
os datasource validate   [<name> | --all]
os datasource remove     <name>

os datasource introspect is the canonical onboarding flow: it prints the generated *.object.ts to stdout (or --out), already populated with datasource: and external: { remoteSchema, remoteName }.

6.4 Studio UI (../objectui)

A new "External Data Sources" surface, served by the routes above:

  1. Connection wizard — driver picker, credential entry (writes to secrets store, never to git), live "Test connection" button.
  2. Schema browser — tree of catalog → schema → table → column with row-count estimates; "Import as Object" action per table.
  3. Mapping editor — side-by-side remote columns vs. object fields with rename / type-override controls; preview pane shows the resulting *.object.ts.
  4. Validation panel — green/yellow/red status per object with the diff list; "Refresh catalog" and "Re-validate" buttons.
  5. Drift inbox — surfaces external.schema.drift events from the background checker.

The UI lives in the sibling ../objectui repo per AGENTS.md; the backend serves only the data plane.

7. AI Integration (the "free lunch")

The decisive design choice — keeping a federated object as a normal Object — means the entire AI surface works with one line of prompt-side awareness and zero changes to tools, agents, or conversation services.

7.1 What just works

Capability Why no changes needed
query_data AI tool Resolves the object via IMetadataService and runs ObjectQL through IDataEngine; the engine routes by object.datasource.
SchemaRetriever schema injection Iterates over all registered objects regardless of datasource.
data-chat-agent / metadata-assistant-agent Consume IDataEngine + IMetadataService only.
Reports, dashboards, charts Defined against objects, not against tables.
ObjectQL filter / sort / aggregation pushdown Driver capabilities already drive pushdown decisions.
REST /api/v1/data/<object> auto-routes Generated from the object registry.
RBAC / row-level security plugin-security applies the same policy graph; predicates are pushed into WHERE.

7.2 The one prompt-side change

SchemaRetriever.renderSnippet() annotates each object with its ownership and writability so the LLM does not propose unsafe actions:

### wh_order — Warehouse Order  [external, read-only, datasource=warehouse]
  - order_id: text (pk)
  - customer_id: text
  - amount: number
  - ordered_at: datetime

data-chat-agent and metadata-assistant-agent system prompts gain a short rule:

Objects marked [external, read-only] come from a customer's production database. You must not propose schema changes, writes, or destructive operations on them. Always include sensible limit and time-range filters when querying them.

7.3 Query safety nets for AI-generated workloads

To prevent a chatty LLM from melting a production warehouse:

  • Default LIMIT injection. query_data wraps every AI-generated ObjectQL query with a limit: 1000 unless the user explicitly asks for more.
  • Per-datasource timeout. Datasource.external.queryTimeoutMs (default 30 s) is enforced inside the driver; the AI tool returns a truncated-with-hint result on timeout.
  • Read replica convention. Documentation strongly recommends pointing the datasource at a read replica; this is policy, not code.
  • Audit trail. Every ObjectQL execution against an external datasource emits an external.query.executed event (object, SQL, rows, ms, actor) consumed by audit.

8. End-to-End Example

// datasources/warehouse.datasource.ts
export default defineDatasource({
  name: 'warehouse',
  driver: 'postgres',
  schemaMode: 'external',
  config: { connectionString: process.env.WAREHOUSE_URL },
  external: {
    label: 'Analytics Warehouse (read replica)',
    allowedSchemas: ['public', 'mart'],
    allowWrites: false,
    validation: { onMismatch: 'fail', checkOnBoot: true },
    queryTimeoutMs: 15_000,
    credentialsRef: 'secret:warehouse/readonly',
  },
});
os datasource list-tables warehouse --schema mart
#  mart.fact_orders         (14 cols, ~12.4M rows)
#  mart.dim_customer        ( 9 cols, ~  280k rows)
#  mart.dim_product         (11 cols, ~   42k rows)

os datasource introspect warehouse --table fact_orders --out objects/wh_order.object.ts
// objects/wh_order.object.ts (generated, then human-reviewed)
export default defineObject({
  name: 'wh_order',
  label: 'Warehouse Order',
  datasource: 'warehouse',
  external: { remoteSchema: 'mart', remoteName: 'fact_orders' },
  fields: [
    { name: 'order_id',    type: 'text',     primaryKey: true },
    { name: 'customer_id', type: 'text' },
    { name: 'amount',      type: 'number' },
    { name: 'ordered_at',  type: 'datetime' },
    // REVIEW: remote column 'metadata jsonb' mapped lossy to 'json'
    { name: 'metadata',    type: 'json' },
  ],
});
os datasource validate warehouse
# ✓ wh_order matches warehouse.mart.fact_orders (5/14 mapped, 9 unmapped — ok)

pnpm dev:crm -- --fresh -p 38421
# [info] external-validation: 1 datasource, 1 object, 0 mismatches

User in Studio's Data Chat: "哪些客户过去 7 天下单金额最高？前 10。"

The data-chat-agent generates ObjectQL:

{
  object: 'wh_order',
  select: ['customer_id', { agg: 'sum', field: 'amount', as: 'total' }],
  where: { ordered_at: { $gte: '7d ago' } },
  groupBy: ['customer_id'],
  orderBy: [{ field: 'total', dir: 'desc' }],
  limit: 10,
}

IDataEngine routes to the warehouse driver, which pushes the entire query down as a single SELECT … GROUP BY … ORDER BY … LIMIT 10 into Postgres. The LLM summarises the result; the audit log records the SQL, the actor, and the 412 ms execution time.

9. Coexistence Matrix

The three "external data" primitives serve different needs and may all appear in the same environment without conflict:

Need Use
Live federation of remote tables, AI/ad-hoc query This ADR — Datasource(schemaMode=external) + Object.
Per-field live HTTP/OData/GraphQL lookup ExternalLookup on a field.
Batch / incremental replication into a local table DataSync + connector instance.

Example: a single environment can federate a Snowflake warehouse for analytics, use ExternalLookup to enrich CRM records from a SaaS HTTP API, and DataSync-mirror a slowly-changing dimension into the managed Postgres for low-latency joins.

10. Security, Operations, Multitenancy

  • Credentials never appear in metadata artefacts. credentialsRef points at service-secrets; the secrets service is the only component that resolves the actual connection string.
  • Connection pools default conservatively for external mode (max: 5) to protect the remote system; pool may be overridden.
  • Timeouts are enforced at the driver. AI tool calls additionally wrap a Promise.race(timeout) and report a graceful error to the LLM.
  • Audit: driver-sql already emits query.executed; service-audit filters by datasource.schemaMode === 'external' to produce a dedicated external-access log.
  • Tenant scope: a Datasource is environment-scoped (per ADR-0006 v4). It is not organisation-overlay-customisable; admins of one environment cannot see another environment's external datasources.
  • Permissions: ACLs apply unchanged. A new optional convenience — Datasource.external.requirePermission?: string — lets admins gate the entire datasource behind a single role without writing per-object ACLs.
  • Realtime: where the underlying driver supports CDC (e.g. Postgres logical decoding), the realtime service may opt-in; otherwise federated objects fall back to poll-based change feeds. This is out of scope for v1.

11. Rollout Plan

Each phase is independently shippable and testable.

Phase Scope Estimate
P1 Spec changes (schemaMode, object.external, error classes) + DDL gate in driver-sql + tests. 1–2 d
P2 IExternalDatasourceService implementation + type-compat matrix + CLI introspect / validate. 2–3 d
P3 Boot validation plugin in @objectstack/runtime + external_catalog metadata type + caching. 1 d
P4 SchemaRetriever annotation + agent prompt update + AI safety nets (LIMIT injection, timeout). 0.5 d
P5 Studio UI in ../objectui (wizard, schema browser, mapping editor, validation panel). ~1 w
P6 Write gate + allowWrites/writable double opt-in + tests. 1 d
P7 Additional drivers (Snowflake / BigQuery / MySQL adapters) — sized per driver. 2–4 d ea

MVP = P1–P4: after MVP, a user can connect a read-only Postgres replica, register a few tables, and let the AI Data Chat query them safely. P5 makes it a product; P6 unlocks writes; P7 expands the driver matrix.

12. Risks & Mitigations

Risk Mitigation
Remote schema evolves, breaking federated objects checkIntervalMs background validator; external.schema.drift events into audit + notification.
AI generates a runaway query against production Default limit: 1000, per-datasource queryTimeoutMs, recommend read replica, audit every query.
Type mapping is imperfect across dialects Type-compat matrix is an explicit, unit-tested module; generated drafts carry // REVIEW: markers.
Introspection of huge schemas is slow Snapshot persisted as external_catalog; refresh is explicit (os datasource refresh) or scheduled.
User expects cross-datasource joins Documented limit; recommend DataSync to mirror dimension tables locally; revisit federation in vNext.
Misconfigured schemaMode='managed' against a real warehouse Connection wizard defaults to external for any non-default datasource; UI surfaces a red banner.
Plugin author bypasses the DDL gate by calling knex directly sql-driver wraps knex; direct knex access is a documented private API. Lint rule in P1 enforces it.

13. Alternatives Considered

13.1 New external_object metadata type

Rejected. Doubles the protocol surface; every downstream consumer would need to handle two object shapes; violates ADR-0005. The object.external sub-record gives identical expressiveness with strictly less surface.

13.2 Reuse ExternalLookup for tables

Rejected. ExternalLookup is field-scoped and HTTP-shaped. Table federation needs driver-level connection pooling, query pushdown, typed schema, and ObjectQL planner integration — all things ExternalLookup deliberately omits.

13.3 Always materialise via DataSync

Rejected as the only option. Materialisation defeats the "live analytics on a mature warehouse" use case and costs storage we do not own. DataSync remains the right tool for some tables (small dimensions, offline analysis); federation is the right tool for others (huge fact tables, real-time freshness). They coexist.

13.4 Boolean readOnly on Datasource

Rejected. Conflates two orthogonal concerns: query-side pushdown hints (already covered by capabilities.readOnly) and ownership / DDL gating (introduced here). The enum schemaMode leaves room for a future 'replica' mode without re-shaping the schema.

13.5 Validate lazily (on first query) instead of at boot

Rejected. Late failures surface deep inside the AI tool call, far from the misconfigured artefact, and only when the schema in question is exercised. Boot-time fail-fast matches the rest of the framework's ergonomics (manifest validation, plugin registration, settings validation).

14. Open Questions

  • Cross-environment sharing. Can two environments register the same physical datasource? (Tentative answer: yes, each environment has its own Datasource artefact; the secrets/credentials may coincidentally point to the same DB. Out of scope to enforce exclusivity.)
  • Schema-evolution autofix. Should os datasource validate --autofix rewrite the local *.object.ts to match remote drift? (Tentative: yes, behind explicit flag, for dev only.)
  • Granular write policy. Beyond per-object writable: true, do we need per-field write masks? Defer until a user need surfaces.
  • Realtime / CDC integration. Out of scope for v1; tracked as a follow-up ADR.

15. Consequences

Positive

  • Customers can point ObjectStack at an existing warehouse in minutes and immediately get AI-powered query, charting, and reporting on it.
  • The protocol gains a clear, enforceable boundary between "we own this database" and "we are a guest in this database."
  • Zero-touch reuse of every existing AI capability (query_data, agents, retrievers).
  • One new metadata type, three new fields, three runtime gates — the blast radius is small.

Negative

  • New service (service-external-datasource) and new CLI surface to maintain.
  • Per-driver introspection quality varies; some dialects need significant matrix work.
  • Studio UI surface grows non-trivially in ../objectui.

Neutral

  • ExternalLookup and DataSync are unchanged but gain a sibling; documentation must clarify which to use when.

16. Acceptance Criteria

The ADR is considered "delivered" when:

  1. pnpm test passes with new unit tests for: Zod refinements on schemaMode, the DDL gate, the boot validator, the write gate, and the type-compat matrix.
  2. os datasource introspect produces a *.object.ts for a real Postgres schema and os datasource validate reports ok: true.
  3. Mutating any column type in the object file and re-running pnpm dev:crm fails with an ExternalSchemaMismatchError printing the diff.
  4. With a federated object loaded, the data-chat-agent answers a natural-language question and the audit log records the pushed-down SQL, the actor, and the execution time.
  5. Studio shows the datasource, its catalog, and validation status (P5 milestone).

17. References

  • ADR-0003 — Package as first-class citizen.
  • ADR-0005 — Metadata customization overlay (one Zod source per type).
  • ADR-0006 v4 — Project / environment split (environment scoping).
  • ADR-0008 — Metadata repository and change log.
  • packages/spec/src/data/datasource.zod.ts
  • packages/spec/src/data/object.zod.ts
  • packages/spec/src/data/external-lookup.zod.ts
  • packages/spec/src/automation/sync.zod.ts
  • packages/spec/src/contracts/schema-diff-service.ts
  • packages/plugins/driver-sql/src/sql-driver.ts (introspectSchema, createTable, alterTable)
  • packages/services/service-ai/src/tools/query-data.tool.ts
  • packages/services/service-ai/src/schema-retriever.ts