SDK API And Backend Contract

This page maps the SDK objects to the TS Manager backend routes.

MetaTables use the backend route namespace:

/orm/api/ts_manager/meta_table/

Use the MetaTable route namespace for registration, validation, introspection, and compiled execution.

SDK Modules

Transport models:

from mainsequence.client import MetaTable
from mainsequence.client.metatables import (
    MetaTableCompiledSQLOperation,
    MetaTableContract,
    MetaTableRegistrationRequest,
    MetaTableValidateContractRequest,
)

SQLAlchemy helpers:

from mainsequence.meta_tables import (
    AlembicMetaTableMigration,
    AlembicVersionMetaTable,
    PlatformManagedMetaTable,
    external_registered_registration_request_from_sqlalchemy_model,
    register_external_sqlalchemy_model,
    schema_table_name,
    sqlalchemy_naming_convention,
)
from mainsequence.meta_tables.compiled_sql.v1 import compile_sqlalchemy_statement

SQLAlchemy is a core SDK dependency for MetaTable declarations. The SDK uses it to inspect table contracts, compile governed SQL operations, and define the provider-based Alembic migration scope used by MetaTable schema migrations.

Registration Primitive

Low-level SDK call:

meta_table = MetaTable.register(request)

For platform-managed SQLAlchemy models this is SDK plumbing used by the migration workflow, not a bootstrap call users should place in application code. External-registered callers may still build a request and call this primitive directly when the application owns the physical table lifecycle.

Backend route:

POST /orm/api/ts_manager/meta_table/register/

Request fields:

For platform_managed, table_contract.physical.table_name is the authored SQLAlchemy table name that Alembic sees. The platform identity is the MetaTable uid; the stable logical application identity is identifier when provided.

PlatformManagedMetaTable exists so SQLAlchemy table construction and migration-managed registration produce the same table contract while preserving the authored physical table name. Prefix explicit table names with the project or package name. Use schema_table_name(project_or_app, concept) and sqlalchemy_naming_convention() to keep authored table, index, constraint, and Alembic version names collision-resistant and within PostgreSQL identifier limits.

If a caller needs a deterministic contract fingerprint, use compute_metatable_contract_hash(model_or_table). It is an explicit utility, not a MetaTable API field, and it includes the physical table name by default.

Contract Validation

Validate a new contract before registration:

result = MetaTable.validate_contract(
    table_contract=request.table_contract,
    management_mode=request.management_mode,
)

Backend route:

POST /orm/api/ts_manager/meta_table/validate-contract/

Validate a replacement contract for an existing table:

result = meta_table.validate_existing_contract(
    table_contract=request.table_contract,
)

Backend route:

POST /orm/api/ts_manager/meta_table/<uid>/validate-contract/

Validation normalizes column, index, and foreign-key fragments and returns the normalized contract. It does not import application code.

Schema Graph

SDK call for outgoing relationships:

graph = meta_table.get_schema_graph(depth=1)

SDK call for outgoing and incoming relationships:

graph = meta_table.get_schema_graph(depth=1, include_incoming=True)

Backend route:

GET /orm/api/ts_manager/meta_table/<uid>/schema-graph/?depth=<n>&include_incoming=<bool>

Use this graph for dependency analysis. Graph edges include both source_uid and target_uid, which is the shape needed to identify dependent MetaTables.

Return shape:

{
    "root_uid": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
    "depth": 1,
    "include_incoming": True,
    "nodes": [...],
    "edges": [
        {
            "name": "fk_account_holdings_asset",
            "source_uid": "cccccccc-cccc-4ccc-8ccc-cccccccccccc",
            "target_uid": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
            "source_columns": ["asset_uid"],
            "target_columns": ["uid"],
            "on_delete": "restrict",
            "relationship_type": "meta_table_to_meta_table",
        }
    ],
}

For inbound dependencies:

incoming_edges = [
    edge for edge in graph["edges"] if edge["target_uid"] == meta_table.uid
]
dependent_table_uids = [edge["source_uid"] for edge in incoming_edges]

Use depth=1 when you only need direct dependents. Increase depth when you need transitive relationship context; the backend enforces its supported maximum.

