Schema Authoring

Driving rules

1. Ask for the domain idea (one paragraph). If vague: one sharp clarifying question, not an interrogation.
2. Tables = plural snake_case. Implicit collections → own tables.
3. Every table: PK (int autoincrement default; UUID only if domain dictates). Business identifier → unique index. Lookup/filter column → non-unique index.
4. FKs optional — only include when the relation is unambiguous. Otherwise foreignKeys: {} and let the Designer model it via relates/depend.
5. Output = one complete Schema.yaml block + explicit assumption list.

Import instruction: Schema → Import → File in the Designer.

1. Schema.yaml structure

tables:
  <table_name_snake_case>:
    name: <same as the key>
    columns:
      -
        name: <column_name>
        type: <SQL type, see catalogue>
        phpType: <int | string | datetime | float | bool>
        length: <optional integer, for varchar/char>
        nullable: <true | false>
        primary: <true | false>           # PK column only
        autoincrement: <true | false>     # PK int only
    indexes:
      -
        name: <index_name | PRIMARY>
        columns: [<col>, <col>, …]
        type: <primary | unique | index>
    foreignKeys: {}                       # empty map OR list of:
      -
        column: <local_column>
        referencedTable: <other_table>
        referencedColumn: <other_column>

Column required: name, type, phpType, nullable. length required for varchar. primary/autoincrement only on PK. Index required: name, columns, type. PK index = PRIMARY. FKs empty: use {} (matches real DB exports) or [].

2. Types

3. Modelling heuristics

• Identifier columns: every business object typically has an internal int PK (id) and a public business identifier (identifier, often varchar(36) UUID7). Unique index on the business identifier.
• Active period: "active period" in the idea → activeFrom (date, not nullable) + activeUntil (date, nullable = open period).
• Lookup tables: categories/types/statuses get their own table even when described as enums.
• Junctions: many-to-many → explicit junction table with PK + two FK columns.
• No relations in Schema.yaml unless obvious. Relations go into Aggregate.yaml (relates/depend/adopt) via tools-definition.

4. Example

Idea: "Track meter readings. A counter has a number and an active period, lives at a meter location, can link to multiple registers via a gateway."

tables:
  counters:
    name: counters
    columns:
      - { name: id, type: int, phpType: int, nullable: false, primary: true, autoincrement: true }
      - { name: identifier, type: varchar, phpType: string, length: 36, nullable: false }
      - { name: meterLocationIdentifier, type: varchar, phpType: string, length: 50, nullable: false }
      - { name: counterNumber, type: varchar, phpType: string, length: 50, nullable: false }
      - { name: activeFrom, type: date, phpType: datetime, nullable: false }
      - { name: activeUntil, type: date, phpType: datetime, nullable: true }
    indexes:
      - { name: PRIMARY, columns: [id], type: primary }
      - { name: identifier, columns: [identifier], type: unique }
      - { name: meterLocationIdentifier, columns: [meterLocationIdentifier], type: index }
    foreignKeys: {}

  registers:
    name: registers
    columns:
      - { name: id, type: int, phpType: int, nullable: false, primary: true, autoincrement: true }
      - { name: identifier, type: varchar, phpType: string, length: 36, nullable: false }
    indexes:
      - { name: PRIMARY, columns: [id], type: primary }
      - { name: identifier, columns: [identifier], type: unique }
    foreignKeys: {}

Assumptions to state:

• Business identifier = identifier (UUID). If actual key is counterNumber, move unique index there.
• FKs empty — counter ↔ register link goes into the Designer via relates/depend.

5. Reference

• Full working example with FKs: examples/Schema.yaml (MeterDevice — counters, registers, gateways, meter locations).
• Post-import interpretation: tools-definition