Skip to main content

Common Queries

Tested-in-production SQL for the questions we ask the DB most often.

Connect​

PGPASSWORD=Ka26Mkt2026 psql -h 34.123.40.64 -U ka26user -d ka26

(Credentials in 1Password — never commit them.)

Health snapshot​

SELECT
  (SELECT count(*) FROM "Consumer") AS consumers,
  (SELECT count(*) FROM "Seller" WHERE status='active') AS active_sellers,
  (SELECT count(*) FROM "Seller" WHERE status='pending') AS pending_sellers,
  (SELECT count(*) FROM "Store" WHERE "isActive") AS active_stores,
  (SELECT count(*) FROM "Product" WHERE status='available' AND "storeId" IS NOT NULL) AS active_store_products,
  (SELECT count(*) FROM "Product" WHERE status='available' AND "consumerId" IS NOT NULL) AS active_ads,
  (SELECT count(*) FROM "Reel") AS reels,
  (SELECT count(*) FROM "Request" WHERE status='active') AS active_requests,
  (SELECT count(*) FROM "StoreOrder" WHERE "createdAt" > NOW() - INTERVAL '24 hours') AS orders_24h;

Today's orders by store​

SELECT
  s.name AS store,
  count(o.id) AS orders,
  sum(o.total)::numeric(10,2) AS gmv,
  sum(o."platformFee")::numeric(10,2) AS commission_earned
FROM "StoreOrder" o
JOIN "Store" s ON s.id = o."storeId"
WHERE o."createdAt" > date_trunc('day', NOW())
GROUP BY s.name
ORDER BY gmv DESC;

Cancelled orders + reasons (last 7 days)​

SELECT
  o."orderNumber",
  s.name AS store,
  o.total,
  o."cancelledAt",
  o."cancellationReason"
FROM "StoreOrder" o
JOIN "Store" s ON s.id = o."storeId"
WHERE o.status='cancelled' AND o."cancelledAt" > NOW() - INTERVAL '7 days'
ORDER BY o."cancelledAt" DESC;

Email send health (last 24 hours)​

SELECT
  kind,
  count(*) FILTER (WHERE status='sent')   AS sent,
  count(*) FILTER (WHERE status='failed') AS failed,
  round(100.0 * count(*) FILTER (WHERE status='sent') / count(*), 1) AS pct_ok
FROM "EmailLog"
WHERE "createdAt" > NOW() - INTERVAL '24 hours'
GROUP BY kind
ORDER BY kind;

Recent email failures​

SELECT "createdAt", kind, "to", error
FROM "EmailLog"
WHERE status='failed' AND "createdAt" > NOW() - INTERVAL '24 hours'
ORDER BY "createdAt" DESC
LIMIT 50;

Top creators by reel earnings (pending + confirmed)​

SELECT
  c.name,
  c.alias,
  count(DISTINCT r.id) AS reels_count,
  sum(e.amount) FILTER (WHERE e.status='pending')::numeric(10,2) AS pending_earnings,
  sum(e.amount) FILTER (WHERE e.status='confirmed')::numeric(10,2) AS confirmed_earnings
FROM "Consumer" c
JOIN "Reel" r ON r."consumerId" = c.id
LEFT JOIN "ReelEarning" e ON e."reelId" = r.id
GROUP BY c.id, c.name, c.alias
HAVING sum(e.amount) IS NOT NULL
ORDER BY confirmed_earnings DESC NULLS LAST
LIMIT 20;

Stuck pending payments​

SELECT id, "merchantTransactionId", amount, status, "createdAt"
FROM "Payment"
WHERE status='pending' AND "createdAt" < NOW() - INTERVAL '5 minutes'
ORDER BY "createdAt" DESC;

These should be auto-cancelled by /api/payments/cleanup cron — if you see many, the cron isn't running.

Public discussions activity (last 7 days)​

SELECT
  r.id,
  r.title,
  r."createdAt",
  count(DISTINCT m."consumerId") AS unique_participants,
  count(m.id) AS message_count
FROM "Request" r
JOIN "RequestThread" t ON t."requestId" = r.id
LEFT JOIN "RequestMessage" m ON m."threadId" = t.id
WHERE r."discussionType" = 'public' AND r."createdAt" > NOW() - INTERVAL '7 days'
GROUP BY r.id
ORDER BY message_count DESC;

Seller approval queue​

SELECT id, name, email, "whatsappNumber", "createdAt"
FROM "Seller"
WHERE status='pending'
ORDER BY "createdAt" ASC;

Find a user by phone​

SELECT 'consumer' AS type, id, name, email, phone FROM "Consumer" WHERE phone = '+91XXXXXXXXXX'
UNION ALL
SELECT 'seller' AS type, id, name, email, "whatsappNumber" FROM "Seller" WHERE "whatsappNumber" = '+91XXXXXXXXXX'
UNION ALL
SELECT 'doctor' AS type, id, name, email, phone FROM "Doctor" WHERE phone = '+91XXXXXXXXXX';

DANGER queries (never run on prod without backup verified)​

-- Cascade delete a consumer (used by admin)
-- This is what /api/admin/users/[id] DELETE does, in correct FK order
BEGIN;
DELETE FROM "ReelLike" WHERE "consumerId" = X;
DELETE FROM "ReelComment" WHERE "consumerId" = X;
DELETE FROM "ReelView" WHERE "consumerId" = X;
DELETE FROM "ReelTag" WHERE "reelId" IN (SELECT id FROM "Reel" WHERE "consumerId" = X);
DELETE FROM "Reel" WHERE "consumerId" = X;
-- ... 10+ more tables ...
DELETE FROM "Consumer" WHERE id = X;
COMMIT;

If you need to actually delete a user, USE the admin endpoint, don't write SQL by hand.