---
title: Add Data View Debug Endpoint
type: task
status: completed
description: >-
  Add POST /api/data-views/debug returning generated SQL and executed results for a data view
  request (admin-gated). Accept either an explicit table_state or a /u/{hash} short URL. Share the
  URL-to-table-state resolver with scripts/debug-data-view.mjs.
base_uri: user:task/league/add-data-view-debug-endpoint.md
created_at: '2026-05-23T16:33:42.683Z'
entity_id: 32318037-58d0-412f-8502-c079c383b0f0
finished_at: '2026-05-23T16:36:57.220Z'
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Low
public_read: true
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-05-23T16:36:57.893Z'
visibility_analyzed_at: '2026-11-05T23:48:10.256Z'
---

# Implementation Plan: Add Data View Debug Endpoint

## Overview

- Add `POST /api/data-views/debug` returning `{ table_state, query, results, metadata, timing }` for a data view request.
- Accept either an explicit `table_state` body OR a `short_url` (full URL, `/u/{hash}`, or bare hash); resolve the latter against the `urls` table.
- Admin-gated (`req.auth.userId === 1`) because the response exposes raw SQL and schema details.
- Bypasses redis cache (debugging must always reflect current SQL generation).
- Refactor `scripts/debug-data-view.mjs` and the new route to share one URL-to-table-state resolver helper.
- Update `workflow/debug-data-view.md` to surface the new endpoint as the fastest path.

## Background

Today there are two ways to inspect data view query generation:

1. `scripts/debug-data-view.mjs` -- CLI; takes `/u/{hash}`, hits Postgres directly, prints SQL. Requires local repo and DB credentials.
2. `mcp__postgres-league__query` -- run arbitrary SQL after copy-pasting the CLI output.

This is friction during agent-driven development and during the `workflow/debug-data-view.md` flow. A single HTTP endpoint that returns both the query and the result rows for a given request closes that loop and is also reachable from any Claude session that can hit the API.

Reusable pieces already in place:

- `libs-server/get-data-view-results.mjs` exports `get_data_view_results_query({...})` which returns `{ query, data_view_metadata }` without executing -- the seam we want.
- Default export executes the query and returns `{ data_view_results, data_view_metadata }`.
- `libs-server/format-sql.mjs` exports `format_sql(sql, options)` for prettified output.
- `api/routes/shorten-url.mjs` already exposes `GET /api/u/:hash` returning `{ url, url_hash }` and uses `resolve_short_url_chain` for chained URLs.
- `validators.table_state_validator` and `validators.short_url_validator` exist.
- Admin gate precedent: `api/routes/cache.mjs:252` -- `if (!req.auth || req.auth.userId !== 1) return 401`.

## Design

### New shared helper

`libs-server/data-views/resolve-table-state.mjs`:

```js
export const resolve_table_state_from_input = async ({
  short_url,
  table_state,
  db
}) => {
  if (table_state) return table_state
  if (!short_url) throw new Error('short_url or table_state required')
  const hash = extract_short_url_hash(short_url)
  const row = await db('urls').where('url_hash', hash).first()
  if (!row) throw new Error(`short URL hash not found: ${hash}`)
  return parse_url_to_table_state(row.url)
}
```

Helpers (`extract_short_url_hash`, `parse_url_to_table_state`) move from `scripts/debug-data-view.mjs` into the same module. `extract_short_url_hash` accepts: full URL containing `/u/{hash}`, `/u/{hash}` path, or bare 32-char hash.

### New route

In `api/routes/data-views.mjs`:

```
POST /api/data-views/debug
  body: { short_url?, table_state?, execute = true, limit?, beautify = true }
  auth: req.auth.userId === 1
  flow:
    1. resolve table_state via shared helper
    2. apply limit override if provided
    3. get_data_view_results_query(table_state) -> raw_sql, params
    4. format_sql if beautify
    5. if execute: get_data_view_results(...) -> rows, metadata
    6. return { table_state, query, query_bindings, results, metadata, timing }
```

### CLI refactor

`scripts/debug-data-view.mjs` drops its local `parse_short_url` and `parse_url_to_table_state`; imports them (or just `resolve_table_state_from_input`) from the new shared module.

### Knowledge Base Impact

- Domain entry doc: N/A.
- Affected docs: `workflow/debug-data-view.md` gains a new "Fast path" subsection at the top of Phase 1.
- Tags: only project tag.
- Observations: none material at completion beyond `[completion]`.

### Scratch / orchestrator

Neither needed. Bounded change; single task entity.

## Tasks

1. - [x] Create `libs-server/data-views/resolve-table-state.mjs` with `resolve_table_state_from_input`, `extract_short_url_hash`, `parse_url_to_table_state`. Export from `libs-server/index.mjs`.
2. - [x] Add `POST /api/data-views/debug` route in `api/routes/data-views.mjs` with admin gate, validator, and the flow above.
3. - [x] Refactor `scripts/debug-data-view.mjs` to import the shared resolver. Remove its private copies.
4. - [x] Update `workflow/debug-data-view.md` Phase 1 to surface the new endpoint as the fast path (CLI remains a fallback for offline use).
5. - [x] Lint the changed files.
6. - [x] Live validation: hit the new endpoint locally (or document via curl example) with both a `table_state` body and a `/u/{hash}` body; confirm both return matching `query`, and that `execute: true` returns rows.