Tables

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.

POST

tables

from-sql

curl --request POST \
  --url https://api.casa-layer.com/v1/tables/from-sql \
  --header 'Authorization: Bearer <token>' \
  --header 'Content-Type: application/json' \
  --data '
{
  "name": "<string>",
  "sql": "<string>",
  "description": "<string>"
}
'

import requests

url = "https://api.casa-layer.com/v1/tables/from-sql"

payload = {
    "name": "<string>",
    "sql": "<string>",
    "description": "<string>"
}
headers = {
    "Authorization": "Bearer <token>",
    "Content-Type": "application/json"
}

response = requests.post(url, json=payload, headers=headers)

print(response.text)

const options = {
  method: 'POST',
  headers: {Authorization: 'Bearer <token>', 'Content-Type': 'application/json'},
  body: JSON.stringify({name: '<string>', sql: '<string>', description: '<string>'})
};

fetch('https://api.casa-layer.com/v1/tables/from-sql', options)
  .then(res => res.json())
  .then(res => console.log(res))
  .catch(err => console.error(err));

<?php

$curl = curl_init();

curl_setopt_array($curl, [
  CURLOPT_URL => "https://api.casa-layer.com/v1/tables/from-sql",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => json_encode([
    'name' => '<string>',
    'sql' => '<string>',
    'description' => '<string>'
  ]),
  CURLOPT_HTTPHEADER => [
    "Authorization: Bearer <token>",
    "Content-Type: application/json"
  ],
]);

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

package main

import (
	"fmt"
	"strings"
	"net/http"
	"io"
)

func main() {

	url := "https://api.casa-layer.com/v1/tables/from-sql"

	payload := strings.NewReader("{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}")

	req, _ := http.NewRequest("POST", url, payload)

	req.Header.Add("Authorization", "Bearer <token>")
	req.Header.Add("Content-Type", "application/json")

	res, _ := http.DefaultClient.Do(req)

	defer res.Body.Close()
	body, _ := io.ReadAll(res.Body)

	fmt.Println(string(body))

}

HttpResponse<String> response = Unirest.post("https://api.casa-layer.com/v1/tables/from-sql")
  .header("Authorization", "Bearer <token>")
  .header("Content-Type", "application/json")
  .body("{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}")
  .asString();

require 'uri'
require 'net/http'

url = URI("https://api.casa-layer.com/v1/tables/from-sql")

http = Net::HTTP.new(url.host, url.port)
http.use_ssl = true

request = Net::HTTP::Post.new(url)
request["Authorization"] = 'Bearer <token>'
request["Content-Type"] = 'application/json'
request.body = "{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}"

response = http.request(request)
puts response.read_body

{
  "data": {
    "table": {
      "id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "property_group_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "name": "<string>",
      "type": "static",
      "member_type": "master",
      "status": "active",
      "member_count": 123,
      "created_at": "2023-11-07T05:31:56Z",
      "updated_at": "2023-11-07T05:31:56Z",
      "description": "<string>",
      "created_by": "<string>",
      "last_refreshed_at": "2023-11-07T05:31:56Z",
      "latest_run_status": "running"
    },
    "filters": {
      "id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "table_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "property_group_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "filter_doc": {},
      "revision": 123,
      "created_at": "2023-11-07T05:31:56Z",
      "updated_at": "2023-11-07T05:31:56Z"
    }
  }
}

{
  "error": "invalid_cursor"
}

{
  "error": "unauthorized"
}

{
  "error": "forbidden",
  "scope": "read:guests"
}

{
  "error": "<string>",
  "scope": "<string>",
  "detail": "<string>"
}

Authorizations

Authorization

string

header

required

Authorization: Bearer <token>. 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.

Body

application/json

name

string

required

sql

string

required

A single SELECT over guest_profiles or master_profiles with a WHERE clause.

description

string

member_type

enum<string>

Overrides the member type derived from the query's FROM table.

Available options:

master,

guest

Response

