feat(db): .kc metadata database migration #148

forbes · 2026-02-18T21:04:20Z

forbes commented

2026-02-18 21:04:20 +00:00

Closes #140

Adds migrations/018_kc_metadata.sql with all tables needed for .kc server-side metadata indexing, as specified in KC_SERVER.md Section 3.

Tables

`item_metadata`

Indexes silo/manifest.json and silo/metadata.json from .kc files. Primary key on item_id. Stores schema_name, tags (TEXT[]), lifecycle_state, fields (JSONB), kc_version, manifest_uuid, silo_instance, revision_hash.

GIN indexes on tags and fields, B-tree on lifecycle_state.

`item_dependencies`

CAD-extracted assembly dependencies from silo/dependencies.json. Complements (does not replace) the existing relationships table -- this is the CAD-authoritative record, relationships is server-authoritative.

`item_approvals` + `approval_signatures`

ECO workflow tables. Server-authoritative -- the .kc snapshot is a read cache packed on checkout.

`item_macros`

Registered macros from silo/macros/. UNIQUE(item_id, filename).

Also included

docs/KC_SERVER.md -- full specification for .kc server-side metadata integration

Notes

Existing items have no metadata rows -- created on first .kc commit or via PUT /metadata
This is a prerequisite for all Phase 1-7 work (#141-#147)
Uses gen_random_uuid() consistent with recent migrations
Wrapped in BEGIN/COMMIT transaction

Closes #140 Adds `migrations/018_kc_metadata.sql` with all tables needed for .kc server-side metadata indexing, as specified in [KC_SERVER.md](docs/KC_SERVER.md) Section 3. ## Tables ### `item_metadata` Indexes `silo/manifest.json` and `silo/metadata.json` from .kc files. Primary key on `item_id`. Stores schema_name, tags (TEXT[]), lifecycle_state, fields (JSONB), kc_version, manifest_uuid, silo_instance, revision_hash. GIN indexes on tags and fields, B-tree on lifecycle_state. ### `item_dependencies` CAD-extracted assembly dependencies from `silo/dependencies.json`. Complements (does not replace) the existing `relationships` table -- this is the CAD-authoritative record, `relationships` is server-authoritative. ### `item_approvals` + `approval_signatures` ECO workflow tables. Server-authoritative -- the .kc snapshot is a read cache packed on checkout. ### `item_macros` Registered macros from `silo/macros/`. UNIQUE(item_id, filename). ## Also included - `docs/KC_SERVER.md` -- full specification for .kc server-side metadata integration ## Notes - Existing items have no metadata rows -- created on first .kc commit or via PUT /metadata - This is a prerequisite for all Phase 1-7 work (#141-#147) - Uses `gen_random_uuid()` consistent with recent migrations - Wrapped in BEGIN/COMMIT transaction

forbes added 1 commit 2026-02-18 21:04:20 +00:00

feat(db): .kc metadata database migration (#140 ) 8d777e83bb

Add migration 018_kc_metadata.sql with all tables needed for .kc
server-side metadata indexing:

- item_metadata: indexed manifest + metadata fields from silo/
  directory (tags, lifecycle_state, fields JSONB, manifest info)
- item_dependencies: CAD-extracted assembly dependencies
  (complements existing relationships table)
- item_approvals + approval_signatures: ECO workflow state
- item_macros: registered macros from silo/macros/

Also adds docs/KC_SERVER.md specification document.

Closes #140

forbes merged commit 628cd1d252 into main

2026-02-18 21:05:16 +00:00

forbes deleted branch feat/kc-metadata-migration

2026-02-18 21:05:16 +00:00

forbes referenced this issue from a commit

2026-02-18 21:05:16 +00:00

Merge pull request 'feat(db): .kc metadata database migration' (#148) from feat/kc-metadata-migration into main

forbes referenced this pull request

2026-02-18 22:37:55 +00:00

feat(kc): commit extraction pipeline + metadata API (Phase 1) #149

Sign in to join this conversation.