From opengov-garden
Manages database operations across SQLite (local state) and Supabase (remote storage) for the OpenGov Harvester.
How this skill is triggered — by the user, by Claude, or both
Slash command
/opengov-garden:database-opsThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Manages database operations across SQLite (local state) and Supabase (remote storage) for the OpenGov Harvester.
Manages database operations across SQLite (local state) and Supabase (remote storage) for the OpenGov Harvester.
Path: data/db/opengov_state.db
Mode: WAL (Write-Ahead Logging)
Purpose: Local state management, queue coordination, session persistence
Key Tables:
opengov_projects: Project inventory with extraction statusopengov_opportunities: Opportunity recordsopengov_followers: Follower datasession_state: Auth session persistenceextraction_queue: Work queue with prioritiesProject: uzczjsmhitnwtsmmffqc (1st-Commercial-Credit)
Region: us-west-2
Schemas: public, opengov
Key Tables:
public.users: User accounts with RBACpublic.user_groups: Group assignmentsopengov.api_discovery: Discovered API endpointsopengov.project_metadata: Project details with versioningCheck Extraction Status:
sqlite3 data/db/opengov_state.db "
SELECT
project_id,
name,
extracted,
updated_at
FROM opengov_projects
WHERE extracted = 1
ORDER BY updated_at DESC
LIMIT 10;
"
Count Opportunities:
sqlite3 data/db/opengov_state.db "
SELECT COUNT(*) as total_opportunities
FROM opengov_opportunities;
"
View Queue Status:
sqlite3 data/db/opengov_state.db "
SELECT
status,
COUNT(*) as count
FROM extraction_queue
GROUP BY status;
"
Pull Remote Schema:
supabase db pull
Create Migration:
supabase migration new add_feature_name
Apply Migrations Locally:
supabase db reset
Push to Remote:
supabase db push
Execute SQL Query:
# Via MCP tool (preferred)
mcp__plugin_supabase_supabase__execute_sql
# Via psql
PGPASSWORD=postgres psql -h 127.0.0.1 -p 54322 -U postgres -d postgres -c "SELECT * FROM opengov.project_metadata LIMIT 5;"
import sqlite3
def get_extraction_status(project_id: str) -> bool:
conn = sqlite3.connect('data/db/opengov_state.db')
conn.execute('PRAGMA journal_mode=WAL')
try:
with conn:
result = conn.execute(
"SELECT extracted FROM opengov_projects WHERE project_id = ?",
(project_id,)
).fetchone()
return bool(result[0]) if result else False
finally:
conn.close()
from supabase import create_client
url = os.getenv("SUPABASE_URL")
key = os.getenv("SUPABASE_SERVICE_ROLE_KEY")
supabase = create_client(url, key)
# Insert data
data = supabase.table("opengov.project_metadata").insert({
"project_id": "abc123",
"name": "City Project",
"version": 1
}).execute()
# Query with filters
projects = supabase.table("opengov.project_metadata")\
.select("*")\
.eq("extracted", True)\
.limit(10)\
.execute()
Schema Change:
-- supabase/migrations/20260125_add_version_tracking.sql
-- Add version column to track changes
ALTER TABLE opengov.project_metadata
ADD COLUMN version INTEGER DEFAULT 1;
-- Create index for version queries
CREATE INDEX idx_project_version ON opengov.project_metadata(project_id, version);
-- Update RLS policies if needed
ALTER POLICY "Users can read their projects" ON opengov.project_metadata
USING (user_id = auth.uid() OR is_public = true);
Data Migration:
-- supabase/migrations/20260125_backfill_versions.sql
-- Backfill version numbers for existing records
UPDATE opengov.project_metadata
SET version = 1
WHERE version IS NULL;
supabase migration new <name>supabase/migrations/<timestamp>_<name>.sqlsupabase db resetsupabase db pushdatabase-transaction-safety.md: Safe transaction patternsetl-atomic-writes.md: Atomic data operationsdata-quality-checks.md: Data validationdata-lineage-metadata.md: Metadata trackingetl-idempotency-patterns.md: Safe re-runs# Supabase stats
supabase inspect db db-stats
supabase inspect db table-stats
supabase inspect db index-stats
# SQLite stats
sqlite3 data/db/opengov_state.db "
SELECT
name,
(SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND tbl_name=m.name) as indexes,
(SELECT COUNT(*) FROM pragma_table_info(m.name)) as columns
FROM sqlite_master m
WHERE type='table';
"
# Long-running queries
supabase inspect db long-running-queries
# Query outliers
supabase inspect db outliers
# Blocking queries
supabase inspect db blocking
# Create backup
sqlite3 data/db/opengov_state.db ".backup data/db/backups/opengov_$(date +%Y%m%d_%H%M%S).db"
# Restore from backup
sqlite3 data/db/opengov_state.db ".restore data/db/backups/opengov_20260125_120000.db"
# Dump schema
supabase db dump -f supabase/backups/schema_$(date +%Y%m%d).sql
# Dump data
supabase db dump --data-only -f supabase/backups/data_$(date +%Y%m%d).sql
Symptom: "database is locked" error Solution:
PRAGMA journal_mode=WALSymptom: Remote schema diverged from local Solution:
supabase db pullsupabase db resetsupabase db pushSymptom: Cannot connect to Supabase Solution:
supabase statussupabase stop && supabase start# Always use transactions for multi-step operations
with conn:
conn.execute("INSERT INTO projects ...")
conn.execute("UPDATE queue SET status ...")
# Automatic commit on success, rollback on error
# Use prepared statements
cursor.execute(
"SELECT * FROM projects WHERE id = ?",
(project_id,)
)
# Batch inserts
cursor.executemany(
"INSERT INTO opportunities VALUES (?, ?, ?)",
opportunity_batch
)
from sqlite3 import IntegrityError, OperationalError
try:
with conn:
conn.execute("INSERT ...")
except IntegrityError:
logger.warning("Duplicate record, skipping")
except OperationalError:
logger.error("Database locked, retrying with backoff")
# Implement exponential backoff
npx claudepluginhub heaney-investments/opengov-garden --plugin opengov-gardenRuns SQL queries, schema changes, RLS policies, and storage operations against a Supabase project via its Management API. Useful for DB CRUD, migrations, and admin-level data tasks.
Executes Supabase incident response: platform status, connection pool checks, pg_stat_activity queries, RLS debugging, Edge Function logs, storage health, and escalation. For outages, errors, and connection issues.
Automates Supabase database queries, table management, project administration, storage, edge functions, and SQL execution via Rube MCP (Composio). Always searches tools first for current schemas.