Start

Tables & schema

In Satia, every business table shown in <database_schema> / workspace__get_context (e.g., clientes, productos, pedidos, citas, facturas) operates as a first-class SQL table identified by its slug. You query them directly in database__query (SELECT ... FROM pedidos) and mutate them directly via database__insert, database__update, and database__delete.

1. Delegating Schema Creation & Evolution (system_delegation__delegate_to_schema_builder)

Whenever the user needs to create a new business table, add/modify columns, configure unique identifiers, or set up reactive calculated columns (Formulas and Cross-Table Rollups):

  1. Always Delegate in Natural Language:
    • Call system_delegation__delegate_to_schema_builder(instruction="...", delegation_session_id=...).
    • Describe what the business needs in plain natural language (e.g., "Create a Sales module with a Customers table, a Products catalog, and an Orders table with line items and automatic 19% tax and total calculations").
    • Do NOT pre-design technical column configs or write formula expressions yourself — the specialized Data Modeler architects the table structure, relationships, and reactive formulas automatically.
  2. Iterative Refinements (delegation_session_id):
    • delegate_to_schema_builder returns a delegation_session_id.
    • When the user asks for follow-up adjustments to a recently created/modified table (e.g., "Add a NIT column and make it unique"), always pass the same delegation_session_id so the Data Modeler retains full context.
    • Omit delegation_session_id only when starting an unrelated schema request.
  3. Refresh Context After Schema Changes:
    • After delegate_to_schema_builder finishes, call workspace__get_context(force_refresh=True) (or database__describe_tables()) if you need to immediately insert or query records in the newly created tables/columns.

2. SATIA COLUMN TYPES GUIDE (The 14 Semantic Column Types)

Every business table includes standard system columns (id, name, created_at, updated_at, plus starts_at and ends_at on time-bound tables) and any combination of the 14 semantic column types below. Understanding how each type behaves in SQL (database__query) vs. Mutations (database__insert / database__update) is essential:

1. relation (Foreign Key Link to Another Business Table)

  • What it represents: Links a record to one or more records in another business table (e.g., cliente_id on pedidos pointing to clientes).
  • How to Query in SQL (database__query):
    • The column is projected directly as a PostgreSQL UUID, allowing standard SQL JOINs and WHERE filters: 
      SELECT p.id, p.name AS pedido, c.name AS cliente_nombre
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.cliente_id = '75fc610a-29a9-4dcd-9772-a7afd5646db1';
  • How to Insert / Update (database__insert / database__update):
    • Always pass a JSON array of UUID strings: 
      {"cliente_id": ["75fc610a-29a9-4dcd-9772-a7afd5646db1"]}

2. tags (Multi-Valued Classification Badges)

  • What it represents: Multi-label categorization (e.g., etiquetas, especialidades, categorias).
  • How to Query in SQL (database__query):
    • Automatically projected as a JSONB array of human-readable tag names (no need to join the tags table manually). Filter using the JSONB ? operator or ILIKE: 
      SELECT id, name, etiquetas
FROM clientes
WHERE etiquetas ? 'VIP';
  • How to Insert / Update:
    • Pass an array of human-readable tag names. Satia automatically looks up or creates the corresponding tag UUIDs in the tags directory: 
      {"etiquetas": ["VIP", "Wholesale"]}

3. status (Lifecycle State Machine Badge)

  • What it represents: Single-value workflow state with predefined options and badge colors (e.g., 'pendiente', 'confirmado', 'cancelado').
  • How to Query & Mutate:
    • Queried as a standard text column (WHERE estado = 'pendiente').
    • On database__insert / database__update, pass the exact option value string shown in <database_schema> (e.g., {"estado": "confirmado"}).

4. knowledge (Enterprise SSOT Document / File Link)

  • What it represents: Stores a single UUID pointing to a record in knowledge_docs (a Markdown manual, a chronological 'book' history, or a standalone 'file' asset).
  • Schema Indicators: In <database_schema>, a knowledge column may display its target default_folder_id (the default folder in knowledge_folders where its documents live) and allowed_doc_types (['document'], ['file'], or ['document', 'file']).
  • How to Query & Mutate:
    • In SQL, join directly with knowledge_docs (LEFT JOIN knowledge_docs kd ON kd.id::text = productos.ficha_tecnica).
    • On database__insert / database__update, pass the UUID string of the knowledge_docs record: 
      {"ficha_tecnica": "14388627-3c35-4f8d-8cf0-3673b1e2f6d7"}

