Savvi Studio

Phase 2 DB Hard Cutover Delta Inventory

Status: PASS (Phase 2 verification gate for current hard-cutover scope) Last updated: 2026-03-29

Purpose

This artifact starts Phase 2 from the locked plan semantics and records exactly where current SQL still relies on pre-cutover fields/constraints.

Plan anchors (from /memories/session/plan.md):

  1. Update graph schema/migrations to canonical fields and constraints.
  2. Update statement predicate denormalization and indexes.
  3. Update SQL functions/views that rely on old semantics.
  4. Update SQL comments/contracts to match formal semantics.

Canonical Target Semantics (Phase 1 freeze)

  1. kind: primary operational classifier.
  2. path: canonical fully-qualified identity path.
  3. external_id: optional integration key only.
  4. Semantic typing remains statement-based and must not be overloaded into classifier/path fields.

Current Baseline Touchpoints (2026-03-29)

Inventory command:

  • rg -n "type_namespace|type_name|predicate\s+LTREE|unique_resource|unique_statement|external_id" db/migrations/20251207_001_initial_schema/301_graph_resource.sql db/migrations/20251207_001_initial_schema/302_graph_statement.sql db/migrations/20251207_001_initial_schema/303_graph_functions.sql db/migrations/20251207_001_initial_schema/307_graph_permissions.sql db/migrations/20251207_001_initial_schema/308_graph_permission_functions.sql db/migrations/20251207_001_initial_schema/309_graph_rls_policies.sql db/migrations/20251207_001_initial_schema/310_graph_workspace_view.sql db/migrations/20251207_001_initial_schema/311_graph_query.sql

Observed high-impact surfaces:

  1. 301_graph_resource.sql
  • Resource identity currently centers on type_namespace and external_id.
  • Uniqueness is currently UNIQUE (type_namespace, external_id).
  1. 302_graph_statement.sql
  • Statement denormalized predicate uses predicate LTREE sourced from predicate_id -> resource.type_namespace.
  • Triple uniqueness is UNIQUE (subject_id, predicate_id, object_id).
  1. 303_graph_functions.sql
  • Core CRUD functions (create_resource, get_resource_by_external_id, filters) are parameterized around p_type_namespace and p_external_id.
  1. 307_graph_permissions.sql and 308_graph_permission_functions.sql
  • Permission contracts use subject_type_namespace and resource_type_namespace throughout table shape, indexes, and function signatures.
  1. 309_graph_rls_policies.sql, 310_graph_workspace_view.sql, and 311_graph_query.sql
  • RLS, workspace views, and query filters all include type_namespace and/or external_id semantics directly.

Phase 2 Delta Checklist

  1. Resource table and constraints
  • Define canonical kind + path column model for graph.resource.
  • Define canonical uniqueness constraints for path and integration-key behavior for external_id.
  • Define index strategy updates for new hot-path access patterns.
  1. Statement and predicate denormalization
  • Re-specify denormalized predicate field and trigger behavior against canonical semantics.
  • Re-validate statement uniqueness/index strategy under cutover semantics.
  1. SQL function/view contract updates
  • Replace/bridge p_type_namespace and related signatures in core graph functions.
  • Replace/bridge *_type_namespace signatures in permissions functions.
  • Update workspace/query helper contracts to canonical filter semantics.
  1. Documentation/comment contract alignment
  • Update SQL comments to canonical term set (kind, path, statement-based typing).
  • Ensure no SQL comments imply mixed classifier/semantic-path meaning.

Open Questions (to resolve before migration edits)

  1. Compatibility window shape
  • Decision: no compatibility window.
  • Resolution: perform in-place replacement cutover for schema columns; do not keep legacy alias columns or synchronization triggers. Migrations use strict operations (no fallback/conditional branches) so schema drift fails fast.
  1. Predicate denormalization target
  • Decision: keep denormalized statement predicate as dedicated predicate path.
  • Resolution: statement predicate denormalization remains path-oriented for query behavior; operational classifier concerns remain in kind and are not overloaded into statement predicate storage.
  1. Permission cache dimensionality
  • Decision: replace namespace columns directly.
  • Resolution: rename/replace *_type_namespace columns to *_kind during migration and update dependent SQL contracts.

