# Tables Overview

Tables define your data models. Each table is a distinct entity (users, products, orders) whose `fields` declare the columns that records can store. Tables are the foundation of a Sovrium app: pages render their records, forms write to them, automations react to their changes, and the REST/MCP APIs expose them.

A table has at minimum an `id`, a `name`, and a `fields` array. Everything else — primary keys, indexes, constraints, views, permissions, webhooks, and AI exposure — is optional and layered on top.

```yaml
tables:
  - id: 1
    name: Contacts
    fields:
      - { id: 1, name: email, type: email, required: true, unique: true }
      - { id: 2, name: full_name, type: single-line-text, required: true }
      - { id: 3, name: created_at, type: created-at, indexed: true }
```

## Table Properties

Each entry in the `tables` array accepts the following properties.

| Property              | Description                                                                                                                                       |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `id`                  | Unique integer identifier for the table. Auto-generated when omitted.                                                                             |
| `name`                | User-friendly table name. Can contain spaces and mixed case; sanitized for database use (lowercase with underscores). Maximum 63 characters.      |
| `fields`              | Array of field definitions. At least one field is required. Field names and IDs must be unique within the table.                                  |
| `primaryKey`          | Primary key configuration. Defaults to an auto-generated `id` column. See [Indexes & Constraints](/en/docs/table-indexes-constraints).            |
| `unique`              | Array of top-level unique-constraint declarations. Each entry covers one or more fields (e.g. `[{ fields: [slug] }]`).                            |
| `indexes`             | Array of index definitions for query performance and uniqueness enforcement.                                                                      |
| `uniqueConstraints`   | _(via `unique`)_ Multi-field uniqueness. Single-field entries fold into `field.unique`; composite entries become unique btree indexes.            |
| `foreignKeys`         | Composite (multi-column) foreign key definitions. Single-column foreign keys are created automatically from `relationship` fields.                |
| `constraints`         | Array of CHECK constraints with SQL expressions for cross-field data validation.                                                                  |
| `views`               | Saved views with pre-configured filters, sorting, grouping, and visible fields. See [Views](/en/docs/table-views).                                |
| `permissions`         | RBAC permissions controlling create, read, update, delete, restore, comment, and per-field access. See [Permissions](/en/docs/table-permissions). |
| `rowLevelPermissions` | Server-side `when` predicates per CRUD operation for defense-in-depth row scoping.                                                                |
| `webhooks`            | Outgoing webhooks fired on record create/update/delete events. See [Webhooks](/en/docs/table-webhooks).                                           |
| `comments`            | Comment-system configuration (guest commenting, moderation, threading).                                                                           |
| `aiAccess`            | Declares the table eligible for exposure via Sovrium's MCP server so AI assistants can list/read/write records (subject to RBAC).                 |
| `allowDestructive`    | Boolean. When `true`, allows destructive schema migrations (column drops, type changes). Defaults to `false`.                                     |
| `allowForceDelete`    | Boolean. Per-table opt-in permitting hard-delete via the admin dashboard (GDPR erasure). Defaults to `false`. See note below.                     |

:::callout
**Soft-delete by default.** Deleting a record sets `deleted_at`/`deleted_by` and leaves the row recoverable via `/restore`. Set `allowForceDelete: true` only when a table needs irreversible hard-delete (e.g. GDPR right-to-erasure on an HR table). Financial ledgers and audit-adjacent tables should leave it at the `false` default — the admin force-delete endpoint returns `404` when force-delete is not allowed.
:::

## Base Field Properties

Every field — regardless of type — extends a common base. These properties are available on all 49 field types.

| Property       | Description                                                                                                                                     |
| -------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| `id`           | Unique integer identifier for the field within the table. Auto-generated sequentially when omitted.                                             |
| `name`         | Column identifier. Must start with a letter and contain only lowercase letters, digits, and underscores (`^[a-z][a-z0-9_]*`). Maximum 63 chars. |
| `type`         | One of the 49 available field types (e.g. `single-line-text`, `integer`, `relationship`). See [Field Types](/en/docs/field-types-overview).     |
| `required`     | Boolean. When `true`, the field must have a value for every record.                                                                             |
| `unique`       | Boolean. When `true`, no two records can share the same value.                                                                                  |
| `indexed`      | Boolean. When `true`, creates a database index on this field for faster queries.                                                                |
| `searchWeight` | PostgreSQL full-text-search relevance weight: `A` (highest) to `D` (lowest). Only effective when `indexed: true` and the data source uses FTS.  |
| `storage`      | Storage configuration object. `storage.compression` (boolean) enables compression for large text or attachment values.                          |

:::callout
**`default` is type-specific, not a base property.** Most value-carrying fields (text, numeric, selection, date, color, …) expose a `default` whose value type matches the field. System fields (`created-at`, relational, computed, AI) do not. Each field-type page lists whether `default` is available.
:::

### Example: a richer table

```yaml
tables:
  - id: 2
    name: Products
    fields:
      - { id: 1, name: sku, type: single-line-text, required: true, unique: true }
      - {
          id: 2,
          name: title,
          type: single-line-text,
          required: true,
          searchWeight: A,
          indexed: true,
        }
      - { id: 3, name: description, type: long-text, searchWeight: B }
      - { id: 4, name: price, type: currency, required: true, currency: USD }
      - { id: 5, name: in_stock, type: checkbox, default: true }
    primaryKey: { type: auto-increment, field: id }
    indexes:
      - { name: idx_products_sku, fields: [sku], unique: true }
    permissions:
      read: all
      create: [admin, editor]
      update: [admin, editor]
      delete: [admin]
```

## Related Pages

- [Field Types Overview](/en/docs/field-types-overview) — all 49 field types by category.
- [Permissions](/en/docs/table-permissions) — RBAC and per-field access control.
- [Indexes & Constraints](/en/docs/table-indexes-constraints) — primary keys, indexes, CHECK constraints.
- [Relationships](/en/docs/table-relationships) — linking tables together.
- [Views](/en/docs/table-views) — saved filters, sorts, and grouping.
- [Validation](/en/docs/table-validation) — field- and table-level validation rules.
- [Webhooks](/en/docs/table-webhooks) — outgoing HTTP on record events.