Talk To Your Tables (TTYT) App Definition Guide (Expert)

This guide describes the app definition schema, modeling conventions, and the platform’s required ttyt_user structure for more technical builders.

Overview

• The AppDefinition drives both database instantiation and runtime behavior enforced by AI agents.
• Core parts:
  • Entities (tables) with Attributes and optional Constraints
  • Relationships (foreign-key semantics) between entities
  • BusinessRules (declarative, human-readable rules evaluated by agents)

Formal Schema Reference

• AppDefinition
  • entities: List<Entity>
  • relationships: List<Relationship>
  • businessRules: List<BusinessRule>
• Entity
  • name: String
  • attributes: List<Attribute>
  • constraints: List<Constraint> = []
  • entitySpecificRules: List<BusinessRule> = []
  • idColumn: String = "entity_id" (identity column used by the CUD Interceptor; strongly recommended to keep the default)
• Attribute
  • name: String
  • type: String (PostgreSQL type literal, e.g., text, uuid, timestamp, jsonb)
  • isNullable: Boolean = true
  • isPrimaryKey: Boolean = false
  • defaultValue: String? = null (SQL expression, e.g., gen_random_uuid())
• Constraint
  • generatedSQL: String (full SQL clause, e.g., CHECK (cost >= 0) or UNIQUE (project_id, reviewer_id))
  • metadata: String? = null
• Relationship
  • fromEntity: String
  • toEntity: String
  • fromKey: String
  • toKey: String
  • relationshipType: String (e.g., ONE_TO_MANY, MANY_TO_ONE, MANY_TO_MANY)
• BusinessRule
  • id: String
  • content: String

Entity Identity

• Each entity has an identity column specified by idColumn.
• The sensible default is entity_id; prefer keeping this for consistency across apps. Only deviate if absolutely necessary.
• Identity column type is always text. Use text for the identity column and any FK that references it. The platform auto‑generates textual identifiers by default.
• If you choose a different identity column (e.g., id as uuid), set idColumn on the entity and include a matching attribute (typically isPrimaryKey: true). Ensure all relationships reference the correct key and match types exactly.

Minimal Definition Examples

• Minimal (platform-required users only):

  {
    "entities": [
      {
        "name": "ttyt_user",
        "attributes": [
          {"name": "entity_id", "type": "text", "isNullable": false, "isPrimaryKey": true},
          {"name": "display_name", "type": "text", "isNullable": false},
          {"name": "metadata", "type": "jsonb", "isNullable": true},
          {"name": "role", "type": "text", "isNullable": true}
        ],
        "constraints": [],
        "entitySpecificRules": []
      }
    ],
    "relationships": [],
    "businessRules": []
  }
  

• Minimal custom entity referencing a user:

  {
    "entities": [
      {
        "name": "ttyt_user",
        "attributes": [
          {"name": "entity_id", "type": "text", "isNullable": false, "isPrimaryKey": true},
          {"name": "display_name", "type": "text", "isNullable": false},
          {"name": "metadata", "type": "jsonb"},
          {"name": "role", "type": "text"}
        ]
      },
      {
        "name": "tickets",
        "idColumn": "id",
        "attributes": [
          {"name": "id", "type": "text", "isNullable": false, "isPrimaryKey": true, "defaultValue": "gen_random_uuid()"},
          {"name": "title", "type": "text", "isNullable": false},
          {"name": "assignee_id", "type": "text", "isNullable": true}
        ],
        "constraints": [
          {"generatedSQL": "CHECK (char_length(title) > 0)"}
        ]
      }
    ],
    "relationships": [
      {"fromEntity": "tickets", "toEntity": "ttyt_user", "fromKey": "assignee_id", "toKey": "entity_id", "relationshipType": "MANY_TO_ONE"}
    ],
    "businessRules": [
      {"id": "BR-1", "content": "Only the assignee or admins can mark a ticket as closed."}
    ]
  }
  

SQL Types and Defaults

• Types are emitted as provided (PostgreSQL dialect: text, uuid, timestamp, date, jsonb, etc.).
• Identity keys: use text for the entity identity (idColumn, default entity_id). The platform auto‑generates textual IDs; only use non‑text identities if you explicitly model a different idColumn (see the single customization example above).
• Nullable vs non-nullable controls column nullability.
• Defaults are literal SQL expressions.

Constraints Best Practices

