Appendix D: API Reference

This appendix provides a comprehensive reference for all Cognica APIs, including PostgreSQL wire protocol, Apache Arrow Flight SQL, and HTTP REST endpoints. Cognica's multi-protocol architecture enables integration with diverse client ecosystems while maintaining consistent semantics across all interfaces.

D.1 API Architecture Overview

Cognica exposes its functionality through three protocol interfaces:

Protocol Characteristics:

D.2 PostgreSQL Wire Protocol

Cognica implements PostgreSQL wire protocol version 3.0, enabling compatibility with standard PostgreSQL tools and drivers.

D.2.1 Connection Establishment

Protocol Version: 196608 (3.0)

Startup Sequence:

Authentication Methods:

D.2.2 Message Types

D.2.2.1 Frontend Messages (Client to Server)

D.2.2.2 Backend Messages (Server to Client)

D.2.3 Query Execution

D.2.3.1 Simple Query Protocol

D.2.3.2 Extended Query Protocol

D.2.4 COPY Protocol

Bulk data transfer using COPY:

COPY Formats:

D.2.5 Transaction Status

The ReadyForQuery message includes transaction status:

D.2.6 Error Response Format

Error messages include structured fields:

D.2.7 Query Executor Interface

Internal interface for query execution:

class QueryExecutor {
public:
  // Simple query execution
  auto execute_simple_query(const std::string& query) -> QueryResult;

  // Extended query protocol
  auto parse_query(const std::string& query,
                   const std::vector<int32_t>& param_oids) -> QueryResult;

  auto execute_portal(const std::string& query,
                      const std::vector<bytes>& params,
                      const std::vector<int16_t>& param_formats,
                      const std::vector<int32_t>& param_oids,
                      int32_t max_rows,
                      uint64_t cursor_id) -> QueryResult;

  // Plan caching
  auto compile_query_plan(const std::string& query,
                          const std::vector<int32_t>& param_oids)
      -> PlanCacheEntry;

  auto execute_with_cached_plan(const PlanCacheEntry& plan,
                                const std::vector<VMValue>& params,
                                int32_t max_rows) -> QueryResult;

  // Schema introspection
  auto describe_query(const std::string& query) -> std::vector<ColumnInfo>;

  // Cursor management
  void close_cursor(uint64_t cursor_id);

  // Transaction control
  void begin_transaction();
  void commit_transaction();
  void rollback_transaction();

  // COPY operations
  auto start_copy_from(const std::string& table,
                       const std::vector<std::string>& columns,
                       CopyFormat format,
                       char delimiter,
                       const std::string& null_string) -> CopyResult;

  auto process_copy_data(std::span<const uint8_t> data) -> CopyResult;
  auto finish_copy_from() -> CopyResult;
  auto abort_copy_from(const std::string& error) -> CopyResult;
};

D.3 Apache Arrow Flight SQL

Flight SQL provides high-performance SQL access using Apache Arrow for data transfer.

D.3.1 Service Interface

class FlightSQLServer : public arrow::flight::sql::FlightSqlServerBase {
public:
  // Query Execution
  auto GetFlightInfoStatement(
      const ServerCallContext& context,
      const StatementQuery& command,
      const FlightDescriptor& descriptor) -> Result<FlightInfo>;

  auto DoGetStatement(
      const ServerCallContext& context,
      const StatementQueryTicket& ticket) -> Result<FlightDataStream>;

  auto DoPutCommandStatementUpdate(
      const ServerCallContext& context,
      const StatementUpdate& command) -> Result<int64_t>;

  // Prepared Statements
  auto CreatePreparedStatement(
      const ServerCallContext& context,
      const ActionCreatePreparedStatementRequest& request)
      -> Result<ActionCreatePreparedStatementResult>;

  auto ClosePreparedStatement(
      const ServerCallContext& context,
      const ActionClosePreparedStatementRequest& request) -> Status;

  // Metadata
  auto GetFlightInfoCatalogs(
      const ServerCallContext& context,
      const FlightDescriptor& descriptor) -> Result<FlightInfo>;

  auto GetFlightInfoSchemas(
      const ServerCallContext& context,
      const GetDbSchemas& command,
      const FlightDescriptor& descriptor) -> Result<FlightInfo>;

  auto GetFlightInfoTables(
      const ServerCallContext& context,
      const GetTables& command,
      const FlightDescriptor& descriptor) -> Result<FlightInfo>;

