Hedhog YAML

Every HedHog library drives its own slice of the database through two directories: hedhog/table/ (schema/DDL) and hedhog/data/ (seed/DML). This page is the full reference for both formats: every column type, every relationship pattern, and the conventions this codebase actually follows — including a few real extensions that go beyond the strict formal schema.

When a library is added (hedhog add <library-name>), the pipeline is:

1. Scan libraries/<library>/hedhog/table/ for YAML schema definitions — one file per table.
2. Pair each table schema with its seed data from libraries/<library>/hedhog/data/ (matched by file name).
3. Generate a PostgreSQL migration (CREATE TABLE, indexes, FK constraints, INSERTs) in apps/api/prisma/migrations/.
4. Apply the migration via Prisma.

libraries/
└── <library-name>/
    └── hedhog/
        ├── table/          ← DDL — one file per table (required)
        │   ├── my_table.yaml
        │   └── my_other_table.yaml
        ├── data/            ← seed data — one file per table (optional)
        │   ├── my_table.yaml
        │   └── my_other_table.yaml
        ├── query/           ← raw .sql, appended verbatim after the migration (optional)
        │   └── post_migration.sql
        └── frontend/        ← frontend page / message files (optional)

Repository policy for hub: never run hedhog dev apply, never manually edit apps/api/prisma/schema.prisma, and never reset/recreate the project, database, or existing migrations. When a table/ or data/ YAML changes, add a new SQL migration under apps/api/prisma/migrations/ that mirrors the change, then run pnpm prisma:deploy from apps/api.

The file name (without extension), in snake_case, becomes the PostgreSQL table name. user_profile.yaml → table "user_profile".

columns:
  - type: pk
  - name: my_column
    type: varchar
    isNullable: false

columns is the only key required by the formal schema. In practice, two more top-level keys are used repo-wide and should be considered part of the real contract:

These are HedHog shorthands. Each one expands to a fixed SQL shape, and for most of them the name you give is ignored — the CLI forces a specific column name.

Short aliases translate directly at CREATE TABLE time (no auto name/constraints — name is required):

Any standard PostgreSQL type is also accepted verbatim (case-insensitive): smallint, integer, bigint, serial, bigserial, decimal, numeric, real, double precision, money, varchar, char, text, bytea, timestamp[tz], date, time, interval, boolean, json, jsonb, uuid, inet, cidr, macaddr, geometric types, tsvector/tsquery, xml. Unrecognized types fail the migration.

Bare columns with no type: implicitly mean VARCHAR. This is a very common convention across the repo (over a hundred columns use it):

# libraries/core/hedhog/table/user.yaml
- name: name
- name: suspended_reason
  isNullable: true

- name: parent_id
  type: fk
  references:
    table: parent_table   # required
    column: id            # required
    onDelete: CASCADE     # optional, default NO ACTION
    onUpdate: CASCADE     # optional, default NO ACTION

Accepted referential actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION (lowercase values such as cascade/set null also work in practice, but prefer uppercase for consistency with most of the codebase). The generated SQL:

ALTER TABLE "<table>"
  ADD CONSTRAINT "<fkTable>_<column>_fkey"
  FOREIGN KEY ("<column>")
  REFERENCES "<fkTable>" ("<fkColumn>")
  ON DELETE <onDelete> ON UPDATE <onUpdate>;