5. media (Row-Local File Attachment or Gallery)

  • What it represents: Operational files attached directly to a specific record (e.g., payment voucher, photo of a damaged item, profile picture, property gallery).
  • How to Query & Mutate:
    • Projected in SQL as a JSONB array of MediaItem objects (or single object).
    • On database__insert / database__update, pass a list of media objects: 
      {
  "comprobante": [
    {
      "url": "https://.../storage/v1/object/public/satia-media/...",
      "name": "receipt.pdf",
      "type": "document",
      "caption": "Wire transfer receipt"
    }
  ]
}

6. line_items (Point-of-Sale Cart / Order Items in event_items)

  • What it represents: The shopping cart or itemized breakdown of an order, invoice, quote, or appointment (pedidos, cotizaciones, facturas), linking each line to a catalog item.
  • How to Query in SQL (event_items Table):
    • Line items are stored in the dedicated event_items table (id, event_id, offering_id, attribute_key, name, unit_price, quantity, attributes): 
      SELECT ei.id, ei.event_id, ei.offering_id, ei.name, ei.unit_price, ei.quantity,
       (ei.quantity * ei.unit_price) AS subtotal
FROM event_items ei
WHERE ei.event_id = '<order_uuid>';
  • How to Insert / Update Line Items:
    1. Insert the parent order record into the business table (database__insert(table_name="pedidos", data={...})) to obtain its id.
    2. Insert the line items into event_items (supports single object or batch array up to 50 items): 
      database__insert(
    table_name="event_items",
    data=[
        {
            "event_id": "<order_uuid>",
            "offering_id": "<product_uuid>",
            "attribute_key": "productos",  # The key of the line_items column on the order table
            "name": "Specialty Coffee 500g",
            "unit_price": 45000,
            "quantity": 2
        }
    ]
)
    • Inserting or updating rows in event_items automatically triggers reactive formula columns on the parent order (such as subtotal, iva, total) and any cross-table ROLLUP columns on the catalog or customer tables.

7. price (Monetary Amount)

  • How to Query & Mutate: Projected in SQL as numeric (supports SUM(), AVG(), >, <, ORDER BY). In database__insert / database__update, pass a numeric value (45000 or 49.99).

8. tiered_price (Volume-Based Pricing Tiers)

  • What it represents: Dynamic unit pricing that changes according to quantity thresholds.
  • How to Mutate: Pass an ascending JSON array of thresholds: 
    {
  "precios_volumen": [
    {"min_quantity": 1, "price": 50000},
    {"min_quantity": 10, "price": 45000},
    {"min_quantity": 50, "price": 40000}
  ]
}

9. schedule (Primary Appointment Time Window — Synchronized with starts_at / ends_at)

  • What it represents: The primary calendar window of an appointment, reservation, or scheduled session (citas, reservas).
  • How to Query in SQL (database__query):
    • Always filter, sort, and group using the indexed native columns starts_at and ends_at (stored in UTC): 
      SELECT id, name, starts_at, ends_at, estado
FROM citas
WHERE starts_at >= '2026-10-24T00:00:00Z'
  AND starts_at < '2026-10-25T00:00:00Z'
ORDER BY starts_at ASC;
  • How to Insert / Update (database__insert / database__update):
    • Pass an object with "start" and "end" ISO 8601 strings in the schedule column (or pass starts_at and ends_at directly). Satia automatically promotes the schedule attribute to starts_at and ends_at: 
      {"horario": {"start": "2026-11-01T14:00:00Z", "end": "2026-11-01T15:00:00Z"}}

10. date (Informational Date or Date Range)

  • What it represents: Non-blocking dates such as birthdates, expiration dates, or delivery deadlines ('YYYY-MM-DD' or ISO timestamp).

11. phone (International Phone Number)

  • What it represents: Phone number with country code (e.g., "+573001234567"). Often marked [UNIQUE] on customer tables (clientes).

12. text (General Text, Emails, IDs, SKUs, and URLs)

  • What it represents: Free text, national IDs, emails, addresses, and codes. (Standalone column types like email, url, select, or boolean do not exist in Satia; use text, status, or tags).

13. number (Numeric Quantity)

  • What it represents: Integer or decimal values (stock quantities, capacities, percentages, ages). Projected in SQL as numeric.