  // Transactions
  auto BeginTransaction(
      const ServerCallContext& context,
      const ActionBeginTransactionRequest& request)
      -> Result<ActionBeginTransactionResult>;

  auto EndTransaction(
      const ServerCallContext& context,
      const ActionEndTransactionRequest& request) -> Status;
};

D.3.2 Query Execution Flow

D.3.3 Metadata Queries

D.3.4 SQL Info Keys

enum SqlInfo {
  FLIGHT_SQL_SERVER_NAME = 0,
  FLIGHT_SQL_SERVER_VERSION = 1,
  FLIGHT_SQL_SERVER_ARROW_VERSION = 2,
  SQL_DDL_CATALOG = 500,
  SQL_DDL_SCHEMA = 501,
  SQL_DDL_TABLE = 502,
  SQL_IDENTIFIER_CASE = 503,
  SQL_IDENTIFIER_QUOTE_CHAR = 504,
  SQL_QUOTED_IDENTIFIER_CASE = 505,
  SQL_ALL_TABLES_ARE_SELECTABLE = 506,
  SQL_NULL_ORDERING = 507,
  SQL_KEYWORDS = 508,
  SQL_NUMERIC_FUNCTIONS = 509,
  SQL_STRING_FUNCTIONS = 510,
  SQL_SYSTEM_FUNCTIONS = 511,
  SQL_DATETIME_FUNCTIONS = 512,
  SQL_SEARCH_STRING_ESCAPE = 513,
  SQL_EXTRA_NAME_CHARACTERS = 514,
  SQL_SUPPORTS_COLUMN_ALIASING = 515,
  SQL_SUPPORTS_CONVERT = 517,
  SQL_SUPPORTS_TABLE_CORRELATION_NAMES = 518,
  SQL_SUPPORTS_DIFFERENT_TABLE_CORRELATION_NAMES = 519,
  SQL_SUPPORTS_EXPRESSIONS_IN_ORDER_BY = 520,
  SQL_SUPPORTS_ORDER_BY_UNRELATED = 521,
  SQL_SUPPORTED_GROUP_BY = 522,
  SQL_SUPPORTS_LIKE_ESCAPE_CLAUSE = 523,
  SQL_SUPPORTS_NON_NULLABLE_COLUMNS = 524,
  SQL_SUPPORTED_GRAMMAR = 525,
  SQL_ANSI92_SUPPORTED_LEVEL = 526,
  SQL_SUPPORTS_INTEGRITY_ENHANCEMENT_FACILITY = 527,
  SQL_OUTER_JOINS_SUPPORT_LEVEL = 528,
  SQL_SCHEMA_TERM = 529,
  SQL_PROCEDURE_TERM = 530,
  SQL_CATALOG_TERM = 531,
  SQL_CATALOG_AT_START = 532,
  SQL_SCHEMAS_SUPPORTED_ACTIONS = 533,
  SQL_CATALOGS_SUPPORTED_ACTIONS = 534,
  SQL_SUPPORTED_POSITIONED_COMMANDS = 535,
  SQL_SELECT_FOR_UPDATE_SUPPORTED = 536,
  SQL_STORED_PROCEDURES_SUPPORTED = 537,
  SQL_SUPPORTED_SUBQUERIES = 538,
  SQL_CORRELATED_SUBQUERIES_SUPPORTED = 539,
  SQL_SUPPORTED_UNIONS = 540,
  SQL_MAX_BINARY_LITERAL_LENGTH = 541,
  SQL_MAX_CHAR_LITERAL_LENGTH = 542,
  SQL_MAX_COLUMN_NAME_LENGTH = 543,
  SQL_MAX_COLUMNS_IN_GROUP_BY = 544,
  SQL_MAX_COLUMNS_IN_INDEX = 545,
  SQL_MAX_COLUMNS_IN_ORDER_BY = 546,
  SQL_MAX_COLUMNS_IN_SELECT = 547,
  SQL_MAX_COLUMNS_IN_TABLE = 548,
  SQL_MAX_CONNECTIONS = 549,
  SQL_MAX_CURSOR_NAME_LENGTH = 550,
  SQL_MAX_INDEX_LENGTH = 551,
  SQL_DB_SCHEMA_NAME_LENGTH = 552,
  SQL_MAX_PROCEDURE_NAME_LENGTH = 553,
  SQL_MAX_CATALOG_NAME_LENGTH = 554,
  SQL_MAX_ROW_SIZE = 555,
  SQL_MAX_ROW_SIZE_INCLUDES_BLOBS = 556,
  SQL_MAX_STATEMENT_LENGTH = 557,
  SQL_MAX_STATEMENTS = 558,
  SQL_MAX_TABLE_NAME_LENGTH = 559,
  SQL_MAX_TABLES_IN_SELECT = 560,
  SQL_MAX_USERNAME_LENGTH = 561,
  SQL_DEFAULT_TRANSACTION_ISOLATION = 562,
  SQL_TRANSACTIONS_SUPPORTED = 563,
  SQL_SUPPORTED_TRANSACTIONS_ISOLATION_LEVELS = 564,
  SQL_DATA_DEFINITION_CAUSES_TRANSACTION_COMMIT = 565,
  SQL_DATA_DEFINITIONS_IN_TRANSACTIONS_IGNORED = 566,
  SQL_SUPPORTED_RESULT_SET_TYPES = 567,
  SQL_BATCH_UPDATES_SUPPORTED = 572,
  SQL_SAVEPOINTS_SUPPORTED = 573,
  SQL_NAMED_PARAMETERS_SUPPORTED = 574,
  SQL_LOCATORS_UPDATE_COPY = 575,
  SQL_STORED_FUNCTIONS_USING_CALL_SYNTAX_SUPPORTED = 576,
};

