Execute a read-only SELECT query and return rows as a list of dicts.

All queries run inside an immediately-rolled-back transaction — write
statements are blocked both statically and at the transaction level.
Call db_get_schema first if you are unfamiliar with the table structure.

Args:
    sql:        SELECT query to execute. No INSERT/UPDATE/DELETE allowed.
    connection: Connection name (e.g. "orders.prod"). Defaults to first defined.
    max_rows:   Maximum rows to return (default 500). Set lower for large tables.

Returns:
    {rows, row_count, connection, database, truncated}

Get column schema for a table, merged with any saved semantic annotations.

Checks the local cache first; fetches from the database on cache miss or
when force_refresh=True. Saves the result to cache for future calls.
Merges column descriptions, enum value mappings, and FK references from
previous db_annotate() calls into the response.

Args:
    table:         Table name, optionally schema-qualified. Use whatever your
                   DB uses — e.g. "users", "public.users" (Postgres),
                   "dbo.Orders" (MSSQL), "mydb.orders" (MySQL).
    connection:    Connection name. Defaults to first defined.
    force_refresh: Bypass cache and fetch fresh schema from the database.

Returns:
    {table, connection, columns (with annotations merged in), table_description, cached}

List all known tables for a connection, with descriptions and column counts.

Tables appear once they have been fetched via db_get_schema or annotated via
db_annotate. Descriptions come from the knowledge store — richer than raw
INFORMATION_SCHEMA.

Args:
    connection: Connection name. Defaults to first defined.

Returns:
    {connection, database, tables: [{table_fqn, description, aliases, column_count}]}

Search the knowledge layer for tables/columns matching a query — BM25-ranked.

Use this BEFORE db_list_tables when you're looking for a specific concept
(e.g. "payments", "user email", "shipping address"). db_list_tables returns
every table; db_search returns just the relevant ones with descriptions and
highlighted snippets.

Searches across:
  - Table names, descriptions, and aliases
  - Column names, descriptions, and enum_values
Falls back gracefully to empty results if the query has invalid FTS5 syntax.

Args:
    query:      Search text. Supports FTS5 syntax: phrases ("foo bar"),
                prefix matching (pay*), boolean operators (foo AND bar).
    connection: Connection name. Defaults to first defined.
    target:     "tables", "columns", or "all" (default).
    limit:      Max results to return (default 10).

Returns:
    {query, connection, target, result_count, results: [
        {target_type, table_fqn, column_name, description, snippet, score}, ...
    ]}

Persist semantic annotations for a table or column — survives across sessions.

This is the core of amnesic's persistent memory. Every annotation saved here
is automatically merged into future db_get_schema() responses, so the AI
never has to rediscover what a status code means or what a table is for.

Call this after discovering: what an enum value means, what a column represents,
how a table relates to another, or what a table is used for.

Args:
    table:              Table name, optionally schema-qualified to match your
                        DB — e.g. "users", "public.users" (Postgres),
                        "dbo.Orders" (MSSQL), "mydb.orders" (MySQL).
    connection:         Connection name. Defaults to first defined.
    table_description:  Human-readable description of the table's purpose.
    table_aliases:      Alternative names the table is known by.
    column:             Column to annotate (required for column-level args below).
    column_description: What this column represents in the business domain.
    enum_values:        Dict mapping stored values to labels {"1": "active", "2": "inactive"}.
    foreign_key:        FK reference as "other_table.column_name".
    example_values:     Representative sample values from this column.

Returns:
    {table, connection, updated: {table_knowledge?, column_knowledge?}}

Soft-retire a table or column annotation — flag it stale without deleting it.

Use when a table/column still exists but should no longer be relied on. The
deprecation flag is surfaced in db_get_schema so the AI is warned off it on
future calls. Reversible via undo=True. To remove an annotation entirely
(e.g. the column was dropped from the DB), use db_forget instead.

Args:
    table:      Table name, optionally schema-qualified (e.g. "users",
                "public.users", "dbo.Orders", "mydb.orders").
    connection: Connection name. Defaults to first defined.
    column:     Column to deprecate. Omit to deprecate the whole table.
    reason:     Why it's deprecated (e.g. "replaced by status_v2").
    undo:       Clear the deprecation flag instead of setting it.

Returns:
    {table, connection, column, target, deprecated, reason}

Copy annotations from one connection's knowledge store to another.

Typical use: after confirming that staging and prod share the same schema,
sync all the semantic knowledge you've built up in staging to prod.
Only syncs tables and columns that exist in the target schema cache —
tables missing from target are reported in 'skipped', columns in 'warnings'.

Args:
    from_connection: Source connection (e.g. "orders.staging").
    to_connection:   Target connection (e.g. "orders.prod").
    tables:          Optional list of specific table FQNs to sync. Defaults to all.

Returns:
    {synced: [...], skipped: [{table, reason}], warnings: [{table, column, reason}]}

List all configured database connections without exposing passwords or usernames.

Use this first to see what databases are available before calling other tools.
Returns connection names, drivers, databases, and server addresses.

Returns:
    {connections: [{name, driver, database, server}]}

Permanently delete a table or column annotation. Safe by default — NOT reversible.

Use to remove a wrong annotation, or to clean up after a table/column was
dropped from the DB (pairs with db_detect_drift). Unlike db_deprecate, this
hard-deletes. Cascade is opt-in so you can't nuke a table by accident:
  - db_forget(table)               -> ONLY the table's own annotation
  - db_forget(table, column="x")   -> ONLY that column's annotation
  - db_forget(table, cascade=True) -> the table + all its column annotations
                                      + all relationships touching it

Only the local knowledge store is changed — never the live database.

Args:
    table:      Table name, optionally schema-qualified (e.g. "users",
                "public.users", "dbo.Orders", "mydb.orders").
    connection: Connection name. Defaults to first defined.
    column:     Column annotation to delete. Omit to target the table.
    cascade:    When targeting a table, also delete its columns +
                relationships. Ignored when column is given.

Returns:
    {table, connection, column, removed_table, removed_columns, removed_relationships}

Audit saved annotations against the live database schema (read-only).

Surfaces drift after the schema evolves:
  - orphaned annotations — a table or column you annotated that no longer
    exists in the DB. Remove with db_forget, or db_deprecate if pending.
  - undocumented tables — live tables with no annotation yet (coverage gaps).

Changes nothing — purely a report. Run after schema changes, or periodically.

Args:
    connection: Connection name. Defaults to first defined.

Returns:
    {connection, orphaned_tables, orphaned_columns, undocumented_tables,
     undocumented_truncated, summary}

Discover all foreign key relationships in the database and save them to the graph.

Runs driver-specific FK introspection queries against the live database and
persists results to the local KnowledgeStore. Run once per database; re-run
after schema changes. After discovery, use db_get_relationships to navigate
the graph when planning complex JOIN queries.

Args:
    connection: Connection name. Defaults to first defined.

Returns:
    {connection, discovered: count, relationships: [{from_table, from_column, to_table, to_column}]}

Get the foreign key relationship graph for a table up to the given traversal depth.

Depth 1 returns direct neighbors (tables one JOIN away). Depth 2 returns
neighbors-of-neighbors. Returns both a flat neighbor list and formatted join
path strings to help plan multi-table queries. Requires db_discover_relationships
to have been run first.

Args:
    table:      Table name (e.g. "Orders").
    connection: Connection name. Defaults to first defined.
    depth:      BFS traversal depth (default 1, recommended max 3).

Returns:
    {table, connection, neighbors: [...], paths: ["TableA -> TableB -> TableC", ...]}