• Use CHECK for value domains (e.g., enumerations) and numeric/date ranges.
• Use UNIQUE for natural keys or composite uniqueness.
• Keep constraints deterministic and database-enforced; use BusinessRules for process/state logic.

Relationship Modeling

• MANY_TO_ONE: add an FK column on the child and a Relationship entry linking it to the parent PK.
• MANY_TO_MANY: prefer an explicit join entity with two MANY_TO_ONE relationships.
• Type matching is required: the FK column type must exactly match the referenced PK type.

Required ttyt_user Contract

• Canonical columns (must exist exactly as specified):
• entity_id text primary key
• display_name text not null
• metadata jsonb null
• role text null
• Platform ownership: this entity is managed by Klerck; do not alter columns or add constraints.
• Allowed: you may add relationships from your entities to ttyt_user (e.g., owner_id → ttyt_user.entity_id).
• FK rule: any FK referencing ttyt_user.entity_id must use text to match the PK type.
• Operational note: user records are synchronized by the platform; do not write directly.

Branches and Environments

• Branch-first model: develop changes on a short‑lived branch (e.g., dev) and keep main stable.
• Each branch uses a fixed schema and isolated database tenant; there are no per‑revision schemas.
• Typical flow: iterate on a branch in DEV, then promote to main when ready; repeat per environment.

Rule Execution Model

• businessRules (global) and entitySpecificRules (per entity) are evaluated by AI agents during operations.
• Prefer database constraints for hard validation and AI rules for workflow/state transitions.
• For bulk operations/migrations, deferred constraints may be used internally to maintain correctness.

Operational Interfaces & Guardrails

• Custom SQL: available for advanced scenarios; use cautiously and respect tenant isolation and privileges.
• Backup/Restore: supported per app/environment; keep schema changes and data lifecycle aligned.
• Testing: validate changes in DEV; promote after verification.

Worked Example: Lightweight Approval App

• Entities: requests, reviews, plus required ttyt_user.
• Sample sketch:

  {
    "entities": [
      { "name": "ttyt_user", "attributes": [
        {"name": "entity_id", "type": "text", "isNullable": false, "isPrimaryKey": true},
        {"name": "display_name", "type": "text", "isNullable": false},
        {"name": "metadata", "type": "jsonb"},
        {"name": "role", "type": "text"}
      ]},
      { "name": "requests", "attributes": [
        {"name": "entity_id", "type": "text", "isNullable": false, "isPrimaryKey": true},
        {"name": "title", "type": "text", "isNullable": false},
        {"name": "status", "type": "text", "isNullable": false},
        {"name": "owner_id", "type": "text", "isNullable": false}
      ], "constraints": [
        {"generatedSQL": "CHECK (status IN ('draft','submitted','approved','rejected'))"}
      ]},
      { "name": "reviews", "attributes": [
        {"name": "entity_id", "type": "text", "isNullable": false, "isPrimaryKey": true},
        {"name": "request_id", "type": "text", "isNullable": false},
        {"name": "reviewer_id", "type": "text", "isNullable": false},
        {"name": "decision", "type": "text", "isNullable": true}
      ], "constraints": [
        {"generatedSQL": "CHECK (decision IS NULL OR decision IN ('approved','rejected'))"}
      ]}
    ],
    "relationships": [
      {"fromEntity": "requests", "toEntity": "ttyt_user", "fromKey": "owner_id", "toKey": "entity_id", "relationshipType": "MANY_TO_ONE"},
      {"fromEntity": "reviews", "toEntity": "requests", "fromKey": "request_id", "toKey": "entity_id", "relationshipType": "MANY_TO_ONE"},
      {"fromEntity": "reviews", "toEntity": "ttyt_user", "fromKey": "reviewer_id", "toKey": "entity_id", "relationshipType": "MANY_TO_ONE"}
    ],
    "businessRules": [
      {"id": "BR-Submit", "content": "Requests can only be submitted from 'draft' when title is set."},
      {"id": "BR-Approve", "content": "Requests move to 'approved' when all reviews are 'approved'."}
    ]
  }
  

Do’s and Don’ts

• Do match FK types to PK types exactly (notably text → ttyt_user.entity_id).
• Do use database constraints for invariant data checks.
• Do keep business rules declarative and state-focused.
• Don’t modify or add constraints to ttyt_user.
• Don’t write directly to ttyt_user; rely on platform synchronization.
• Don’t mix UI or authentication concerns into the AppDefinition.

For a non‑technical overview, see the main TTYT App Definition Guide.