standardizing data layer strategy

[willgriffin]

Here is a summary of the database design approach, formatted as a technical proposal or ticket for your team's evaluation.

---

Ticket: Proposal for smrt Data Layer - Hybrid STI Pattern

Objective:

To specify a robust, flexible, and scalable database pattern for the smrt framework. This pattern must support a Single Source of Truth (SSOT) for primitive types (e.g., Event) while allowing extended agent-specific types (e.g., HockeyGame, Meeting) to co-exist.

---

1. Executive Summary: The Hybrid Pattern

This proposal advocates for a Hybrid Single Table Inheritance (STI) Pattern. This model is not a strict STI implementation but a pragmatic hybrid that combines two patterns to get the best of both:

1. Single Table Inheritance (STI): A base table (e.g., events) holds all shared fields and a type discriminator column.¹

2. JSON "Property Bag": A single meta (or properties) column of type JSONB holds all non-relational, unstructured data.

This hybrid approach will be managed by the smrt framework, which will introspect TypeScript types to correctly map fields to either the JSONB bag or a dedicated top-level column.

2. Core Problem

Agents in the ecosystem need to create specialized types that extend a common primitive. For example:

• A base Event primitive.

• An agent-defined Meeting type that extends Event.

• An agent-defined HockeyGame type that extends Event.

We need to store Meeting and HockeyGame objects in the same events table to allow for simple, polymorphic queries (e.g., "fetch all events for today"). However, HockeyGame may have relationships (e.g., to a Team table) that Meeting does not.

3. Proposed smrt Framework Logic

The smrt framework's introspection logic will be responsible for mapping TypeScript class fields based on the following rules:

Rule 1: Common Fields

• What: Any field defined on the base primitive (e.g., Event.title, Event.start_time).

• Action: Mapped to a dedicated, top-level column on the base table (e.g., events.title).

Rule 2: Unstructured Properties (The "Property Bag")

• What: Any field on an extended type (e.g., HockeyGame.arena_name) that is not a foreign key or critical, indexed search field.

• Action: Mapped into the meta JSONB column.

• Example: A HockeyGame with { arena_name: 'The Dome' } is saved in events.meta as {"arena_name": "The Dome"}.

• Benefit: High flexibility.² New properties can be added by agents without requiring ALTER TABLE migrations.

Rule 3: Relational Foreign Keys (The "STI" Part)

• What: Any field on an extended type that represents a relationship to another table (e.g., HockeyGame.home_team_id).

• Action: Mapped to a dedicated, NULLable, top-level column on the base table (e.g., events.home_team_id).

• Benefit: Enables database-level integrity (Foreign Key constraints) and, most importantly, allows for fast, indexed SQL JOINs.

4. Example Schema (events table)

This hybrid approach would result in the following schema:

Column | Type | Purpose -- | -- | -- id | PK | Primary Key type | String | STI Discriminator ("Meeting", "HockeyGame") title | String | Common Field (from base Event) start_time | DateTime | Common Field (from base Event) meta | JSONB | Property Bag (for non-relational data) meeting_room_id | FK | STI Relation (NULL for HockeyGame) home_team_id | FK | STI Relation (NULL for Meeting) away_team_id | FK | STI Relation (NULL for Meeting)

---

5. Trade-offs Analysis

This approach is a deliberate trade-off that aligns with our ecosystem goals.

Benefits:

• SSOT / Polymorphism: Agents can query the events table and get all event types in a single, fast query.

• Relational Integrity: We retain the ability to use FOREIGN KEY constraints for critical relationships.

• Query Performance: We can perform fast, indexed JOINs on relational columns (e.g., JOIN teams ON events.home_team_id = teams.id).

• Flexibility: Simple properties (arena_name) can be added by agents without database schema migrations, increasing developer velocity.

Acknowledged Costs (Cons):

• Schema Rigidity (for Relations): When an agent needs a new relationship, smrt must generate and run an ALTER TABLE migration to add the new NULLable FK column. This is the main cost, but it's an acceptable one for the benefit of real relationships.

• Sparse Columns: The events table will have NULLable FK columns that are only populated for their specific type. This is a standard and acceptable trade-off for STI.

• Application-Layer Integrity: The smrt framework (not the DB) must enforce that a HockeyGame has a non-null home_team_id. The DB can only enforce that if a value exists, it's valid.

6. Recommendation

