> ## Documentation Index > Fetch the complete documentation index at: https://docs.casa-layer.com/llms.txt > Use this file to discover all available pages before exploring further. # Save a SQL query as a dynamic table > Converts one read-only `SELECT` over `guest_profiles` or `master_profiles` into a dynamic table: the `WHERE` clause becomes the saved filter document, so the table refreshes through the same evaluator and reconcile workflow as any other dynamic table. Select list, aggregations, GROUP BY, ORDER BY, LIMIT, and OFFSET are dropped; only the WHERE clause carries over. Queries the filter model cannot express (joins, unknown columns, unsupported predicates) return 422 `sql_not_convertible`. Requires scope `write:tables`. ## OpenAPI ````yaml /api-reference/openapi.yaml post /v1/tables/from-sql openapi: 3.1.0 info: title: Casa API version: 1.0.0 description: > Headless, read-only access to the guest context layer — the entire canonical data model over HTTP. Every endpoint is **scoped by the credential**: the default scope is the caller's property group, returning data across all of its properties. Auth sources resolve to the same credential: a user-generated **API key** (`casa_…`, created in Account settings — the recommended method for machines), a WorkOS AuthKit session JWT (the web app; org and role come from verified token claims), or a static token. (The MCP server also accepts read-only OAuth connector tokens, but those are rejected on these REST routes.) Send `X-Property-Id` to drill down into a single property; API keys can also be pinned to one property at creation. Tenant isolation holds by construction. Integration-management endpoints write connection *metadata* only and are session-only; canonical guest data enters exclusively through the connector workers and the ingest pipeline. The Model Context Protocol (MCP) server is exposed separately at `/mcp` and is documented under **MCP Server** in the guides — it is not a REST endpoint and is intentionally omitted from this reference. It accepts the same API keys. servers: - url: https://api.casa-layer.com description: Production Cloudflare Worker security: - bearerAuth: [] tags: - name: System description: Liveness, health, and the public welcome page. - name: Guests description: Canonical guest profiles as ingested from each source system. - name: Master Profiles description: >- Identity-resolved golden records — one entity per real guest, merged across sources and properties. - name: Reservations description: >- Reservation records linked to guest profiles, searchable by status, stay window, and channel. - name: Transactions description: >- Revenue lines (Pace Transactions V2–shaped) linked to guests and optionally reservations. - name: Consents description: > Contact-grain consent observations (contact method + value × purpose). Rows store decisive signals only (granted/withdrawn/denied); absence of a row means unknown / not eligible. Effective eligibility within a property group is the most restrictive status among stored observations for the same contact×purpose (withdrawn/denied beat granted). - name: Tables description: > Attio-style ops tables — curated or filter-defined containers for day-to-day work inside Casa. Tables are not consent eligibility records and not marketing segments; campaign audiences stay in downstream ESP tools (Klaviyo, Mailchimp, and peers). - name: Actions description: Guest actions (purchases, visits, and other tracked events). - name: Events description: >- The append-only guest event log — full change history or latest delta slice per entity. - name: Reviews description: Guest reviews with free-text search and an aggregate summary. - name: Loyalty description: Loyalty programs and member search (tier, points, guest). - name: Dimensions description: Reference dimensions — booking channels and bookable spaces. - name: Properties description: >- Properties within a group, plus self-serve onboarding (org-anchored tenant bootstrap). - name: SQL description: >- Read-only SQL over the public data model, isolated per tenant by row-level security. - name: API Keys description: Manage the organization's API keys (session-only). - name: Integrations description: >- Connection metadata for source systems (Mews, Square, …). Metadata only — never canonical guest data. Session-only. paths: /v1/tables/from-sql: post: tags: - Tables summary: Save a SQL query as a dynamic table description: > Converts one read-only `SELECT` over `guest_profiles` or `master_profiles` into a dynamic table: the `WHERE` clause becomes the saved filter document, so the table refreshes through the same evaluator and reconcile workflow as any other dynamic table. Select list, aggregations, GROUP BY, ORDER BY, LIMIT, and OFFSET are dropped; only the WHERE clause carries over. Queries the filter model cannot express (joins, unknown columns, unsupported predicates) return 422 `sql_not_convertible`. Requires scope `write:tables`. requestBody: required: true content: application/json: schema: type: object required: - name - sql properties: name: type: string description: type: string sql: type: string description: >- A single SELECT over guest_profiles or master_profiles with a WHERE clause. member_type: type: string enum: - master - guest description: >- Overrides the member type derived from the query's FROM table. responses: '201': description: Created table and its saved filter row. content: application/json: schema: type: object required: - data properties: data: type: object required: - table - filters properties: table: $ref: '#/components/schemas/Table' filters: $ref: '#/components/schemas/TableFilter' '400': $ref: '#/components/responses/BadRequest' '401': $ref: '#/components/responses/Unauthorized' '403': $ref: '#/components/responses/Forbidden' '422': description: The SQL cannot be expressed as saved table filters. content: application/json: schema: $ref: '#/components/schemas/Error' components: schemas: Table: type: object description: > An ops table container. Tables are not consent records and not marketing segments. required: - id - property_group_id - name - type - member_type - status - member_count - created_at - updated_at properties: id: type: string format: uuid property_group_id: type: string format: uuid name: type: string description: type: - string - 'null' type: type: string enum: - static - dynamic member_type: type: string enum: - master - guest status: type: string enum: - active - archived created_by: type: - string - 'null' last_refreshed_at: type: - string - 'null' format: date-time member_count: type: integer latest_run_status: type: - string - 'null' enum: - running - succeeded - failed - null created_at: type: string format: date-time updated_at: type: string format: date-time TableFilter: type: object required: - id - table_id - property_group_id - filter_doc - revision - created_at - updated_at properties: id: type: string format: uuid table_id: type: string format: uuid property_group_id: type: string format: uuid filter_doc: type: object additionalProperties: true revision: type: integer created_at: type: string format: date-time updated_at: type: string format: date-time Error: type: object required: - error properties: error: type: string description: Machine-readable error code. scope: type: string description: The scope that was required when the error is a scope failure.: null detail: type: string description: Optional human-readable detail. responses: BadRequest: description: >- Malformed request — e.g. a `cursor` that is not a valid pagination token. content: application/json: schema: $ref: '#/components/schemas/Error' example: error: invalid_cursor Unauthorized: description: Missing, unknown, or revoked bearer credential. content: application/json: schema: $ref: '#/components/schemas/Error' example: error: unauthorized Forbidden: description: > The credential lacks the scope required for this endpoint, or the auth surface is wrong. Machine-readable codes include `forbidden` (scope or role), `tenant_not_provisioned` (org claim present but no property group), and `mcp_oauth_required` (SPA session token used on `/mcp`). content: application/json: schema: $ref: '#/components/schemas/Error' example: error: forbidden scope: read:guests securitySchemes: bearerAuth: type: http scheme: bearer description: > `Authorization: Bearer `. Token types resolve to the same scoped credential: a user-generated **API key** (`casa_…`, from Account settings — recommended for machines and MCP clients), a WorkOS AuthKit session JWT (the web app; org and role come from verified token claims), or a static token. API keys are group-scoped, optionally pinned to one property at creation. MCP OAuth connector tokens authenticate the MCP server only and are rejected on these REST routes. ````