Database Inspect

Query the Neon Postgres database using psql via the DATABASE_URL environment variable. All access is strictly read-only — never run INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or any mutating statement.

Connection

psql "$DATABASE_URL" -c "<query>"

If DATABASE_URL is not set, check .env.local:

source .env.local 2>/dev/null && psql "$DATABASE_URL" -c "<query>"

Common Queries

Schema & Structure

# List all tables
psql "$DATABASE_URL" -c "\dt"

# Describe a specific table (columns, types, constraints)
psql "$DATABASE_URL" -c "\d labels"
psql "$DATABASE_URL" -c "\d application_data"
psql "$DATABASE_URL" -c "\d validation_items"

# List enums
psql "$DATABASE_URL" -c "SELECT typname, enumlabel FROM pg_enum JOIN pg_type ON pg_enum.enumtypid = pg_type.oid ORDER BY typname, enumsortorder;"

Row Counts

# Quick counts for all main tables
psql "$DATABASE_URL" -c "
  SELECT 'users' as tbl, count(*) FROM users
  UNION ALL SELECT 'applicants', count(*) FROM applicants
  UNION ALL SELECT 'batches', count(*) FROM batches
  UNION ALL SELECT 'labels', count(*) FROM labels
  UNION ALL SELECT 'label_images', count(*) FROM label_images
  UNION ALL SELECT 'application_data', count(*) FROM application_data
  UNION ALL SELECT 'validation_results', count(*) FROM validation_results
  UNION ALL SELECT 'validation_items', count(*) FROM validation_items
  UNION ALL SELECT 'human_reviews', count(*) FROM human_reviews
  UNION ALL SELECT 'settings', count(*) FROM settings
  UNION ALL SELECT 'accepted_variants', count(*) FROM accepted_variants
  ORDER BY tbl;
"

Labels

# Labels by status
psql "$DATABASE_URL" -c "SELECT status, count(*) FROM labels GROUP BY status ORDER BY count DESC;"

# Labels by beverage type
psql "$DATABASE_URL" -c "SELECT beverage_type, count(*) FROM labels GROUP BY beverage_type;"

# Recent labels (last 10)
psql "$DATABASE_URL" -c "SELECT id, status, beverage_type, overall_confidence, created_at FROM labels ORDER BY created_at DESC LIMIT 10;"

# Single label full detail
psql "$DATABASE_URL" -c "SELECT l.*, ad.brand_name, ad.fanciful_name, ad.class_type, ad.alcohol_content, ad.net_contents FROM labels l JOIN application_data ad ON ad.label_id = l.id WHERE l.id = '<label-id>';"

# Labels with expiring deadlines
psql "$DATABASE_URL" -c "SELECT id, status, correction_deadline, deadline_expired FROM labels WHERE correction_deadline IS NOT NULL AND deadline_expired = false ORDER BY correction_deadline ASC LIMIT 20;"

# Priority resubmissions
psql "$DATABASE_URL" -c "SELECT id, prior_label_id, status, is_priority FROM labels WHERE prior_label_id IS NOT NULL ORDER BY created_at DESC LIMIT 10;"

Validation Items

# Validation items for a specific label (current results only)
psql "$DATABASE_URL" -c "
  SELECT vi.field_name, vi.status, vi.confidence, vi.expected_value, vi.extracted_value
  FROM validation_items vi
  JOIN validation_results vr ON vi.validation_result_id = vr.id
  WHERE vr.label_id = '<label-id>' AND vr.is_current = true
  ORDER BY vi.field_name;
"

# Most common failing fields
psql "$DATABASE_URL" -c "SELECT field_name, count(*) FROM validation_items WHERE status = 'mismatch' GROUP BY field_name ORDER BY count DESC;"

Review Queue

# Pending reviews (review queue depth)
psql "$DATABASE_URL" -c "SELECT count(*) as queue_depth FROM labels WHERE status = 'needs_correction';"

# Human review history
psql "$DATABASE_URL" -c "SELECT hr.id, u.name as specialist, hr.original_status, hr.resolved_status, hr.reviewed_at FROM human_reviews hr JOIN users u ON hr.specialist_id = u.id ORDER BY hr.reviewed_at DESC LIMIT 10;"

Applicants

# Applicant compliance stats
psql "$DATABASE_URL" -c "
  SELECT a.company_name,
    count(l.id) as total_labels,
    count(CASE WHEN l.status = 'approved' THEN 1 END) as approved,
    round(100.0 * count(CASE WHEN l.status = 'approved' THEN 1 END) / NULLIF(count(l.id), 0), 1) as approval_rate
  FROM applicants a
  LEFT JOIN labels l ON l.applicant_id = a.id
  GROUP BY a.id, a.company_name
  ORDER BY total_labels DESC;
"

Users & Specialists

# List all users with roles
psql "$DATABASE_URL" -c "SELECT id, name, email, role FROM users ORDER BY role, name;"

# Labels per specialist
psql "$DATABASE_URL" -c "SELECT u.name, count(l.id) as labels_processed FROM users u LEFT JOIN labels l ON l.specialist_id = u.id WHERE u.role = 'specialist' GROUP BY u.id, u.name ORDER BY labels_processed DESC;"

Settings

# Current validation settings
psql "$DATABASE_URL" -c "SELECT key, value FROM settings ORDER BY key;"

# Accepted variants
psql "$DATABASE_URL" -c "SELECT field_name, canonical_value, variant_value FROM accepted_variants ORDER BY field_name, canonical_value;"

Dashboard Stats

# Quick dashboard summary
psql "$DATABASE_URL" -c "
  SELECT
    (SELECT count(*) FROM labels) as total_labels,
    (SELECT count(*) FROM labels WHERE status = 'approved') as approved,
    (SELECT count(*) FROM labels WHERE status = 'needs_correction') as needs_correction,
    (SELECT count(*) FROM labels WHERE status = 'rejected') as rejected,
    (SELECT count(*) FROM labels WHERE status = 'conditionally_approved') as conditionally_approved,
    (SELECT count(*) FROM labels WHERE correction_deadline IS NOT NULL AND deadline_expired = false AND correction_deadline < now() + interval '7 days') as expiring_soon,
    (SELECT count(*) FROM human_reviews) as total_reviews,
    (SELECT count(*) FROM batches) as total_batches;
"

Rules

1. READ-ONLY — never run INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or any DDL/DML that modifies data
2. Use LIMIT on exploratory queries to avoid dumping huge result sets
3. Use \x (expanded display) for wide rows: psql "$DATABASE_URL" -c "\x" -c "SELECT * FROM labels WHERE id = '...'"
4. If a query returns too many rows, add LIMIT 20 or filter with WHERE
5. For the schema source of truth, read src/db/schema.ts — it defines all tables, columns, enums, and relationships