Supabase PostgreSQL schema reference, connection patterns, and pre-built query templates for the Tibetan Spirit operations database. Load this skill whenever you need to query the database, write data, or understand the data model. Every skill that touches business data should reference this skill.
SUPABASE_URL environment variableSUPABASE_SERVICE_KEY (bypasses RLS) for agent system operationslib/ts_shared/supabase_client.pyThe database extends Shopify's data model with fields Shopify can't store natively (COGS, competitive intel, cross-channel inventory, supplier payments). A lightweight sync process keeps Supabase in sync with Shopify's canonical data.
Read agents/shared/supabase-ops-db/schema.sql for the full DDL. Key tables:
products — Master product catalog
shopify_id, title, , , , handleskupricestatuscogs_confirmed, cogs_estimated, freight_per_unit, duty_rate, duty_hs_code, margin_floor_by_channel (JSONB), cogs_confidence (enum: confirmed/estimated/unknown)inventory_extended — Cross-channel inventory view
product_id, sku, total_on_hand, shopify_available, fba_allocated, fba_in_transit, nepal_pipeline, nepal_eta, reorder_trigger_qty, safety_stockorders — Order history across all channels
channel enum: shopify, etsy, amazon, wholesalefulfillment_status, fulfillment_route (domestic/mexico/nepal)competitive_intel — Competitor pricing data
product_category, competitor_name, competitor_url, price, last_checked, sourcesupplier_payments — Nepal supplier payment tracking
supplier_name, invoice_number, amount_npr, amount_usd, exchange_rate, payment_status, payment_method, due_datemarketing_performance — Daily marketing snapshots
date, channel, campaign_id, ad_spend, revenue, roas, cpc, ctr, impressions, clicksskill_invocations — Audit trail for every AI action
id, timestamp, agent_name, skill_name, skill_version, trigger_source (webhook/cron/manual)raw_input (JSONB), raw_output (JSONB), structured_result (JSONB)model_used, input_tokens, output_tokens, cached_tokens, cost_usd, latency_msconfidence_score, phase (1 or 2), human_approved (boolean), error (text)Refreshed via pg_cron on Supabase:
channel_profitability_monthly — P&L by channel: revenue, COGS, fees, shipping, gross profit, margin %
product_margin_detail — Per-SKU margin including COGS, channel fees, shipping estimate
inventory_health — Days of supply, reorder urgency, stockout risk by SKU
marketing_roas_trailing — 7-day and 30-day trailing ROAS by channel and campaign
See agents/shared/supabase-ops-db/queries/ for ready-to-use SQL:
product-margin.sql — Get margin by SKU with COGS confidenceinventory-availability.sql — Cross-channel availability including FBA and in-transitcompetitive-position.sql — Price position vs. competitors by categorychannel-profitability.sql — True P&L by channel after all feesWhen writing queries against this database:
status = 'active' on the products table unless explicitly analyzing archived itemsON CONFLICT DO NOTHING or check for existing records)skill_invocations with the full context