references.table can point at a table defined in a different @hed-hog/* library, as long as that library is a declared dependency — the migration engine includes it in the dependency graph without re-creating it.

Choose onDelete by semantics, not by habit — this is a deliberate, repo-wide convention:

• CASCADE — true ownership (a page owns its components, a proposal owns its revisions).
• SET NULL — optional/soft reference (created_by_user_id, photo_id).
• RESTRICT — block deletion of rows still referenced by audit/history data (e.g. an agent referenced by its agent_run history, or settlement.reverses_settlement_id).

Table-level indexes go in an indices: (preferred, more common) or indexes: block, sitting next to columns::

# libraries/lms/hedhog/table/xp_skill.yaml
indices:
  - columns: [slug]
    isUnique: true
  - columns: [xp_area_id]
  - columns: [status]

Composite/multi-column indexes are the standard way to express compound uniqueness (e.g. [proposal_id, revision_number], or a 4-column tuple for versioned agent nodes). Both isUnique and unique are accepted as the "make it a unique index" flag — you will see either in the wild.

Partial (filtered) unique indexes use a raw SQL predicate string in where: (this is a different where than the FK-lookup one used in data YAML — here it's a literal SQL fragment):

# libraries/inbox/hedhog/table/inbox_account.yaml
indices:
  - columns: [user_id, is_default]
    isUnique: true
    where: is_default = true AND deleted_at IS NULL

# libraries/crm/hedhog/table/crm_activity.yaml
indices:
  - columns: [person_id, source_kind]
    isUnique: true
    where: source_kind = 'followup' AND completed_at IS NULL

Any locale_varchar/locale_text column is removed from the main table and placed in an auto-synthesized <table>_locale companion table — no extra table YAML file is needed for it:

# libraries/lms/hedhog/table/xp_skill.yaml
- name: name
  type: locale_varchar
  locale: { pt: Nome, en: Name }
- name: description
  type: locale_text
  isNullable: true
  locale: { pt: Descrição, en: Description }

The locale: {...} key seen above is not part of the migration engine — it is a design-time default-label hint some tooling reads (e.g. an admin CRUD generator). It is optional and can be an empty object (locale: {}).

Simple FK (many-to-one) — the overwhelming majority of relationships:

- name: category_id
  type: fk
  isNullable: true
  references: { table: category, column: id, onDelete: SET NULL }

Self-referencing FK (trees, threads, hierarchies) — e.g. menu.yaml (menu tree), category.yaml (category tree), course_lesson_discussion_topic.yaml (comment threads), agent_run.yaml (parent_run_id):

# libraries/lms/hedhog/table/course_lesson_discussion_topic.yaml
- name: parent_topic_id
  type: fk
  isNullable: true
  references: { table: course_lesson_discussion_topic, column: id, onDelete: CASCADE }

Junction (pivot) tables for many-to-many — a dedicated table with exactly two fk columns, usually with a composite unique index:

# libraries/core/hedhog/table/dashboard_component_role.yaml
columns:
  - type: pk
  - name: component_id
    type: fk
    references: { table: dashboard_component, column: id, onDelete: CASCADE }
  - name: role_id
    type: fk
    references: { table: role, column: id, onDelete: CASCADE }
  - type: created_at
  - type: updated_at

The migration engine auto-detects this shape: any table with two FK columns pointing at both sides of a relationship is treated as its junction table when a data YAML uses relations: between those two entities (see §3.5) — you never reference the junction table by name.

Polymorphic-ish relations — Postgres FKs can't target "one of several tables", so this is modeled as a *_type enum plus a plain integer id, with a composite unique index for integrity:

# libraries/operations/hedhog/table/operations_approval.yaml
- name: target_type
  type: enum
  values: [timesheet, time_off_request, schedule_adjustment_request, expense_reimbursement_request]
- name: target_id
  type: int
indices:
  - columns: [target_type, target_id]
    isUnique: true

• Soft delete: a nullable deleted_at timestamp column by convention (there is no dedicated type: deleted_at alias) — always paired with an index.
• Money as integer: monetary values are modeled as *_amount_cents / amount_cents (int/bigint), not numeric, to avoid floating-point drift.
• JSON/JSONB for flexible payloads: input/output/state (jsonb), snapshot_json (json).
• Post-migration raw SQL: files in hedhog/query/*.sql are appended verbatim after all generated CREATE TABLE/INSERT statements, in filesystem order — used for views, triggers, and anything easier expressed directly in SQL. When you add a trigger/function this way, keep it in sync with libraries/<lib>/hedhog/query/triggers.sql so fresh installs get the same objects.

The file name (without extension) must match the corresponding table name. A table with no data file simply gets no seed rows — that's valid.

A data YAML file must parse to a root-level array; otherwise it's skipped with a warning and nothing is inserted.

- id: 1
  name: "First Row"
- id: 2
  name: "Second Row"

- id: 1
  slug: example-item
  order: 10
  is_active: true
  rating: 4.5
  description: "A description with 'single quotes' works fine"

Never hard-code a foreign key integer — look the row up by a stable key instead:

- id: 1
  name: "Child Row"
  parent_id:
    where:
      slug: some-slug

This compiles to an inline sub-select: (SELECT "id" FROM "parent_table" WHERE "slug" = 'some-slug'). Multiple keys in the same where: are joined with AND.

For locale_varchar/locale_text columns, provide an object keyed by locale code instead of a plain string — the row fans out into the auto-generated <table>_locale table, one insert per locale supplied:

- id: 1
  slug: dashboard
  title:
    en: "Dashboard"
    pt: "Painel"
  description:
    en: "Main control panel"
    pt: "Painel de controlo principal"

Supported locale codes: en, pt, es, it, jp. You don't need to provide every locale for every row. A plain lookup/enum table that isn't meant to be translated should just use a plain (non-locale_*) column with a raw string instead — don't reach for locale_varchar unless the value is genuinely user-facing, translatable content.

Caution: the engine recognizes a locale object only if every key is a 2–3 letter code from the known set above. Any other key shape is treated as a raw JSON-like value instead of translations.

relations: triggers inserts into one or more related tables in the same transaction as the parent row:

- id: 1
  slug: admin
  relations:
    permission:
      - where:
          slug: view-dashboard
      - where:
          slug: manage-users

Resolution rule: the engine looks for a junction table with two FK columns pointing at the parent and the target (§2.8) — if found, it inserts into the junction table automatically. If no junction table exists, the FK column pointing back to the parent is injected automatically and the row is inserted directly into the target table (a direct child, not a many-to-many).

Nested relations can go arbitrarily deep — every nested level resolves the same way. This example seeds a 3-level settings tree, where the link back to the immediate parent (setting.group_id) is implicit from nesting, while a cross-tree FK (setting.subgroup_id) still needs an explicit where: lookup:

# libraries/core/hedhog/data/setting_group.yaml
- slug: general
  name: { en: General, pt: Geral }
  relations:
    setting:
      - slug: pagination-page-sizes
        type: array
        component: checkbox
        value: '["6","12","24","48","96"]'
        subgroup_id:
          where:
            slug: general-system
        relations:
          setting_list:
            - value: '6'
              order: 0
            - value: '12'
              order: 1

When a library needs to link its own rows to a table owned by a different library (so nesting under that library's data file isn't an option), seed the junction table directly as its own flat data file, with both sides resolved by where::

# libraries/finance/hedhog/data/role_route.yaml
- role_id:
    where:
      slug: admin-finance
  route_id:
    where:
      url: /dashboard-core/home
      method: GET

This is how finance attaches admin-finance to shared core dashboard routes without ever touching core/hedhog/data/route.yaml.

relations: blocks are plain YAML values, so native YAML anchors/aliases are a handy way to avoid repeating the same role list across dozens of rows — used throughout core/hedhog/data/route.yaml:

- url: /auth/verify
  method: GET
  type: HTTP
  relations: &core_user_roles
    role:
      - where: { slug: admin }
      - where: { slug: admin-access }
- url: /auth/roles
  method: GET
  type: HTTP
  relations: *core_user_roles
- url: /profile
  method: GET
  type: HTTP
  relations: *core_user_roles

• Idempotent by design: for any row whose identity key can be resolved (slug → a unique column → a unique index → the [url, method] pair for route-shaped tables), the engine emits an UPDATE ... WHERE <key> followed by an INSERT ... WHERE NOT EXISTS (...) — never a bare INSERT relying on ON CONFLICT. Re-running a migration that seeds existing rows is safe.

• Junction relations are wrapped in a guarded DO $$ ... END $$ block that raises loudly if either side of the relation can't be resolved, instead of silently inserting nothing:

  DO $$
  DECLARE
    v_parent_id INTEGER;
    v_target_id INTEGER;
  BEGIN
    v_parent_id := (SELECT "id" FROM "menu" WHERE "slug" = '/core/management');
    v_target_id := (SELECT "id" FROM "role" WHERE "slug" = 'admin');
    IF v_parent_id IS NULL THEN
      RAISE EXCEPTION 'HedHog migration: cannot link "menu" to "role_menu" — parent row not found';
    END IF;
    INSERT INTO "role_menu" ("menu_id", "role_id")
    SELECT v_parent_id, v_target_id
    WHERE NOT EXISTS (SELECT 1 FROM "role_menu" WHERE "menu_id" = v_parent_id AND "role_id" = v_target_id);
  END $$;
  

• Execution order: the locale table is always created first, then tables are topologically sorted by FK dependency so every parent exists before its children. A circular dependency fails the migration immediately with the offending tables listed. Cross-library dependencies (declared in package.json) are pulled into the same sort.

• There is no conditional/environment-scoped seeding syntax (no env:/if: key) — the only way to make a seed conditional is a where: lookup that may resolve to nothing, or simply not shipping the data file at all.

Whenever a backend endpoint's URL, method, auth, or roles change, route.yaml and role.yaml in the same library must be kept in sync (see the root CLAUDE.md "Endpoint Permission Sync" rule, or run the /sync-permissions skill).

# libraries/core/hedhog/data/role.yaml
- slug: admin
  name: { en: Administrator, pt: Administrador }
  description: { en: System administrator with full access., pt: Administrador do sistema com acesso total. }
- slug: admin-access
  name: { en: Admin Access, pt: Acesso de Administrador }
  description: { en: Access to administrative features., pt: Acesso a funcionalidades administrativas. }

# libraries/finance/hedhog/data/role.yaml
- slug: admin-finance
  name: { en: Finance Administrator, pt: Administrador de Finanças }
- slug: admin-finance-operator
  name: { en: Finance Operator, pt: Operador de Finanças }
- slug: finance-statements-editor
  name: { en: Finance Statements Editor, pt: Editor de Extratos Bancários }

One row per HTTP endpoint (url + method) or MCP tool (tool_name). Roles are assigned inline via relations.role:

# libraries/core/hedhog/data/route.yaml
- url: /user/:userId/verify-identifier/:identifierId
  method: POST
  type: HTTP
  relations:
    role:
      - where: { slug: admin }
      - where: { slug: admin-access }

# libraries/finance/hedhog/data/route.yaml
- url: /finance/realtime/cursor
  method: GET
  relations:
    role:
      - where: { slug: admin }
      - where: { slug: admin-finance }
      - where: { slug: admin-finance-operator }
      - where: { slug: finance-statements-editor }

Always include admin plus the library-specific admin role (admin-<library>) on every route your library adds.

When a library needs its own role to see routes/menus owned by a different library (typically shared core dashboard routes), don't edit that library's route.yaml — add rows to your own flat role_route.yaml / role_menu.yaml instead, as shown in §3.6.

# hedhog/table/category.yaml
columns:
  - type: pk
  - name: slug
    type: slug
  - name: order
    type: order
  - type: created_at
  - type: updated_at

# hedhog/data/category.yaml
- id: 1
  slug: electronics
  order: 1
- id: 2
  slug: clothing
  order: 2

# hedhog/table/category.yaml
columns:
  - type: pk
  - name: slug
    type: slug
  - name: name
    type: locale_varchar   # → category_locale, not category
    length: 100
  - name: description
    type: locale_text      # → category_locale, not category
  - type: created_at
  - type: updated_at

# hedhog/data/category.yaml
- id: 1
  slug: electronics
  name: { en: Electronics, pt: Eletrônicos, es: Electrónica }
  description:
    en: Electronic products and gadgets
    pt: Produtos eletrônicos e gadgets

# hedhog/table/role_permission.yaml (junction table)
columns:
  - type: pk
  - name: role_id
    type: fk
    references: { table: role, column: id, onDelete: CASCADE, onUpdate: CASCADE }
  - name: permission_id
    type: fk
    references: { table: permission, column: id, onDelete: CASCADE, onUpdate: CASCADE }

# hedhog/data/role.yaml
- id: 1
  slug: admin
  relations:
    permission:
      - where: { slug: view-dashboard }
      - where: { slug: manage-users }
- id: 2
  slug: viewer
  relations:
    permission:
      - where: { slug: view-dashboard }

# hedhog/table/article.yaml
columns:
  - type: pk
  - name: status
    type: enum
    enum: [draft, published, archived]
  - name: title
    type: varchar
    length: 255
  - type: created_at
  - type: updated_at

# hedhog/data/article.yaml
- id: 1
  status: draft
  title: "My First Article"
- id: 2
  status: published
  title: "Hello World"

# hedhog/table/product.yaml
columns:
  - type: pk

  - name: category_id
    type: fk
    references: { table: category, column: id, onDelete: SET NULL, onUpdate: CASCADE }
    isNullable: true

  - name: slug
    type: slug

  - name: order
    type: order

  - name: status
    type: enum
    enum: [active, inactive, discontinued]

  - name: name
    type: locale_varchar   # → product_locale
    length: 200

  - name: description
    type: locale_text      # → product_locale

  - name: sku
    type: varchar
    length: 100
    isUnique: true

  - name: price_amount_cents
    type: bigint

  - name: metadata
    type: jsonb
    isNullable: true

  - name: deleted_at
    type: timestamptz
    isNullable: true

  - type: created_at
  - type: updated_at

indices:
  - columns: [deleted_at]