Schema reference

The tables and columns behind the freeform query tools — schema families, the canonical column pattern, and how PII redaction works per permission level.

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 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 resilience, vulnerability quadrants, at-risk 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, 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 — 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.

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.

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 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 retention 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 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 both routing value and defection risk.

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 at-risk queue. 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.