Agent skill

Query Supabase Database

Execute PostgreSQL queries against the Supabase database using psql with 1Password credential retrieval

View SKILL.md on GitHub Repository
Stars 0
Forks 0

Install this agent skill to your Project

npx add-skill https://github.com/bigandslow/cproj/tree/main/.claude/skills/query-supabase

SKILL.md

Query Supabase Database

Purpose

Provides a standardized way to query the Supabase PostgreSQL database:

  • Execute SQL queries with secure credential retrieval
  • View table structures and data
  • Manage database records (INSERT, UPDATE, DELETE)
  • Test migrations and schema changes

When to Use

  • Verifying migration results
  • Debugging data issues
  • Inspecting table structures
  • Running ad-hoc queries
  • Testing RLS policies

Connection Pattern

All database queries use this connection string format:

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SQL QUERY HERE"

Important:

  • Single quotes around the 1Password reference: 'op://...'
  • Double quotes around the SQL query: "SELECT ..."
  • Use the pooler connection for better performance

Common Operations

List Tables

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "\dt"

Describe Table Structure

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "\d table_name"

View Organizations

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT id, name, domain FROM organizations;"

View User Profiles

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT id, email, full_name, role, organization_id FROM profiles;"

View Integrations

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT id, organization_id, integration_type, name, auth_mode FROM integrations;"

Data Manipulation

Insert Organization

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "INSERT INTO organizations (name, slug, plan) VALUES ('Company Name', 'company-slug', 'pay_as_you_go') RETURNING id, name;"

Update Record

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "UPDATE integrations SET auth_mode = 'realtime_approval' WHERE id = 'uuid-here' RETURNING id, name, auth_mode;"

Delete Record

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "DELETE FROM table_name WHERE id = 'uuid-here' RETURNING id;"

Running SQL Files

For migrations or complex scripts:

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -f path/to/script.sql

Investigation Tables

View Workflow Runs

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT id, workflow_id, status, started_at, metadata FROM workflow_runs ORDER BY started_at DESC LIMIT 10;"

View Bank Transactions

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT id, bank_txn_id, account_id, date, amount, memo FROM bank_transactions ORDER BY date DESC LIMIT 10;"

Test Full-Text Search

bash
psql "postgresql://postgres.oegxmnknuqvibndnrgou:$(op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass')@aws-1-us-east-2.pooler.supabase.com:5432/postgres" -c "SELECT bank_txn_id, memo, name FROM bank_transactions WHERE search_vector @@ websearch_to_tsquery('english', 'interest payment');"

Key Tables

  • organizations: Organization/tenant records
  • profiles: User profiles (linked to auth.users)
  • integrations: External system integrations (NetSuite, QuickBooks, etc.)
  • workflows: Temporal workflow execution records
  • sso_connections: SSO/SAML connections via WorkOS
  • workflow_runs: Bank reconciliation workflow executions (investigation)
  • bank_transactions: Imported bank statements (investigation)
  • gl_transactions: General ledger entries (investigation)
  • match_results: Transaction matching results (investigation)
  • created_transactions: JEs/transfers/checks created (investigation)
  • investigation_conversations: Chat conversations
  • investigation_messages: Chat messages

Best Practices

  1. Always use RETURNING clause for INSERT/UPDATE/DELETE to see results
  2. Test with LIMIT when querying large tables
  3. Use transaction blocks for complex operations:
    sql
    BEGIN;
-- your queries here
ROLLBACK; -- or COMMIT;
  4. Check RLS policies - queries may return empty if user context isn't set
  5. Use pg_catalog for metadata queries to avoid RLS issues

Troubleshooting

Connection Issues

  • Verify 1Password CLI is installed: op --version
  • Verify you're signed in: op whoami
  • Test credential retrieval: op read 'op://Private/wtoof5i5k7jiap6gnzmg3n7u5m/dbPass'

Empty Results

  • Check if RLS is enabled: SELECT tablename, rowsecurity FROM pg_tables WHERE tablename = 'your_table';
  • Verify organization_id: SELECT organization_id FROM profiles WHERE id = auth.uid();

Performance

  • Use EXPLAIN ANALYZE to check query plans
  • Verify indexes exist: \di table_name*
  • Check for sequential scans on large tables

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

bigandslow/cproj

GitHub Security Alert Fixer

Systematically analyzes and fixes GitHub CodeQL security alerts with proper documentation and testing

0 0
Explore
bigandslow/cproj

GitHub Security Alert Fixer

Systematically analyzes and fixes GitHub CodeQL security alerts with proper documentation and testing

0 0
Explore
mattpocock/skills

setup-pre-commit

Set up Husky pre-commit hooks with lint-staged (Prettier), type checking, and tests in the current repo. Use when user wants to add pre-commit hooks, set up Husky, configure lint-staged, or add commit-time formatting/typechecking/testing.

111,310 9,758
Explore
mattpocock/skills

edit-article

Edit and improve articles by restructuring sections, improving clarity, and tightening prose. Use when user wants to edit, revise, or improve an article draft.

111,310 9,758
Explore
mattpocock/skills

obsidian-vault

Search, create, and manage notes in the Obsidian vault with wikilinks and index notes. Use when user wants to find, create, or organize notes in Obsidian.

111,310 9,758
Explore
mattpocock/skills

handoff

Compact the current conversation into a handoff document for another agent to pick up.

111,310 9,758
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results