> 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/nodes, affinity, member & location connection strength (resilience), connection-strength quadrants, attention signals (derived) |
| `compliance` | Compliance records                                                                                                                                 |
| `ingest`     | `raw_record` — the verbatim landing zone before transform                                                                                          |
| `mcp`        | The AI-collaboration layer (skills, memory, knowledge, artifacts, business context) 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.

> **Hide the books.** A connector can also be set to withhold the whole `accounting` schema — chart of accounts, journals, entries, invoice lines — regardless of its permission level. A query against any `accounting` table (including its `_guarded` view) then returns a clear denial, while `commerce` (sales, payments, refunds) stays readable. This is the orthogonal "hide the books" restriction — see [Permission levels](/connect-claude-and-access/scopes.md).

## 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).

### `people.member_status_event`

Append-only log of member **status** and **membership/plan** transitions. No booking or payment system emits a status-change event — each exposes only a current-state field — so Kula synthesises the event by detecting the change itself. This is the only place that answers "when did this member churn" or "was that inactive flag the vendor's or ours". Key columns:

`id`, `member_id`, `source`, `source_external_id`, `dimension` (`status` | `membership_type` | `plan` | `suspension`), `from_value`, `to_value`, `occurred_at`, `detected_at`, `is_baseline`, `reason`, `reason_source` (`rule` | `vendor` | `operator` | `baseline` | `unknown`), `actor`, `detail`.

> Two rules, or every number is wrong. **(1)** The log is live-forward only — it starts when capture began on the studio and cannot be backfilled — and each member has one `is_baseline = true` origin row per dimension recording what we first saw. Filter `NOT is_baseline` when counting changes. **(2)** It is one row per `people.member` **row**, not per human: group by `COALESCE(canonical_member_id, id)` or cross-source duplicates count several times. `list_member_status_changes` does both for you.

### `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.

## The `graph` schema (derived)

The `graph.*` tables are a **derived** layer, rebuilt from `people`, `bookings` and `commerce` — they hold no source data of their own. See [Graph & relationship tools](/for-developers/graph.md) for what the model means; the tables below are what you query directly. Person/location keys are **loose foreign keys** to `people.member.id` / `people.location.id`.

### `graph.member_resilience`

One row per (member, location). The connection-strength model.

`person_id`, `location_id`, `w_ml`, `w_peers`, `max_w_ms`, `resilience`, `activity_affinity`, `contract_commitment`, `resilience_constrained`, `vuln_quadrant` (`resilient_enthusiast` | `constrained_captive` | `flexible_explorer` | `specialist_nomad`), `computed_at`.

> `resilience_constrained` is the headline connection-strength score; `vuln_quadrant` is the activity-affinity × contract-commitment taxonomy.

### `graph.member_affinity`

One row per member — the routing rollup.

`person_id`, `primary_staff_id`, `top3_staff_ids`, `staff_concentration_pct`, `primary_edge_weight`, `primary_edge_tier`, `computed_at`.

### `graph.staff_concentration`

One row per staff member. `primary_member_count` is routing value — how many members' connections are anchored on this staff member.

`staff_id`, `primary_member_count`, `total_member_edges`, `avg_edge_weight`, `computed_at`.

### `graph.location_resilience`

One row per location — operational resilience.

`location_id`, `ops_resilience`, `cover_depth`, `staff_count`, `single_points_of_failure`, `computed_at`.

### `graph.risk_signal`

The attention-signal queue — the sense layer, distinct from the connection- strength scores above. The open queue is `WHERE resolved_at IS NULL`.

`id`, `person_id`, `node_id`, `signal_type` (`drift` | `freq_softening` | `missed_second_visit` | `milestone` | `pause_drift` | `at_risk_threshold`), `severity` (`low` | `medium` | `high`), `detected_at`, `context` (`jsonb` — supporting facts and routing), `resolved_at`, `resolution`.

### `graph.node` and `graph.edge`

The underlying entities and relationships. `graph.node` carries one row per entity (`entity_type`, `entity_id`, `display_label`); `graph.edge` carries the weighted relationships (`from_node_id`, `to_node_id`, `kind`, `edge_class` MS/ML/SL/MM/SS, `weight`, `tier`, `last_observed_at`, `status`). Prefer the rollups above and `get_entity_edges` for most questions.

## 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.