Approve this Hybrid STI Pattern as the standard for smrt primitives. This model provides the best balance of flexibility (via JSONB), performance (via JOINs), and SSOT (via a single table).

The smrt core library must be updated to introspect types and differentiate between property-bag fields and relational fields, generating migrations only when new relational columns are required.

[willgriffin]

i'd like to evaluate the viability of using accessors to interface with the metadata object

[willgriffin]

Implementation Feasibility Analysis

I've analyzed the current codebase against this STI proposal. Here's what we found:

Current State: Class Table Inheritance (CTI)

The framework currently implements one table per class with field inheritance:

@smrt()
class Event extends SmrtObject {
  title: string = '';
  startTime: Date = new Date();
}

@smrt()
class Meeting extends Event {
  roomId = foreignKey(Room);
}

@smrt()
class HockeyGame extends Event {
  homeTeamId = foreignKey(Team);
  arenaName: string = '';
}

Creates three separate tables:

• events: (id, title, start_time, created_at, updated_at)
• meetings: (id, title, start_time, room_id, created_at, updated_at)
• hockey_games: (id, title, start_time, home_team_id, arena_name, created_at, updated_at)

Each child table duplicates all inherited fields.

Proposed: STI as Optional Feature

Making STI opt-in keeps the change non-breaking while enabling the benefits where needed:

// Set strategy once on base class
@smrt({ tableStrategy: 'sti' })
class Event extends SmrtObject {
  title: string = '';
  startTime: Date = new Date();
}

// Children automatically inherit strategy
@smrt()
class Meeting extends Event {
  roomId = foreignKey(Room);
}

@smrt()
class HockeyGame extends Event {
  homeTeamId = foreignKey(Team);
  awayTeamId = foreignKey(Team);
  arenaName = meta();  // Explicit JSONB storage
}

Creates one shared table:

CREATE TABLE events (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,           -- Discriminator
  meta JSON,                    -- Flexible storage
  title TEXT NOT NULL,
  start_time TIMESTAMP,
  room_id TEXT,                 -- Meeting (nullable)
  home_team_id TEXT,            -- HockeyGame (nullable)
  away_team_id TEXT,            -- HockeyGame (nullable)
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_events_type ON events(type);

Implementation Plan: 4-5 Weeks

Week 1: Foundation - Add tableStrategy config with inheritance, base class detection
Week 2: Schema generation - Conditional generateSTISchema() with discriminator + meta
Week 3: Serialization - Meta storage, fail-fast validation
Week 4: Query logic - Auto-inject type filters
Week 5: Testing & polish

Strategy Inheritance Behavior

// Set once on base - children inherit automatically
@smrt({ tableStrategy: 'sti' })
class Event extends SmrtObject { }

@smrt()  // Inherits 'sti' from Event
class Meeting extends Event { }

// Can override if needed
@smrt({ tableStrategy: 'cti' })
class SpecialEvent extends Event { }  // Opts back to CTI

Key Differences

Aspect	Current (CTI)	Proposed (STI)
Tables	One per class	One per base
Discriminator	None	type column
Field storage	All columns	Columns + JSONB
FK columns	Required	Nullable union
Queries	Direct table	Base + type filter

Fail-Fast Validation

Philosophy: Throw descriptive errors early. Developer fixes schema, not the framework.

// Example validation
async save() {
  if (strategy === 'sti' && !data.type) {
    throw new Error(
      `STI class '${this.constructor.name}' missing 'type' discriminator.`
    );
  }
}

Clear errors guide developers:

• "STI configured but no descendant classes found"
• "Table missing 'type' discriminator column"
• "Type mismatch: expected 'Meeting' but got 'HockeyGame'"

Benefits of Optional Approach

1. Zero breaking changes - CTI remains default
2. Gradual adoption - use STI where beneficial
3. Easy comparison - benchmark both strategies
4. Coexistence - both work simultaneously

Use Cases for STI

Good fits: Event hierarchies, polymorphic queries, agent-driven schema evolution

Better with CTI: Stable hierarchies, no polymorphic queries needed

Questions

1. Meta classification: Explicit meta() or auto-detect?
2. Polymorphic queries: Load mixed types from base table?
3. Index strategy: Partial indexes for FK columns?
4. Migration tooling: Provide CTI → STI converter?

---

Bottom line: Feasible in 4-5 weeks. Optional approach gives maximum flexibility while maintaining backward compatibility.

Full analysis: STI_IMPLEMENTATION_ANALYSIS.md