Decisions Locked (2026-03-29)

  1. Compatibility mode: hard cutover (in-place replacement, no legacy alias columns).
  2. Predicate denormalization: dedicated predicate path.
  3. Permission cache: direct *_kind replacement schema.

Migration Progress

  1. Applied hard-cutover migration file:
  • db/migrations/20260329_008_resource_kind_path_phase2_cutover.sql
  1. Applied hard-cutover migration file:
  • db/migrations/20260329_009_statement_permission_phase2_cutover.sql
  1. Applied hard-cutover migration file:
  • db/migrations/20260329_010_sql_contract_kind_path_cutover.sql
  1. Contents:
  • Replaces graph.resource.type_namespace with graph.resource.kind (in-place rename).
  • Adds canonical graph.resource.path and enforces NOT NULL.
  • Adds canonical path/kind indexes without legacy sync triggers.
  1. Contents:
  • Replaces graph.statement.predicate with canonical graph.statement.predicate_path.
  • Replaces permission namespace columns in-place with resource_kind/subject_kind.
  • Removes compatibility alias/sync-trigger pattern in favor of direct schema replacement.
  1. Contents:
  • Re-defines core graph SQL contracts to canonical kind/path terminology (resource create/get/query and collection predicate helper callers).
  • Re-defines permission function contracts to *_kind terminology and updates internal table references accordingly.
  • Keeps hard-cutover behavior with no alias-column back-compat shim in migration body.
  1. Applied forward stabilization migrations:
  • db/migrations/20260329_011_resource_path_ltree_sanitization.sql
  • db/migrations/20260329_012_create_resource_conflict_arbiter_fix.sql

Phase 2 Verification Gate (2026-03-29)

Verification checkpoint from plan section 4.2 requires:

  1. Fresh DB bootstrap succeeds.
  2. Migration contract tests pass.

Executed checks:

  1. pnpm vitest run --project integration-tests-node-pglite-memory integration-tests/db-bootstrap.db-contract.test.ts integration-tests/db-migrations.db-contract.test.ts --reporter=dot
  • Result: Test Files 2 passed (2), Tests 10 passed (10), Duration 8.26s.
  • Interpretation: Fresh bootstrap path and migration contract surface are green.
  1. pnpm vitest run --project integration-tests-node-pglite-memory integration-tests/graph-db-functions.db-contract.test.ts integration-tests/graph.router.test.ts --reporter=dot
  • Result: Test Files 2 passed (2), Tests 19 passed (19), Duration 35.28s.
  • Interpretation: Hard-cutover graph function/view behavior remains green on current migration set.

Gate Revalidation (Post-Bundle Refresh)

Executed checks:

  1. pnpm vitest run --project integration-tests-node-pglite-memory integration-tests/db-bootstrap.db-contract.test.ts integration-tests/db-migrations.db-contract.test.ts --reporter=dot
  • Result: Test Files 2 passed (2), Tests 10 passed (10), Duration 7.93s.
  • Interpretation: Fresh bootstrap and migration contract surfaces remain green after bundle refresh.
  1. pnpm vitest run --project integration-tests-node-pglite-memory integration-tests/graph-db-functions.db-contract.test.ts integration-tests/graph.router.test.ts --reporter=dot
  • Result: Test Files 2 passed (2), Tests 19 passed (19), Duration 32.78s.
  • Interpretation: Phase 2 hard-cutover behavior remains green on the refreshed migration replay path.

Gate for starting SQL edits

Proceed to migration edits only after the three open questions above are resolved in writing for this phase.