> For the complete documentation index, see [llms.txt](https://docs.kula.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kula.digital/for-developers/schema.md).

# Schema reference

This is the data model the freeform query tools (`execute_query`, `build_query`) read. It documents the **schema families** and the shape of the core tables. For exact, live column lists, use `list_tables` and `get_table_schema` against your own studio — they are the source of truth, and this page mirrors them.

> Every studio's database carries the same shape, populated from whichever [connectors](/your-data-sources/connectors.md) it has. A table is empty, not absent, if you haven't connected a source that feeds it.

## How the model is organised

Tables live under a small set of schemas, all on the default `search_path`:

| Schema       | Holds                                                                                                                      |
| ------------ | -------------------------------------------------------------------------------------------------------------------------- |
| `people`     | Members, staff, companies, locations, identity links                                                                       |
| `commerce`   | Sales, payments, refunds, plans, products                                                                                  |
| `bookings`   | Class sessions, attendance, appointments, facility entries                                                                 |
| `accounting` | Chart of accounts, journals, entries, invoice lines                                                                        |
| `marketing`  | Ad accounts, campaigns, daily spend, attribution links                                                                     |
| `comms`      | Messages and attachments                                                                                                   |
| `graph`      | Relationship edges, affinity, risk signals, staff concentration                                                            |
| `compliance` | Compliance records                                                                                                         |
| `ingest`     | `raw_record` — the verbatim landing zone before transform                                                                  |
| `mcp`        | The AI-collaboration layer (skills, memory, knowledge, artifacts, business context, ignition state) and ingest bookkeeping |

## The canonical column pattern

Most fact tables are **source-stamped and event-sourced**, so they share a common envelope of columns:

| Column                      | Meaning                                                                   |
| --------------------------- | ------------------------------------------------------------------------- |
| `id`                        | Canonical UUID for the row.                                               |
| `source`                    | The connector that produced it (e.g. `com.stripe`, `com.wix`).            |
| `source_external_id`        | The provider's own id for the record.                                     |
| `site_id`                   | The location/sub-tenant within the studio, if any.                        |
| `occurred_at`               | When the event actually happened (not when we received it).               |
| `source_extras`             | `jsonb` — provider fields we kept verbatim but didn't promote to columns. |
| `source_modified_at`        | When the provider last changed it.                                        |
| `created_at` / `updated_at` | When Kula first/last wrote the row.                                       |
| `ingest_event_id`           | The idempotency key from the ingest envelope.                             |

This means you can always trace a canonical row back to the exact provider record it came from.

## PII redaction and the `_guarded` companions

Many base tables have a **`_guarded` companion** (for example, `commerce.sale` and `commerce.sale_guarded`). The guarded view applies **redaction based on your token's** [**permission level**](/connect-claude-and-access/scopes.md) — hiding member names, contact details, or payment fragments you aren't entitled to. Redaction happens server-side, so a lower level can't be tricked into revealing more.

Prefer the guarded companion (or the purpose-built read tools like `get_member_payments` and `get_member_plan_status`, which honour scoping automatically) when you want redaction handled for you.

## Core tables

### `people.member`

One row per member. Key columns:

`id`, `first_name`, `last_name`, `display_name`, `email`, `mobile_phone`, `status`, `membership_type`, `plan_name`, `current_plan_id`, `is_booking_suspended`, `suspension_start_date`, `suspension_end_date`, `member_since`, `first_session_date`, `home_location_id`, `home_location_name` — plus the canonical envelope columns.

> `email`, `mobile_phone`, and the suspension dates redact below the `admin` level; names redact below `operations`. See [permission levels](/connect-claude-and-access/scopes.md).

### `commerce.sale`

One row per sale line (refunds are negative companions in `commerce.refund`). Key columns:

`id`, `occurred_at`, `amount`, `tax`, `total`, `discount`, `currency`, `quantity`, `item_type`, `item_id`, `item_name`, `item_category`, `description`, `member_id`, `staff_id`, `location_id`, `payment_method`, `payment_status`, `payment_provider`, `provider_transaction_id`, `transaction_id`, `transaction_status`, `payment_last4`, `reason_code`, `billing_email`, `is_recurring`, `installments` — plus the envelope columns.

> Amounts and classification are visible at `operations`; `billing_email` at `admin`; `payment_last4` and provider transaction ids only at `full`.

### `bookings.attendance`

One row per booking/visit. Key columns:

`id`, `class_session_id`, `member_id`, `occurred_at`, `booking_status`, `booked_at`, `checked_in_at`, `cancelled_at`, `cancellation_window_minutes`, `sale_source_external_id`, `plan_source_external_id` — plus the envelope columns.

> `sale_source_external_id` and `plan_source_external_id` are what the diagnostic uses to connect each attendance to the plan and sale behind it.

## Getting the full, live picture

This page documents the shape; your studio's database is authoritative.

```
list_tables                      → every table/view on your search_path
get_table_schema("commerce.sale") → exact columns, types, and redaction state
get_semantic_catalogue            → human descriptions + PII classification
                                    + your studio's business context
```

Because the guarded views and the read tools enforce scoping, you can query freely with `execute_query` (SELECT/`WITH` only) without having to track the column-level PII map yourself.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.kula.digital/for-developers/schema.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.