Introspection

SDK call:

result = meta_table.introspect()
snapshot = result["introspection_snapshot"]

Backend route:

POST /orm/api/ts_manager/meta_table/<uid>/introspect/

The backend stores the snapshot on MetaTable.introspection_snapshot. The current PostgreSQL path returns schema, table name, columns, indexes, and constraints when the selected data source supports table introspection.

Compiled Execution

SDK call:

result = MetaTable.execute_operation(operation)

Backend route:

POST /orm/api/ts_manager/meta_table/execute-operation/

Execution expects a compiled-sql.v1 operation:

operation = compile_sqlalchemy_statement(
    stmt,
    operation="select",
    data_source_uid=data_source.uid,
    scope_tables=[
        {"metaTableUid": asset_meta_table.uid, "alias": "asset", "access": "read"},
        {"metaTableUid": account_meta_table.uid, "alias": "account", "access": "read"},
    ],
    limits={"max_rows": 1000, "statement_timeout_ms": 15000},
)
result = MetaTable.execute_operation(operation)

The SDK accepts metaTableUid and meta_table_uid when building scope objects. The serialized backend payload uses meta_table_uid. If scope.data_source_uid is omitted, the SDK resolves the configured project/session default data source before sending the request.

Backend checks include:

• every scoped MetaTable is visible to the caller
• write operations require edit access
• the selected data source supports the requested compiled operation
• version is compiled-sql.v1
• dialect is postgresql
• statement.paramstyle is pyformat
• the SQL is one statement
• the SQL statement type matches operation

The response shape is:

{
  "ok": true,
  "operation": "select",
  "dialect": "postgresql",
  "row_count": 25,
  "rows": [
    {
      "uid": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
      "symbol": "BTC"
    }
  ],
  "truncated": false,
  "max_rows": 1000,
  "pagination": {
    "limit": 1000,
    "offset": 0,
    "returned_count": 1,
    "has_more": false,
    "next_offset": null
  },
  "scope": {
    "meta_table_uids": ["aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa"],
    "data_source_uid": "dddddddd-dddd-4ddd-8ddd-dddddddddddd"
  }
}

Rows are returned only for statements that produce rows. For result sets larger than one backend page, MetaTable.execute_operation(...) follows pagination.next_offset automatically for select operations until it has returned the requested limits.max_rows rows or pagination.has_more is false. Include a deterministic ORDER BY in paginated select SQL.

Migration Execution

MetaTable schema migrations are executed by Alembic. The SDK migration CLI is a provider adapter that:

1. loads the selected AlembicMetaTableMigration provider;
2. asks the backend for a temporary migration URI when a command needs database access and no explicit SQLAlchemy URL was supplied;
3. for current, upgrade, and downgrade, registers or resolves its AlembicVersionMetaTable;
4. for upgrade and downgrade, creates or resolves provider platform-managed MetaTable rows before Alembic runs and finalizes them after successful DDL;
5. calls Alembic current, revision, upgrade, or downgrade directly.

Backend coordination uses:

POST /orm/api/ts_manager/meta_table/
POST /orm/api/ts_manager/dynamic_table/
POST /orm/api/ts_manager/dynamic_table_data_source/<uid>/migration-connection/
POST /orm/api/ts_manager/meta_table/finalize-managed/

The migration-connection request does not include MetaTable UIDs; the provider and namespace define the migration context. The backend does not receive an SDK-rendered SQL artifact and does not execute a client-defined migration operation. After a successful Alembic upgrade, the CLI finalizes provider MetaTable catalog rows through finalize-managed/.

Backend Capabilities

The selected DynamicTableDataSource must expose the capability required by the requested operation:

If a capability is missing, the backend returns a structured error instead of falling back to direct database access.

This is the main platform-managed tradeoff: users get governed table creation and execution without direct database credentials, but only for operations that the selected data source explicitly supports.

Search And Discovery

After registration, TS Manager stores:

• the MetaTable row
• normalized MetaTableColumn rows
• normalized MetaTableIndex rows
• normalized foreign-key projection rows
• an optional introspection_snapshot
• a TableSearchIndex document

Command Center and table discovery read those MetaTable records directly.