Created table and its saved filter row.

data

object

required

Show child attributes

Save a SQL query as a dynamic table

curl --request POST \
  --url https://api.casa-layer.com/v1/tables/from-sql \
  --header 'Authorization: Bearer <token>' \
  --header 'Content-Type: application/json' \
  --data '
{
  "name": "<string>",
  "sql": "<string>",
  "description": "<string>"
}
'

import requests

url = "https://api.casa-layer.com/v1/tables/from-sql"

payload = {
    "name": "<string>",
    "sql": "<string>",
    "description": "<string>"
}
headers = {
    "Authorization": "Bearer <token>",
    "Content-Type": "application/json"
}

response = requests.post(url, json=payload, headers=headers)

print(response.text)

const options = {
  method: 'POST',
  headers: {Authorization: 'Bearer <token>', 'Content-Type': 'application/json'},
  body: JSON.stringify({name: '<string>', sql: '<string>', description: '<string>'})
};

fetch('https://api.casa-layer.com/v1/tables/from-sql', options)
  .then(res => res.json())
  .then(res => console.log(res))
  .catch(err => console.error(err));

<?php

$curl = curl_init();

curl_setopt_array($curl, [
  CURLOPT_URL => "https://api.casa-layer.com/v1/tables/from-sql",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => json_encode([
    'name' => '<string>',
    'sql' => '<string>',
    'description' => '<string>'
  ]),
  CURLOPT_HTTPHEADER => [
    "Authorization: Bearer <token>",
    "Content-Type: application/json"
  ],
]);

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

package main

import (
	"fmt"
	"strings"
	"net/http"
	"io"
)

func main() {

	url := "https://api.casa-layer.com/v1/tables/from-sql"

	payload := strings.NewReader("{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}")

	req, _ := http.NewRequest("POST", url, payload)

	req.Header.Add("Authorization", "Bearer <token>")
	req.Header.Add("Content-Type", "application/json")

	res, _ := http.DefaultClient.Do(req)

	defer res.Body.Close()
	body, _ := io.ReadAll(res.Body)

	fmt.Println(string(body))

}

HttpResponse<String> response = Unirest.post("https://api.casa-layer.com/v1/tables/from-sql")
  .header("Authorization", "Bearer <token>")
  .header("Content-Type", "application/json")
  .body("{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}")
  .asString();

require 'uri'
require 'net/http'

url = URI("https://api.casa-layer.com/v1/tables/from-sql")

http = Net::HTTP.new(url.host, url.port)
http.use_ssl = true

request = Net::HTTP::Post.new(url)
request["Authorization"] = 'Bearer <token>'
request["Content-Type"] = 'application/json'
request.body = "{\n  \"name\": \"<string>\",\n  \"sql\": \"<string>\",\n  \"description\": \"<string>\"\n}"

response = http.request(request)
puts response.read_body

{
  "data": {
    "table": {
      "id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "property_group_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "name": "<string>",
      "type": "static",
      "member_type": "master",
      "status": "active",
      "member_count": 123,
      "created_at": "2023-11-07T05:31:56Z",
      "updated_at": "2023-11-07T05:31:56Z",
      "description": "<string>",
      "created_by": "<string>",
      "last_refreshed_at": "2023-11-07T05:31:56Z",
      "latest_run_status": "running"
    },
    "filters": {
      "id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "table_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "property_group_id": "3c90c3cc-0d44-4b50-8888-8dd25736052a",
      "filter_doc": {},
      "revision": 123,
      "created_at": "2023-11-07T05:31:56Z",
      "updated_at": "2023-11-07T05:31:56Z"
    }
  }
}

{
  "error": "invalid_cursor"
}

{
  "error": "unauthorized"
}

{
  "error": "forbidden",
  "scope": "read:guests"
}

{
  "error": "<string>",
  "scope": "<string>",
  "detail": "<string>"
}

API documentation

Endpoints

Save a SQL query as a dynamic table

Authorizations

Body

Response