# Database MCP Server Read-only SQL over two engines: Postgres (men's ODI cricket) and DuckDB (men's T20 cricket). Each carries a ball-by-ball table plus a match_info companion joined on match_id. Live row counts are in the service root at / rather than repeated here, so they cannot go stale. Exposed as MCP tools for AI client consumption. ## Run read-only SQL on Postgres (Supabase): ODI cricket ball-by-ball data `POST /v1/query/postgres` Execute a read-only SQL query against the Supabase Postgres database. Men's ODI (One Day International) cricket, refreshed twice daily from the same validated source the rest of the Tigzig cricket data comes from. ### Scope Men's internationals only. Women's cricket is deliberately excluded for now - `gender` lives only in match_info, so leaving it in meant every single-table aggregate silently pooled men's and women's records with nothing in the response to say so. It returns as a proper dimension later. There is also no domestic or franchise cricket here (no IPL, no Big Bash): this is a Cricsheet-derived internationals set. ### Two tables Both are joined on `match_id`. 1. odi_ball_by_ball - one row per DELIVERY, men's ODI only, ~1.36M rows. Columns: match_id, season, start_date, venue, innings, ball, batting_team, bowling_team, striker, non_striker, bowler, runs_off_bat, extras, wides, noballs, byes, legbyes, penalty, wicket_type, player_dismissed, other_wicket_type, other_player_dismissed, match_type. 2. match_info - one row per MATCH, ~6,900 matches. Columns: match_id, match_type, team1, team2, team1_icc_type, team2_icc_type, gender, season, start_date, venue, city, event, match_number, toss_winner, toss_decision, winner, winner_runs, winner_wickets, player_of_match, umpire1, umpire2, tv_umpire, reserve_umpire, match_referee. This is where who won, by how much, which competition, player of the match and the officials live - none of that is in the ball-by-ball table. ### Join example ```sql SELECT m.winner, SUM(b.runs_off_bat) FROM odi_ball_by_ball b JOIN match_info m ON b.match_id = m.match_id GROUP BY m.winner ``` ### Before you aggregate One thing here will otherwise give you a surprising answer. `team1_icc_type` and `event` live in match_info, not in the ball table. A plain "top run scorers" over the ball table pools every level of international cricket, so Associate nations rank alongside Full Members. That is correct data, rarely the intended question. Join to match_info and filter when you mean a subset. ### One asymmetry Stated up front, because it will not be obvious from a row count: match_info covers ALL THREE formats (ODI, T20 and Test) in BOTH databases, while the ball-by-ball table here is ODI only. A match_info row can therefore exist with no deliveries in this database, and counting matches in match_info will not agree with counting them in the ball table unless you filter match_info by match_type = 'ODI'. This is deliberate: cross-database joins are impossible, so each engine carries its own copy of match_info, and keeping every format means the full match universe stays queryable from either side. ### match_type values Uppercase, and exactly these three: 'ODI', 'T20', 'TEST'. match_type = 'Test' matches nothing. Supports JSON (default) and TSV response formats. TSV uses shortened headers and is ~70% smaller (better for AI context windows). ## SQL guardrails Read-only endpoint. Everything below is enforced, so nothing here is a surprise you have to discover by being refused. ### Allowed - `SELECT` and `WITH`. CTEs do not count as nesting. - Up to 10 JOINs per SELECT, each with an explicit `ON`. - Subqueries up to depth 3. - Window functions, including `OVER (PARTITION BY ... ORDER BY ...)` and `FILTER`. - `SHOW TABLES`, `DESCRIBE ` and `EXPLAIN`. `DESCRIBE` is the quickest way to get column types, and both work on either engine. - Schema metadata: `information_schema` and `pg_class` are readable. They show only what your role may access, and the full schema is documented above anyway. ### Blocked - Writes and DDL of any kind. - SQL comments. - Server and role catalogs: `pg_roles`, `pg_settings`, `pg_stat_activity` and similar. - Joins without `ON`, comma joins, `CROSS JOIN`. - More than 10 JOINs per SELECT. - More than 10 SELECT keywords. That is the count of the word SELECT **anywhere** in the query, including inside CTEs and subqueries. - `WITH RECURSIVE`. - Subqueries inside `ORDER BY`, and function calls inside `ORDER BY`. Sort on a plain column, or compute the value in a SELECT or CTE first and sort on that. - Any function that reads a file or a URL: `read_csv`, `read_parquet` and similar. This endpoint runs SQL against the tables above and nothing else. - More than one statement per request. A semicolon-separated batch is rejected rather than silently running only the last one. ### Row limit: every query returns at most 1000 rows - If yours would return more, you get the first 1000 and `truncated: true`. Always read that field. - The ceiling applies even when you ask for more: `LIMIT 5000` returns 1000 rows with `truncated: true`, not 5000. - A `LIMIT` or `FETCH FIRST` below 1000 is honoured exactly and comes back `truncated: false`, so you know you have the whole result. - If you set no limit at all, one is added for you. Everything else behaves normally: `ORDER BY`, `OFFSET`, CTEs and aggregates are unaffected, so `LIMIT ... OFFSET ...` pagination works. - **Set your own limit.** It is faster, and a `false` on `truncated` is your proof nothing was cut. - There is also a 1MB response ceiling, which nothing in normal use comes close to. ### Time limit: 30s for the query, 45s worst case for the request The extra 15s is time spent waiting for a free slot when the service is busy. If you disconnect, the query does not stop, because the database cannot tell you left, so it runs to its 30s budget. Retrying immediately stacks work rather than replacing it. ### Two engines, two dialects `/v1/query/postgres` is PostgreSQL and `/v1/query/duckdb` is DuckDB, and SQL is **not** portable between them. They hold different tables, and each supports functions and syntax the other does not. `QUALIFY` works on DuckDB and fails on Postgres, and date and string functions differ. If a query works on one endpoint and fails on the other, check the dialect before you check your table names. Standard `SELECT`, `JOIN`, `GROUP BY`, CTEs, window functions and `FILTER` work on both. ## Data semantics Each row is a single delivery (ball) in a match. ODI = 50 overs/innings, usually 2 innings per match. ### Ball counting The ball field (e.g. 0.1, 7.5) is an over.ball identifier, NOT a sequential count. Overs may have >6 deliveries due to wides/no-balls (e.g. 0.7). Use COUNT(*) for total balls bowled. ### Runs `runs_off_bat` is runs scored by batsman. extras = additional runs (wides, no-balls, byes, legbyes, penalty). Total runs for a delivery = runs_off_bat + extras. NULL extras components should be treated as 0. ### Wickets Check both wicket_type AND other_wicket_type for dismissals. If either is non-null, that delivery has a dismissal. Common wicket_type values: bowled, caught, lbw, run out, stumped, caught and bowled, hit wicket, retired hurt. ### Player names Use the exact full name if known. If uncertain, use surname with LIKE wildcards (e.g. WHERE striker LIKE '%Kohli%'). ### Season format Can be a year (2023) or split-year (2023/24) for southern hemisphere seasons. ### match_type in this table Always 'ODI'. ### Example query ```sql SELECT striker, SUM(runs_off_bat) as runs, COUNT(*) as balls FROM odi_ball_by_ball WHERE season = '2023' GROUP BY striker ORDER BY runs DESC LIMIT 10 ``` Part of Tigzig: free interactive tools, open-source repos, APIs and MCP servers for analytics and live data across global and Indian markets, macro indicators and filings. Catalog: https://api.tigzig.com/.well-known/api-catalog Guide: https://www.tigzig.com/llms.txt ## GET variant: read-only SQL on Postgres (ODI cricket) via ?sql= `GET /v1/query/postgres` GET variant for fetch-only clients (browsers, crawler-style agents, no-code HTTP nodes that cannot send a POST body). Same engine, same read-only SQL validation, same row/size caps, timeouts and rate limits as the POST endpoint - the SQL simply arrives in the `sql` query parameter (URL-encoded; `query` also works as an alias), with optional `format=json|tsv`. POST remains the primary interface and is better for long SQL (no URL-length limit). ## Run read-only SQL on DuckDB: T20 cricket ball-by-ball data `POST /v1/query/duckdb` Execute a read-only SQL query against the DuckDB database. Men's T20 (Twenty20) international cricket, refreshed twice daily from the same validated source the rest of the Tigzig cricket data comes from. ### Scope Men's internationals only. Women's cricket is deliberately excluded for now - `gender` lives only in match_info, so leaving it in meant every single-table aggregate silently pooled men's and women's records with nothing in the response to say so. It returns as a proper dimension later. There is also no domestic or franchise cricket here (no IPL, no Big Bash): this is a Cricsheet-derived internationals set. ### Two tables Both are joined on `match_id`. 1. t20_ball_by_ball - one row per DELIVERY, men's T20 only, ~787k rows. Columns: match_id, season, start_date, venue, innings, ball, batting_team, bowling_team, striker, non_striker, bowler, runs_off_bat, extras, wides, noballs, byes, legbyes, penalty, wicket_type, player_dismissed, other_wicket_type, other_player_dismissed, match_type. 2. match_info - one row per MATCH, ~6,900 matches. Columns: match_id, match_type, team1, team2, team1_icc_type, team2_icc_type, gender, season, start_date, venue, city, event, match_number, toss_winner, toss_decision, winner, winner_runs, winner_wickets, player_of_match, umpire1, umpire2, tv_umpire, reserve_umpire, match_referee. This is where who won, by how much, which competition, player of the match and the officials live - none of that is in the ball-by-ball table. ### Join example ```sql SELECT m.winner, SUM(b.runs_off_bat) FROM t20_ball_by_ball b JOIN match_info m ON b.match_id = m.match_id GROUP BY m.winner ``` ### Before you aggregate One thing here will otherwise give you a surprising answer. `team1_icc_type` and `event` live in match_info, not in the ball table. A plain "top run scorers" over the ball table pools every level of international cricket, so Associate nations rank alongside Full Members. That is correct data, rarely the intended question. Join to match_info and filter when you mean a subset. ### One asymmetry Stated up front, because it will not be obvious from a row count: match_info covers ALL THREE formats (ODI, T20 and Test) in BOTH databases, while the ball-by-ball table here is T20 only. A match_info row can therefore exist with no deliveries in this database, and counting matches in match_info will not agree with counting them in the ball table unless you filter match_info by match_type = 'T20'. This is deliberate: cross-database joins are impossible, so each engine carries its own copy of match_info, and keeping every format means the full match universe stays queryable from either side. ### match_type values Uppercase, and exactly these three: 'ODI', 'T20', 'TEST'. match_type = 'Test' matches nothing. Supports JSON (default) and TSV response formats. TSV uses shortened headers and is ~70% smaller (better for AI context windows). ## SQL guardrails Read-only endpoint. Everything below is enforced, so nothing here is a surprise you have to discover by being refused. ### Allowed - `SELECT` and `WITH`. CTEs do not count as nesting. - Up to 10 JOINs per SELECT, each with an explicit `ON`. - Subqueries up to depth 3. - Window functions, including `OVER (PARTITION BY ... ORDER BY ...)` and `FILTER`. - `SHOW TABLES`, `DESCRIBE ` and `EXPLAIN`. `DESCRIBE` is the quickest way to get column types, and both work on either engine. - Schema metadata: `information_schema` and `pg_class` are readable. They show only what your role may access, and the full schema is documented above anyway. ### Blocked - Writes and DDL of any kind. - SQL comments. - Server and role catalogs: `pg_roles`, `pg_settings`, `pg_stat_activity` and similar. - Joins without `ON`, comma joins, `CROSS JOIN`. - More than 10 JOINs per SELECT. - More than 10 SELECT keywords. That is the count of the word SELECT **anywhere** in the query, including inside CTEs and subqueries. - `WITH RECURSIVE`. - Subqueries inside `ORDER BY`, and function calls inside `ORDER BY`. Sort on a plain column, or compute the value in a SELECT or CTE first and sort on that. - Any function that reads a file or a URL: `read_csv`, `read_parquet` and similar. This endpoint runs SQL against the tables above and nothing else. - More than one statement per request. A semicolon-separated batch is rejected rather than silently running only the last one. ### Row limit: every query returns at most 1000 rows - If yours would return more, you get the first 1000 and `truncated: true`. Always read that field. - The ceiling applies even when you ask for more: `LIMIT 5000` returns 1000 rows with `truncated: true`, not 5000. - A `LIMIT` or `FETCH FIRST` below 1000 is honoured exactly and comes back `truncated: false`, so you know you have the whole result. - If you set no limit at all, one is added for you. Everything else behaves normally: `ORDER BY`, `OFFSET`, CTEs and aggregates are unaffected, so `LIMIT ... OFFSET ...` pagination works. - **Set your own limit.** It is faster, and a `false` on `truncated` is your proof nothing was cut. - There is also a 1MB response ceiling, which nothing in normal use comes close to. ### Time limit: 30s for the query, 45s worst case for the request The extra 15s is time spent waiting for a free slot when the service is busy. If you disconnect, the query does not stop, because the database cannot tell you left, so it runs to its 30s budget. Retrying immediately stacks work rather than replacing it. ### Two engines, two dialects `/v1/query/postgres` is PostgreSQL and `/v1/query/duckdb` is DuckDB, and SQL is **not** portable between them. They hold different tables, and each supports functions and syntax the other does not. `QUALIFY` works on DuckDB and fails on Postgres, and date and string functions differ. If a query works on one endpoint and fails on the other, check the dialect before you check your table names. Standard `SELECT`, `JOIN`, `GROUP BY`, CTEs, window functions and `FILTER` work on both. ## Data semantics Each row is a single delivery (ball) in a match. T20 = 20 overs/innings, usually 2 innings per match. ### Ball counting The ball field (e.g. 0.1, 7.5) is an over.ball identifier, NOT a sequential count. Overs may have >6 deliveries due to wides/no-balls (e.g. 0.7). Use COUNT(*) for total balls bowled. ### Runs `runs_off_bat` is runs scored by batsman. extras = additional runs (wides, no-balls, byes, legbyes, penalty). Total runs for a delivery = runs_off_bat + extras. NULL extras components should be treated as 0. ### Wickets Check both wicket_type AND other_wicket_type for dismissals. If either is non-null, that delivery has a dismissal. Common wicket_type values: bowled, caught, lbw, run out, stumped, caught and bowled, hit wicket, retired hurt. ### Player names Use the exact full name if known. If uncertain, use surname with LIKE wildcards (e.g. WHERE striker LIKE '%Kohli%'). ### Season format Can be a year (2023) or split-year (2023/24) for southern hemisphere seasons. ### match_type in this table Always 'T20'. ### Example query ```sql SELECT striker, SUM(runs_off_bat) as runs, COUNT(*) as balls FROM t20_ball_by_ball WHERE season = '2023' GROUP BY striker ORDER BY runs DESC LIMIT 10 ``` Part of Tigzig: free interactive tools, open-source repos, APIs and MCP servers for analytics and live data across global and Indian markets, macro indicators and filings. Catalog: https://api.tigzig.com/.well-known/api-catalog Guide: https://www.tigzig.com/llms.txt ## GET variant: read-only SQL on DuckDB (T20 cricket) via ?sql= `GET /v1/query/duckdb` GET variant for fetch-only clients (browsers, crawler-style agents, no-code HTTP nodes that cannot send a POST body). Same engine, same read-only SQL validation, same row/size caps, timeouts and rate limits as the POST endpoint - the SQL simply arrives in the `sql` query parameter (URL-encoded; `query` also works as an alias), with optional `format=json|tsv`. POST remains the primary interface and is better for long SQL (no URL-length limit). ## Health check `GET /health` Returns service status, version, and connectivity to both databases. Part of Tigzig: free interactive tools, open-source repos, APIs and MCP servers for analytics and live data across global and Indian markets, macro indicators and filings. Catalog: https://api.tigzig.com/.well-known/api-catalog Guide: https://www.tigzig.com/llms.txt