D.4 HTTP REST API

D.4.1 Base URL and Headers

Base URL: http://host:8080/api/v1

Common Headers:

D.4.2 Health and System Endpoints

GET /api/v1/health

Check server health.

Response:

{
  "status": "ok"
}

GET /api/v1/version

Get server version.

Response:

{
  "version": "1.0.0",
  "api": "REST"
}

GET /api/v1/metrics

Get Prometheus metrics.

Response: text/plain (Prometheus exposition format)

# HELP cognica_queries_total Total number of queries executed
# TYPE cognica_queries_total counter
cognica_queries_total{type="select"} 12345
cognica_queries_total{type="insert"} 6789
...

D.4.3 SQL Endpoints

POST /api/v1/sql/query

Execute SQL query.

Request:

{
  "query": "SELECT * FROM users WHERE status = 'active'",
  "limit": 100,
  "offset": 0
}

Response:

{
  "rows": [
    {"id": 1, "name": "Alice", "status": "active"},
    {"id": 2, "name": "Bob", "status": "active"}
  ],
  "rows_returned": 2,
  "total_scanned": 1000,
  "has_more": false,
  "total_rows": 2
}

POST /api/v1/sql/explain

Get query execution plan.

Request:

{
  "query": "SELECT * FROM users WHERE id = 123"
}

Response:

{
  "plan": [
    {
      "type": "IndexScan",
      "table": "users",
      "index": "users_pkey",
      "cost": 1.5,
      "rows": 1
    }
  ]
}

D.4.4 GraphQL Endpoint

POST /api/v1/graphql

Execute GraphQL query.

Request:

{
  "query": "query { users(limit: 10) { id name email } }",
  "variables": {}
}

Response:

{
  "data": {
    "users": [
      {"id": "1", "name": "Alice", "email": "alice@example.com"}
    ]
  }
}

WebSocket /api/v1/graphql/ws

GraphQL subscriptions via WebSocket.

Configuration:

• Idle timeout: 5 minutes
• Ping interval: 30 seconds

D.4.5 Error Responses

Error Response Format:

{
  "error": "Detailed error message",
  "code": "ERROR_CODE",
  "details": {}
}

D.5 Profiling Information

All APIs include execution profiling in responses.

D.5.1 ProfileInfo Structure

{
  "duration_us": 1234,
  "serialization_duration_us": 56,
  "rows_matched": 100,
  "rows_scanned": 10000,
  "rows_filtered": 9900,
  "bytes_read": 524288,
  "bytes_written": 0,
  "counters": {}
}

D.5.2 Performance Metrics

Summary

Cognica's multi-protocol API architecture provides:

• PostgreSQL Protocol: SQL tool compatibility (psql, JDBC, etc.)
• Flight SQL: Arrow-based analytics integration
• HTTP REST: Web application integration

All protocols share the same underlying execution engine, ensuring consistent behavior and performance characteristics across different access methods. The choice of protocol depends on client ecosystem requirements, performance needs, and data transfer patterns.