[EPIC] Database object model: containers, kinds, and a lazy object tree across all 17 engines

[cevheri]

Four open requests point at one missing abstraction. This issue plans the end-to-end work that answers all of them with a single object model, and tracks it.

Related: #710, #765, discussion 778, #773.

The gap

The schema surface is one flat TableSchema[] with a name: string (src/lib/types.ts:195). It has no namespace level, no object kind, and no lazy boundary. Each of the four requests needs one of those three.

Request	Asks for	Blocked by
#710	Show views and materialised views on PostgreSQL	No object-kind concept
#765	Oracle with 43,512 tables and 910,000 columns freezes the UI on connect	No container level, no lazy loading
discussion 778	Edit procedures and functions	No source text, no DDL apply path
#773	Double-click a table for a data preview	No tree interaction model

Measured current state

Measured on main at ea5730c9.

• name embeds the schema on PostgreSQL when it is not public (postgres.ts:1478), and query-generators.ts:132 splits it back on .. A table literally named a.b in public therefore generates "a"."b".
• PostgreSQL filters table_type IN ('BASE TABLE', 'MATERIALIZED VIEW') (postgres.ts:189). The materialised-view entry exists for Materialize and RisingWave. Stock PostgreSQL does not report materialised views through information_schema.tables at all, so on real PostgreSQL neither views nor materialised views are listed. That is [FEATURE] support views and materialised views for Postgres #710.
• MySQL filters TABLE_TYPE = 'BASE TABLE' in four places.
• Oracle hard-scopes introspection to OWNER = connection.user (oracle.ts:778), so the app shows exactly one schema on Oracle with no way to reach another. PostgreSQL sweeps every non-system schema. The two engines have opposite scoping and the user controls neither.
• Two-phase loading exists in 3 of 17 providers. The other 14 fall back to a full getSchema().
• schemaRefreshPattern triggers a full schema refetch for any editor statement matching CREATE|DROP|ALTER|TRUNCATE (use-query-execution.ts:598).
• docker/postgres-init/02-sample-data.sql already creates four views in schema app. [FEATURE] support views and materialised views for Postgres #710 is reproducible in this repo's own dev environment today.

The model

Providers declare their container levels and object kinds as data on ProviderCapabilities. Core code never reads a kind id or a type id.

• ObjectRole is a closed set the UI derives behaviour from: relation, routine, group, attached, config.
• ObjectKindSpec.id is an open string. DBeaver, CloudBeaver, Azure Data Studio and pgAdmin all reached the same answer independently, and JDBC's closed model is the counter-example: it cannot express a trigger, a sequence, a materialized view or a package at all. A ClickHouse dictionary, a Druid lookup and an Oracle package therefore reach the tree through a provider-local declaration and no core change.
• ContainerLevelSpec gives zero, one or two levels, each carrying the engine's own word. Five engines have no container, seven have one, five have two.
• DatabaseObject.path is a readonly string[], never a joined string, which retires the dot-splitting defect above.
• KindCount is { count } | { unavailable }, so three facts stay separate: the engine has no such concept (kind not declared, no folder drawn), the engine holds none (count: 0, zero badge), the read was refused (the engine's own sentence).

Four provider methods replace getSchema / getSchemaList / getSchemaRelations:

listContainers(parent?)            countObjects(container)
listObjects(container, kind)       describeObject(path)

Plus a bulk inventory surface for the agent, the diagram and schema diff, which reports its own truncation rather than handing a slice over as a whole inventory.

Decisions

Decision	Choice
Scope	One model, three phases
Tree shape	Full tree: containers, kind folders, objects, lazy per node
First paint	Container list plus the active container expanded with kind counts
Escape hatch	Connection-level skipObjectScan plus an explicit load action, which is #765's reporter's own stated preference
Migration	One pull request: DatabaseObject replaces TableSchema, getSchema() removed, major version
Acceptance	All 17 type-ids verified against a live instance before merge
DDL authority	The database is the boundary, not the UI role. A user can already run CREATE OR REPLACE in the query editor.

Phases

Phase 1, this pull request. The model, the tree, the migration. Closes #710 and #765, and makes #773 cheap.

Phase 2. Source reading. hasSource gates a Source tab; sourceLanguage picks the Monaco language. An unreadable source shows the engine's own sentence and never an empty editor, because an empty editor reads as "no source" and a user who types over it deletes the object.

Phase 3. Editing and DDL apply, which answers discussion 778. In-place editing behind a mandatory preview. The provider builds the statement, core never does. MySQL 8.4 has no CREATE OR REPLACE PROCEDURE and its DDL implicitly commits, so its strategy is DROP then CREATE with the current source retained for restore; MariaDB 11.x has CREATE OR REPLACE and uses it. Oracle's CREATE OR REPLACE can return success and leave the object INVALID without node-oracledb throwing, so result.warning is checked and USER_ERRORS supplies the line and position.

Acceptance for Phase 1

1. Every type-id has a fixture containing one instance of every kind its provider declares. PostgreSQL already has four views; sixteen fixtures are new.
2. An end-to-end spec walks the tree per engine and asserts each declared kind's folder and count against the fixture.
3. An end-to-end spec asserts the three absences render differently.
4. [FEATURE] support views and materialised views for Postgres #710 closes on evidence: the four seeded PostgreSQL views appear in the tree.
5. [BUG] Oracle: eager full-owner getSchema() on connect freezes the UI on large schemas (no lazy/paged introspection) #765 closes on evidence: a large Oracle owner paints without freezing, measured, and skipObjectScan issues zero catalog queries.
6. Coverage stays at 100 percent lines.
7. docs/providers/<type-id>.md and tests/integration/db/<type-id>-provider.test.ts sync for all 17, per the triad rule.

Out of scope

Debugging PL/SQL, object-level version control, creating objects from a form, cross-database references, and #773's data preview, which becomes cheap once the tree exists but is its own change.

Design and plan

The full design and the 28-task implementation plan were written before any code and live in the working tree under docs/superpowers/, which is git-ignored. Their substance is reproduced above; ask if you want either posted in full.