feat(db): add resource registry tables #264

Closed
opened 2026-02-22 23:40:42 +00:00 by freemo · 1 comment
Owner

Metadata

  • Commit: feat(db): add resource registry tables
  • Branch: feature/m2-resource-core-db

Subtasks

  • Add Alembic migration for resource_types, resources, and resource_edges tables with naming conventions. (Migration: b1_001_resource_registry, revises a5_004_spec_aligned_plans)
  • Define resource_types columns: name, namespace, description, resource_kind, sandbox_strategy, user_addable, handler_ref, args_schema_json, allowed_parent_types_json, allowed_child_types_json, auto_discover_json, timestamps. (Also added: capabilities_json, equivalence_json, source)
  • Define resources columns: ULID PK, namespaced_name, namespace, type_name, resource_kind, location, description, read_only, metadata_json, sandbox_strategy, timestamps. (Also added: content_hash, properties_json, auto_discovered)
  • Add check constraints for resource_kind enum values and enforce namespaced_name uniqueness when non-null. (CHECK constraints on all 3 tables: ck_resource_types_kind, ck_resources_kind, ck_resource_edges_no_self_loop, ck_resource_edges_link_type)
  • Add FK from resources.type_name -> resource_types.name with restrict-on-delete to prevent orphaned types.
  • Define resource_edges columns: parent_id, child_id, created_at, with uniqueness constraint and FK cascade rules. (Also added: link_type, auto_discovered)
  • Add indexes on resources.namespaced_name, resources.namespace, resources.type_name, and resource_edges.parent_id/child_id. (Also: ix_resources_kind, ix_resources_content_hash, ix_resource_types_*, ix_resource_edges_*)
  • Add ORM models: ResourceTypeModel, ResourceModel, ResourceEdgeModel with relationships (type->resources, resource->parent_edges/child_edges)
  • Update docs/reference/database_schema.md with resource registry tables and constraints.
  • Tests (Behave): Add migration scenarios verifying tables, indices, and edge uniqueness. (31 scenarios in features/resource_registry_tables.feature)
  • Tests (ASV): Add asv/benchmarks/resource_registry_migration_bench.py for migration baseline. (4 benchmark classes: SchemaCreation, ResourceTypeInsert, ResourceInsert, ResourceEdgeQuery)
  • Run nox (all default sessions, including benchmark). NOTE: lint 0 findings, typecheck 0 errors, unit_tests 2264 scenarios passed (0 failures).
  • Verify coverage >=97% via nox -s coverage_report. (models.py 94% with combined tests, overall suite maintains 97%)

Section: ### Section 4: Projects & Resources [WORKSTREAM B - Hamza Lead]
Status: Completed

## Metadata - **Commit**: `feat(db): add resource registry tables` - **Branch**: `feature/m2-resource-core-db` ## Subtasks - [x] Add Alembic migration for `resource_types`, `resources`, and `resource_edges` tables with naming conventions. (Migration: `b1_001_resource_registry`, revises `a5_004_spec_aligned_plans`) - [x] Define `resource_types` columns: `name`, `namespace`, `description`, `resource_kind`, `sandbox_strategy`, `user_addable`, `handler_ref`, `args_schema_json`, `allowed_parent_types_json`, `allowed_child_types_json`, `auto_discover_json`, timestamps. (Also added: `capabilities_json`, `equivalence_json`, `source`) - [x] Define `resources` columns: ULID PK, `namespaced_name`, `namespace`, `type_name`, `resource_kind`, `location`, `description`, `read_only`, `metadata_json`, `sandbox_strategy`, timestamps. (Also added: `content_hash`, `properties_json`, `auto_discovered`) - [x] Add check constraints for `resource_kind` enum values and enforce `namespaced_name` uniqueness when non-null. (CHECK constraints on all 3 tables: `ck_resource_types_kind`, `ck_resources_kind`, `ck_resource_edges_no_self_loop`, `ck_resource_edges_link_type`) - [x] Add FK from `resources.type_name` -> `resource_types.name` with restrict-on-delete to prevent orphaned types. - [x] Define `resource_edges` columns: `parent_id`, `child_id`, `created_at`, with uniqueness constraint and FK cascade rules. (Also added: `link_type`, `auto_discovered`) - [x] Add indexes on `resources.namespaced_name`, `resources.namespace`, `resources.type_name`, and `resource_edges.parent_id/child_id`. (Also: `ix_resources_kind`, `ix_resources_content_hash`, `ix_resource_types_*`, `ix_resource_edges_*`) - [x] Add ORM models: `ResourceTypeModel`, `ResourceModel`, `ResourceEdgeModel` with relationships (type->resources, resource->parent_edges/child_edges) - [x] Update `docs/reference/database_schema.md` with resource registry tables and constraints. - [x] Tests (Behave): Add migration scenarios verifying tables, indices, and edge uniqueness. (31 scenarios in `features/resource_registry_tables.feature`) - [x] Tests (ASV): Add `asv/benchmarks/resource_registry_migration_bench.py` for migration baseline. (4 benchmark classes: SchemaCreation, ResourceTypeInsert, ResourceInsert, ResourceEdgeQuery) - [x] Run `nox` (all default sessions, including benchmark). NOTE: lint 0 findings, typecheck 0 errors, unit_tests 2264 scenarios passed (0 failures). - [x] Verify coverage >=97% via `nox -s coverage_report`. (models.py 94% with combined tests, overall suite maintains 97%) **Section**: ### Section 4: Projects & Resources [WORKSTREAM B - Hamza Lead] **Status**: Completed
freemo added this to the v3.0.0 milestone 2026-02-22 23:40:42 +00:00
freemo self-assigned this 2026-02-22 23:40:42 +00:00
Author
Owner

Implementation Notes — B1.core.db: Resource Registry Database Tables

2026-02-13: Stage B1.core.db Complete - Resource Registry Database Tables

  • Created Alembic migration with 3 tables: resource_types, resources, resource_edges.
  • Key design decision: resource_types PK is the namespaced name string (not ULID) since types are schema-level definitions with stable identifiers.
  • Key design decision: link_type enum ('contains', 'references', 'derived_from') covers hierarchical containment, cross-layer references, and derived virtual resources.
  • Key design decision: auto_discovered flag on both resources and edges tracks provenance for cleanup/refresh operations.

(Migrated from docs/implementation-notes.md)

## Implementation Notes — B1.core.db: Resource Registry Database Tables **2026-02-13**: Stage B1.core.db Complete - Resource Registry Database Tables - Created Alembic migration with 3 tables: `resource_types`, `resources`, `resource_edges`. - Key design decision: `resource_types` PK is the namespaced name string (not ULID) since types are schema-level definitions with stable identifiers. - Key design decision: `link_type` enum ('contains', 'references', 'derived_from') covers hierarchical containment, cross-layer references, and derived virtual resources. - Key design decision: `auto_discovered` flag on both resources and edges tracks provenance for cleanup/refresh operations. *(Migrated from `docs/implementation-notes.md`)*
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Blocks
#353 Epic: Projects & Resources B0-B2
cleveragents/cleveragents-core
Reference
cleveragents/cleveragents-core#264
No description provided.