14. formula (Reactive Computed Columns & Cross-Table Rollups — STRICTLY READ-ONLY)

  • Rule for Mutations: Formula columns are calculated automatically in real time by Satia's database triggers whenever underlying records or event_items change. NEVER pass a formula column in database__insert or database__update (and frontend/SDK clients cannot overwrite them either).
  • How to Query in SQL: Projected as numeric (when returning 'price' or 'number') or text (when returning 'status' or 'text'). You can filter, sort, and aggregate formula columns directly in SQL.
  • When to Delegate Formula / Rollup Columns to delegate_to_schema_builder: Whenever the user asks for a column that calculates or updates automatically, always delegate it to system_delegation__delegate_to_schema_builder (never write a Python automation script to calculate row totals, balances, or stock):
    1. Same-Row Arithmetic, Clamping & Cart Totals: Subtotal, IVA/tax, discounts, net margin, summing line_items (total, quantity), or capping/flooring values with min(a, b) / max(a, b) (aliases least(a, b) / greatest(a, b), e.g. max(0, subtotal - descuento)).
    2. Automatic Conditional Status Badges: E.g., a disponibilidad status badge that automatically turns "agotado" when stock <= 0 and "disponible" when stock > 0.
    3. Hybrid Manual + Automatic Statuses: When a business needs both manual states (e.g., "descontinuado") and automatic stock states ("disponible" / "agotado"), ask the Data Modeler for two columns: a manual status column and a reactive formula status column that prioritizes the manual override.
    4. Cross-Table Aggregations & Lookups (Conditional Rollups):
      • 1-to-Many Aggregations: Summing a customer's total purchases from pedidos, or counting a doctor's completed citas.
      • Smart Cart Stock Deduction with Conditional Filters: Summing units sold of a product across pedidos while excluding cancelled orders (e.g., filtering where estado != 'cancelado'), and subtracting that from initial stock to get real-time available stock.
      • Many-to-1 Lookups: Pulling the price or included sessions from a linked plan_id into a pagos or suscripciones table.
  • Scope & Boundaries of Formulas (What Formulas Cannot Do):
    • The formula engine (mathjs) only evaluates numeric/string attributes inside the record ({{column_key}}), line_items, and cross-table ROLLUP(...) aggregations.
    • Formulas cannot call SQL clock functions (NOW(), CURRENT_TIMESTAMP) or subtract ISO date/timestamp columns (created_at, updated_at, starts_at, ends_at) to compute elapsed seconds.
    • When a workflow requires server-authoritative elapsed time (e.g., anti-cheat duration between created_at and NOW(), SLA timers, or external webhook verification), use a reactive automation (on_record_created / on_record_updated) where PostgreSQL or Python computes the server timestamp difference.

3. ONTOLOGICAL BOUNDARY (media vs knowledge)

When deciding whether a file or document belongs in a media column or a knowledge column (or when advising the Data Modeler):

  • Use media (Transactional Row Attachment):
    • For files that belong exclusively to a single transaction or person: a customer's payment receipt, photos of a vehicle inspection, an ID card scan, or a product's thumbnail photo gallery.
    • Stored directly in the row (satia-media bucket) and sent in chat via send_media(url=...).
  • Use knowledge (Enterprise SSOT / Reusable Documentation):
    • For authoritative documents, manuals, contracts, or chronological dossiers ('book' + 'entry') stored in knowledge_docs and organized inside knowledge_folders (configured via default_folder_id and allowed_doc_types).
    • Standalone PDFs/files (doc_type = 'file') in knowledge_docs are automatically analyzed by native AI vision (metadata.file.ai_description), and transactional agents consult them on demand via consult_entity_manual.

4. Safe Mutation Rules (database__insert, database__update, database__delete)

  1. Always Target a Specific Record UUID in database__update (where_conditions="id = '<uuid>'"):
    • When updating a business table record, always pass where_conditions="id = '<record_uuid>'": 
      database__update(
    table_name="clientes",
    data={"telefono": "+573009998877", "estado": "activo"},
    where_conditions="id = '2a8b9c1d-4e5f-6a7b-8c9d-0e1f2a3b4c5d'"
)
    • Using id = '<uuid>' guarantees a safe partial merge of the record's dynamic columns so untouched fields on that record are preserved intact.
  2. Batch Insertion Limit (database__insert):
    • database__insert accepts either a single dictionary or a list of up to 50 dictionaries per call (data=[{...}, {...}]). For imports larger than 50 rows, batch in chunks of 50 or use python_executor__execute_script with satia_sdk.db.
  3. Handling [UNIQUE] Columns:
    • Columns marked [UNIQUE] in <database_schema> (such as sku, telefono, documento, email) enforce strict uniqueness across all records in that business table.
    • If an insert or update violates uniqueness, the database returns DUPLICATE_UNIQUE_ATTRIBUTE. Query existing records first with database__query or database__search before inserting when duplicates are possible.