• Endpoint Group: Tools / Diagnostics
• Purpose: Provide read-only visibility of production database tables for super user investigations.
• Availability: Requires super user team membership (Brewski internal operations).

1. Dynamic Schema Introspection

   • Never hardcode column names or relationships.
   • Always read live schema data from Postgres using information_schema and pg_catalog.
   • Use this base query to get all visible columns:

     SELECT
       table_name,
       column_name,
       data_type,
       is_nullable,
       column_default
     FROM information_schema.columns
     WHERE table_schema = 'public'
     ORDER BY table_name, ordinal_position;

   • Cache results in memory to reduce load and refresh after migrations.

2. Foreign Key Discovery

   • Discover foreign keys dynamically:

     SELECT
       kcu.table_name AS source_table,
       kcu.column_name AS fk_column,
       ccu.table_name AS target_table,
       ccu.column_name AS target_column
     FROM information_schema.table_constraints AS tc
     JOIN information_schema.key_column_usage AS kcu
       ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu
       ON ccu.constraint_name = tc.constraint_name
     WHERE tc.constraint_type = 'FOREIGN KEY';

   • Merge this into a mapping {source_table.fk_column → {target_table, target_column}}.

3. Friendly Columns

   • Prefer columns named (case-insensitive): name, reference, title, label, code, description
   • When resolving foreign keys, select one of those fields if available.

• If no such column exists, fall back to any text field shorter than 100 chars.
• Avoid IDs, UUIDs, booleans, timestamps, and numeric columns as labels.

4. Metadata Table (optional but preferred)

   • Use table_labels if it exists:

     CREATE TABLE IF NOT EXISTS table_labels (
       table_name text PRIMARY KEY,
       label_columns text[]
     );

   • Read mappings from there first; only use heuristics when missing.

5. Runtime Behaviour

   • When rendering tables in the admin browser:
     • Use live schema to build headers and data grids.
     • Automatically join FK targets to display friendly names.
     • Validate columns before running $queryRaw() to avoid “column does not exist” errors.
   • Never assume a fixed list of label columns — always compute dynamically.

6. Schema Sync

   • Regenerate or refresh schema cache whenever:
     • a migration runs, or
     • prisma migrate deploy completes.
   • Store the cached schema as src/config/schema-map.json for offline operation.

7. Fail Gracefully

   • If a label column is missing, display the raw ID but log a warning.
   • Never break API routes because of schema drift.

The DB browser must be entirely data-driven. Discover columns, types, and foreign keys dynamically. Use heuristics for “friendly” display fields. Do not hardcode schema details in code.

• Authorization: Bearer <token> — the caller must belong to a team with role = 'su'. Other memberships receive 403 Forbidden.

No parameters.

• :table (string, required) — table name in the public schema. Only alphanumeric and underscore identifiers are accepted.

• page (integer, optional, min 1) — 1-based page index (default 1).
• pageSize (integer, optional, min 1, max 200) — rows per page (default 25).
• searchColumn (string, optional) — column name to filter on. Must exist on the table.
• searchTerm (string, optional) — value to search for within searchColumn. When provided, searchColumn is required. The backend performs a case-insensitive ILIKE match against the column coerced to text.
• sort (string, optional) — column to order by. Must match one of the selected table’s columns. When omitted, the API prefers timestamp columns (updated_at, created_at), then date fields, and finally falls back to the first column.
• direction (string, optional) — asc or desc. Defaults to asc and applies to whichever column the API sorts by (custom or fallback).

Lists every entry in _prisma_migrations, ordered by most recently finished. No parameters.

{
  "tables": [
    { "name": "users", "rowCount": 1432 },
    { "name": "orders", "rowCount": 276 }
  ]
}

{
  "table": "orders",
  "columns": [
    { "name": "id", "dataType": "uuid" },
    { "name": "status", "dataType": "text" },
    { "name": "created_at", "dataType": "timestamp with time zone" }
  ],
  "rows": [
    {
      "id": "c5b6d0da-8d5a-4fca-8f76-2b1547bca9de",
      "status": "confirmed",
      "team_id": "f58dc8ab-1578-4bc7-982c-20b8886be646",
      "created_at": "2025-02-07T12:34:56.000Z",
      "__references": {
        "team_id": "Summit Spire"
      }
    }
  ],
  "page": 1,
  "pageSize": 25,
  "total": 134,
  "pages": 6,
  "sort": "created_at",
  "direction": "asc"
}

• columns lists every column in ordinal order with the Postgres data type, including size/precision (e.g., character varying(255), numeric(10,2)) when available.
• rows contains JSON-serialised results. bigint and numeric decimals are returned as strings, timestamps are ISO 8601. When the route can resolve a foreign key (team_id, client_id, user_id, beer_id, batch_id, brewer_id), the related label appears under __references for that row (and the client may use it for tooltips or display).
• sort reflects the column used for ordering (falls back to the first column when no timestamp or id field exists).
• direction is the server-applied ordering direction (asc or desc) that mirrors the request, defaulting to ascending.
• rowCount in the table list reflects the approximate live row count reported by pg_stat_user_tables (suitable for diagnostics, not auditing).
• Pagination metadata follows the standard Brewski contract.

{
  "migrations": [
    {
      "id": "20250910113000_add_beer_integrations",
      "migrationName": "20250910113000_add_beer_integrations",
      "checksum": "55c0ffee",
      "startedAt": "2025-11-05T21:33:05.431Z",
      "finishedAt": "2025-11-05T21:33:05.530Z",
      "rolledBackAt": null,
      "appliedStepsCount": 1,
      "status": "applied"
    }
  ]
}

• status is one of:
  • applied — finished_at is populated and no rollback exists.
  • running — started_at is populated but finished_at is null.
  • pending — neither started_at nor finished_at is set yet.
  • rolledBack — rolled_back_at contains the time the migration was marked as reverted.

• None. These endpoints perform read-only queries with parameterised filters.

• No special limits beyond standard API gateway policies. All requests are safe to retry.

curl -H "Authorization: Bearer $TOKEN" \
  "https://api.brewskiapp.com/tools/db/tables/orders/rows?page=2&pageSize=50&searchColumn=status&searchTerm=confirmed&sort=due_date&direction=desc"