PgHero

Queries

Total Time Average Time Calls
0 min 25% 18,406 ms 1 postgres 
SELECT * FROM public.business_campaign_touch_events
group by id, campaign_id
0 min 12% 0 ms 47,682 postgres 
INSERT INTO business_campaign_touch_events
      (business_fk, campaign_id, brand_id, occurred_at, touch_type, source_system, source_ref, payload)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
     ON CONFLICT DO NOTHING
     RETURNING id
0 min 12% 0 ms 52,433 postgres 
INSERT INTO business_campaign_touches
       (business_fk, business_id_text, campaign_id, brand_id, touch_count, first_touch_at, last_touch_at, last_touch_type, last_touch_source)
     VALUES
       ($1,$2,$3,$4,$7,$5,$5,$8,$6)
     ON CONFLICT (business_fk, campaign_id, brand_id) DO UPDATE SET
       touch_count = business_campaign_touches.touch_count + $9,
       first_touch_at = COALESCE(LEAST(business_campaign_touches.first_touch_at, EXCLUDED.first_touch_at), EXCLUDED.first_touch_at),
       last_touch_at  = COALESCE(GREATEST(business_campaign_touches.last_touch_at,  EXCLUDED.last_touch_at),  EXCLUDED.last_touch_at),
       last_touch_source = COALESCE(EXCLUDED.last_touch_source, business_campaign_touches.last_touch_source),
       business_id_text = COALESCE(EXCLUDED.business_id_text, business_campaign_touches.business_id_text),
       updated_at = now()
0 min 9% 0 ms 92,748 postgres 
INSERT INTO business(business_id, business_sequence, name, website, website_normalized)
     VALUES ($1,$2,$3,$4,$5)
     ON CONFLICT (business_id) DO UPDATE SET
       business_sequence = COALESCE(EXCLUDED.business_sequence, business.business_sequence),
       name = COALESCE(EXCLUDED.name, business.name),
       website = COALESCE(EXCLUDED.website, business.website),
       website_normalized = COALESCE(EXCLUDED.website_normalized, business.website_normalized)
     RETURNING id
0 min 7% 0 ms 59,755 postgres 
INSERT INTO campaign_metrics_daily(day, campaign_id, brand_id, touches, paid_count, paid_cents)
     VALUES ($1,$2,$3,$4,$5,$6)
     ON CONFLICT (day, campaign_id, brand_id) DO UPDATE SET
       touches = campaign_metrics_daily.touches + EXCLUDED.touches,
       paid_count = campaign_metrics_daily.paid_count + EXCLUDED.paid_count,
       paid_cents = campaign_metrics_daily.paid_cents + EXCLUDED.paid_cents,
       updated_at = now()
0 min 5% 0 ms 444,080 postgres 
SELECT $1
0 min 4% 498 ms 6 postgres 
-- Make unmatched logging idempotent for reruns.
-- First, drop dupes that would block the unique partial index.
DELETE FROM unmatched_payment a
USING unmatched_payment b
WHERE a.ctid < b.ctid
  AND a.bubble_id IS NOT NULL
  AND a.source_app IS NOT DISTINCT FROM b.source_app
  AND a.bubble_id  IS NOT DISTINCT FROM b.bubble_id
0 min 3% 0 ms 24,053 postgres 
INSERT INTO campaign(code,name) VALUES ($1,$2)
     ON CONFLICT (code) DO UPDATE SET name=EXCLUDED.name
     RETURNING id
0 min 2% 884 ms 2 postgres 
SELECT COUNT(*)::text FROM public.id_lookup
0 min 2% 0 ms 10,983 postgres 
INSERT INTO payment
      (business_fk, business_id_text, campaign_code, pincode, brand_domain, brand_id,
       amount_cents, paid_at, created_at, modified_at, refunded, payment_type,
       source_app, bubble_id, source_ref, matched_business, raw_data, normalization_meta)
     VALUES
      ($1,$2,$3,$4,$5,$6,
       $7,$8,$9,$10,$11,$12,
       $13,$14,$15,$16,$17,$18)
     ON CONFLICT (source_app, bubble_id) DO UPDATE SET
       -- keep the earliest inserted row but allow backfill of linkage
       business_fk = COALESCE(EXCLUDED.business_fk, payment.business_fk),
       business_id_text = COALESCE(EXCLUDED.business_id_text, payment.business_id_text),
       campaign_code = COALESCE(EXCLUDED.campaign_code, payment.campaign_code),
       pincode = COALESCE(EXCLUDED.pincode, payment.pincode),
       brand_domain = COALESCE(EXCLUDED.brand_domain, payment.brand_domain),
       brand_id = COALESCE(EXCLUDED.brand_id, payment.brand_id),
       amount_cents = COALESCE(EXCLUDED.amount_cents, payment.amount_cents),
       paid_at = COALESCE(EXCLUDED.paid_at, payment.paid_at),
       modified_at = GREATEST(COALESCE(payment.modified_at, EXCLUDED.modified_at), COALESCE(EXCLUDED.modified_at, payment.modified_at)),
       refunded = COALESCE(EXCLUDED.refunded, payment.refunded),
       payment_type = COALESCE(EXCLUDED.payment_type, payment.payment_type),
       matched_business = payment.matched_business OR EXCLUDED.matched_business,
       raw_data = COALESCE(payment.raw_data, $19::jsonb) || COALESCE(EXCLUDED.raw_data, $20::jsonb),
       normalization_meta = COALESCE(payment.normalization_meta, $21::jsonb) || COALESCE(EXCLUDED.normalization_meta, $22::jsonb)
     RETURNING id
0 min 2% 0 ms 90,860 postgres 
SELECT $2 FROM ONLY "public"."business" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
0 min 2% 0 ms 107,339 postgres 
SELECT $2 FROM ONLY "public"."campaign" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
0 min 2% 12 ms 102 postgres 
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
0 min 2% 0 ms 14,498 postgres 
INSERT INTO brand(key,name) VALUES ($1,$2)
     ON CONFLICT (key) DO UPDATE SET name=EXCLUDED.name
     RETURNING id
0 min 2% 0 ms 110,424 postgres 
SELECT $2 FROM ONLY "public"."brand" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
0 min 1% 0 ms 5,370 postgres 
INSERT INTO business_campaign_touches
       (business_fk, campaign_id, brand_id, touch_count, first_touch_at, last_touch_at, last_touch_type, last_touch_source)
     VALUES
       ($1,$2,$3,$6,$4,$4,$7,$5)
     ON CONFLICT (business_fk, campaign_id, brand_id) DO UPDATE SET
       touch_count = business_campaign_touches.touch_count + $8,
       first_touch_at = COALESCE(LEAST(business_campaign_touches.first_touch_at, EXCLUDED.first_touch_at), EXCLUDED.first_touch_at),
       last_touch_at  = COALESCE(GREATEST(business_campaign_touches.last_touch_at,  EXCLUDED.last_touch_at),  EXCLUDED.last_touch_at),
       last_touch_source = COALESCE(EXCLUDED.last_touch_source, business_campaign_touches.last_touch_source),
       updated_at = now()
0 min 1% 762 ms 1 postgres 
SELECT * FROM public.unmatched_payment
ORDER BY id ASC
Covered by index on (id)
Rows: 3837
Row progression: 3837, 1

Row estimates
- id (sort): 1

Existing indexes
- id PRIMARY
- source_app, bubble_id UNIQUE
- source_app, bubble_id WHERE bubble_id IS NOT NULL UNIQUE
0 min 1.0% 147 ms 5 postgres 
SELECT n.nspname as "schema",
        p.proname as "name",
        d.description,
        pg_catalog.pg_get_function_result(p.oid) as "result_type",
        pg_catalog.pg_get_function_arguments(p.oid) as "argument_types",
      CASE
        WHEN p.prokind = $1 THEN $2
        WHEN p.prokind = $3 THEN $4
        WHEN p.prorettype = $5::pg_catalog.regtype THEN $6
        ELSE $7
      END as "type"
      FROM pg_catalog.pg_proc p
          LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
          LEFT JOIN pg_catalog.pg_description d ON p.oid = d.objoid
      WHERE n.nspname <> $8
        AND pg_catalog.pg_function_is_visible(p.oid)
        AND p.prorettype <> $9::pg_catalog.regtype
        AND has_schema_privilege(n.oid, $10) = $11
        AND has_function_privilege(p.oid, $12) = $13
      ORDER BY 1, 2, 4
0 min 0.9% 0 ms 2,604 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Total",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $4))  AS "Active",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $5 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6))  AS "Idle"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $8)) AS "Transactions",
   (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $9)) AS "Commits",
   (SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $10)) AS "Rollbacks"
) t
UNION ALL
SELECT $11 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $12)) AS "Inserts",
   (SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $13)) AS "Updates",
   (SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $14)) AS "Deletes"
) t
UNION ALL
SELECT $15 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $16)) AS "Fetched",
   (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $17)) AS "Returned"
) t
UNION ALL
SELECT $18 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $19)) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $20)) AS "Hits"
) t
0 min 0.7% 250 ms 2 postgres 
SELECT COUNT(*)::text FROM public.business_reconciliation
0 min 0.4% 0 ms 1,953 postgres 
INSERT INTO business_campaign_payment_rollup
        (business_fk, campaign_id, last_payment_at, total_paid_cents, payment_count)
       VALUES ($1,$2,$3,$4,$5)
       ON CONFLICT (business_fk, campaign_id) DO UPDATE SET
         last_payment_at = GREATEST(COALESCE(business_campaign_payment_rollup.last_payment_at, EXCLUDED.last_payment_at), EXCLUDED.last_payment_at),
         total_paid_cents = business_campaign_payment_rollup.total_paid_cents + EXCLUDED.total_paid_cents,
         payment_count = business_campaign_payment_rollup.payment_count + $6,
         updated_at = now()
0 min 0.4% 0 ms 3,048 postgres 
INSERT INTO unmatched_payment(source_app, bubble_id, reason, payload)
     VALUES ($1,$2,$3,$4)
     ON CONFLICT (source_app, bubble_id) DO UPDATE SET
       reason = EXCLUDED.reason,
       payload = EXCLUDED.payload
0 min 0.3% 0 ms 5,985 postgres 
INSERT INTO unmatched_payment(source_app, bubble_id, reason, payload)
             VALUES ($1,$2,$3,$4)
0 min 0.3% 1 ms 164 postgres 
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)
0 min 0.3% 0 ms 3,445 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Fetched",
   (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $3)) AS "Returned"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $5)) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6)) AS "Hits"
) t
0 min 0.3% 0 ms 863 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Total",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $4))  AS "Active",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $5 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6))  AS "Idle"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $8)) AS "Transactions",
   (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $9)) AS "Commits",
   (SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $10)) AS "Rollbacks"
) t
UNION ALL
SELECT $11 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $12)) AS "Inserts",
   (SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $13)) AS "Updates",
   (SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $14)) AS "Deletes"
) t
UNION ALL
SELECT $15 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $16)) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $17)) AS "Hits"
) t
0 min 0.2% 2 ms 97 postgres 
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/
0 min 0.2% 0 ms 3,429 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $3)) AS "Hits"
) t
0 min 0.2% 0 ms 1,953 postgres 
INSERT INTO business_payment_rollup
      (business_fk, last_payment_at, total_paid_cents, payment_count)
     VALUES ($1,$2,$3,$4)
     ON CONFLICT (business_fk) DO UPDATE SET
       last_payment_at = GREATEST(COALESCE(business_payment_rollup.last_payment_at, EXCLUDED.last_payment_at), EXCLUDED.last_payment_at),
       total_paid_cents = business_payment_rollup.total_paid_cents + EXCLUDED.total_paid_cents,
       payment_count = business_payment_rollup.payment_count + $5,
       updated_at = now()
0 min 0.2% 2 ms 93 postgres 
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
0 min 0.2% 124 ms 1 postgres 
SELECT * FROM public.business_campaign_touches
ORDER BY id ASC
Covered by index on (id)
Rows: 43794
Row progression: 43794, 1

Row estimates
- id (sort): 1

Existing indexes
- id PRIMARY
- brand_id, campaign_id, last_touch_at
- business_fk, campaign_id, brand_id UNIQUE
- business_fk, last_touch_at DESC
- business_id_text
- campaign_id, last_touch_at
0 min 0.1% 9 ms 10 postgres 
CREATE UNIQUE INDEX IF NOT EXISTS unmatched_payment_source_bubble_uniq
  ON unmatched_payment (source_app, bubble_id)
  WHERE bubble_id IS NOT NULL
0 min 0.1% 45 ms 2 postgres 
SELECT id, business_id FROM business WHERE business_id IS NOT NULL
0 min 0.1% 86 ms 1 postgres 
SELECT
  business_fk,
  business_id_text,

  SUM(touch_count)              AS total_touches,
  MIN(first_touch_at)           AS first_touch_at,
  MAX(last_touch_at)            AS last_touch_at,

  -- pick fields from the most recent touch
  (ARRAY_AGG(last_touch_type ORDER BY last_touch_at DESC))[$1]   AS last_touch_type,
  (ARRAY_AGG(last_touch_source ORDER BY last_touch_at DESC))[$2] AS last_touch_source,

  MAX(updated_at)               AS updated_at,
  MIN(created_at)               AS created_at

FROM public.business_campaign_touches
GROUP BY business_fk, business_id_text
0 min 0.1% 78 ms 1 postgres 
CREATE DATABASE acho_connect
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LOCALE_PROVIDER = 'libc'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False
0 min < 0.1% 8 ms 5 postgres 
SELECT
          tbl.schemaname,
          tbl.tablename,
          tbl.quoted_name,
          tbl.is_table,
          json_agg(a) as columns
        FROM
          (
            SELECT
              n.nspname as schemaname,
              c.relname as tablename,
              (quote_ident(n.nspname) || $1 || quote_ident(c.relname)) as quoted_name,
              $2 as is_table
            FROM
              pg_catalog.pg_class c
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE
              c.relkind = $3
              AND n.nspname not in ($4, $5, $6)
              AND n.nspname not like $7
              AND n.nspname not like $8
              AND c.relnatts > $9
              AND has_schema_privilege(n.oid, $10) = $11
              AND has_table_privilege(quote_ident(n.nspname) || $12 || quote_ident(c.relname), $13) = $14
            union all
            SELECT
              n.nspname as schemaname,
              c.relname as tablename,
              (quote_ident(n.nspname) || $15 || quote_ident(c.relname)) as quoted_name,
              $16 as is_table
            FROM
              pg_catalog.pg_class c
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE
              c.relkind in ($17, $18)
              AND n.nspname not in ($19, $20, $21)
              AND n.nspname not like $22
              AND n.nspname not like $23
              AND has_schema_privilege(n.oid, $24) = $25
              AND has_table_privilege(quote_ident(n.nspname) || $26 || quote_ident(c.relname), $27) = $28
          ) as tbl
          LEFT JOIN (
            SELECT
              attrelid,
              attname,
              format_type(atttypid, atttypmod) as data_type,
              attnum,
              attisdropped
            FROM
              pg_attribute
          ) as a ON (
            a.attrelid = tbl.quoted_name::regclass
            AND a.attnum > $29
            AND NOT a.attisdropped
            AND has_column_privilege(tbl.quoted_name, a.attname, $30)
          )
        GROUP BY schemaname, tablename, quoted_name, is_table
0 min < 0.1% 0 ms 164 postgres 
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typelem IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52)
0 min < 0.1% 0 ms 164 postgres 
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typtype IN ($1, $2, $3)
0 min < 0.1% 0 ms 128 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT count(*) FROM pg_catalog.pg_stat_activity) AS "Total",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $2)  AS "Active",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3)  AS "Idle"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Transactions",
   (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database) AS "Commits",
   (SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT $5 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database) AS "Inserts",
   (SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database) AS "Updates",
   (SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT $6 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database) AS "Fetched",
   (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
0 min < 0.1% 0 ms 164 postgres 
SET SESSION timezone TO 'UTC'
0 min < 0.1% 2 ms 14 postgres 
-- ============================================================
-- CYCL0PS MIGRATION SCHEMA (Postgres) - Revised naming
-- ============================================================

-- 0) Utilities
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql
0 min < 0.1% 0 ms 338 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database) AS "Fetched",
   (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT $2 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
0 min < 0.1% 0 ms 93 postgres 
SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*pghero*/
0 min < 0.1% 0 ms 332 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
0 min < 0.1% 4 ms 5 postgres 
SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
0 min < 0.1% 0 ms 87 postgres 
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT count(*) FROM pg_catalog.pg_stat_activity) AS "Total",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $2)  AS "Active",
   (SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3)  AS "Idle"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Transactions",
   (SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database) AS "Commits",
   (SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT $5 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database) AS "Inserts",
   (SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database) AS "Updates",
   (SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT $6 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
   (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
   (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
0 min < 0.1% 0 ms 164 postgres 
SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
0 min < 0.1% 1 ms 13 postgres 
-- ============================================================
-- 6) updated_at triggers
-- ============================================================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_business_updated_at') THEN
    CREATE TRIGGER trg_business_updated_at
    BEFORE UPDATE ON business
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_campaign_updated_at') THEN
    CREATE TRIGGER trg_campaign_updated_at
    BEFORE UPDATE ON campaign
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_brand_group_updated_at') THEN
    CREATE TRIGGER trg_brand_group_updated_at
    BEFORE UPDATE ON brand_group
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_brand_updated_at') THEN
    CREATE TRIGGER trg_brand_updated_at
    BEFORE UPDATE ON brand
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_brand_pattern_updated_at') THEN
    CREATE TRIGGER trg_brand_pattern_updated_at
    BEFORE UPDATE ON brand_pattern
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_brand_override_updated_at') THEN
    CREATE TRIGGER trg_brand_override_updated_at
    BEFORE UPDATE ON brand_override
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_bct_updated_at') THEN
    CREATE TRIGGER trg_bct_updated_at
    BEFORE UPDATE ON business_campaign_touches
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;
END $$
0 min < 0.1% 1 ms 14 postgres 
-- ============================================================
-- 1) MASTER TABLES
-- ============================================================

-- Business master
-- IMPORTANT mapping:
-- Mongo: business.public_id  -> Postgres: business.business_id (TEXT, unique)
-- Mongo: business.business_id (numeric) -> Postgres: business_sequence (BIGINT)
CREATE TABLE IF NOT EXISTS business (
  id                 BIGSERIAL PRIMARY KEY,
  business_id         TEXT UNIQUE NOT NULL,     -- e.g., 'AAAA108' (canonical public ID)
  business_sequence   BIGINT UNIQUE,            -- e.g., 108
  name               TEXT,
  website             TEXT,                     -- optional
  website_normalized  TEXT,                     -- optional
  agb_primary_id      TEXT UNIQUE,              -- optional external map if you still need it
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 2 ms 7 postgres 
-- ============================================================
-- 2) TOUCH EVENTS (source of truth, makes rollups idempotent)
-- ============================================================

CREATE TABLE IF NOT EXISTS business_campaign_touch_events (
  id            BIGSERIAL PRIMARY KEY,
  business_fk   BIGINT NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  campaign_id   BIGINT NOT NULL REFERENCES campaign(id) ON DELETE CASCADE,
  brand_id      BIGINT NOT NULL REFERENCES brand(id) ON DELETE CASCADE,

  occurred_at   TIMESTAMPTZ NOT NULL,
  touch_type    TEXT NOT NULL DEFAULT 'mail',

  source_system TEXT NOT NULL,     -- e.g., 'mongo_business.campaign_list'
  source_ref    TEXT NOT NULL,     -- e.g., pin_code or other stable ref
  payload       JSONB,

  inserted_at   TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 5 ms 3 postgres 
SELECT
  attname AS name,
  attnum AS OID,
  typ.oid AS typoid,
  typ.typname AS datatype,
  attnotnull AS not_null,
  attr.atthasdef AS has_default_val,
  nspname,
  relname,
  attrelid,
  CASE WHEN typ.typtype = $1 THEN typ.typtypmod ELSE atttypmod END AS typmod,
  CASE WHEN atthasdef THEN (SELECT pg_get_expr(adbin, cls.oid) FROM pg_attrdef WHERE adrelid = cls.oid AND adnum = attr.attnum) ELSE $2 END AS default,
  $3 AS is_updatable, /* Supported only since PG 8.2 */
  -- Add this expression to show if each column is a primary key column. Can't do ANY() on pg_index.indkey which is int2vector
  CASE WHEN EXISTS (SELECT * FROM information_schema.key_column_usage WHERE table_schema = nspname AND table_name = relname AND column_name = attname) THEN $4 ELSE $5 END AS isprimarykey,
  CASE WHEN EXISTS (SELECT * FROM information_schema.table_constraints WHERE table_schema = nspname AND table_name = relname AND constraint_type = $6 AND constraint_name IN (SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_schema = nspname AND table_name = relname AND column_name = attname)) THEN $7 ELSE $8 END AS isunique 
FROM pg_attribute AS attr
JOIN pg_type AS typ ON attr.atttypid = typ.oid
JOIN pg_class AS cls ON cls.oid = attr.attrelid
JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
LEFT OUTER JOIN information_schema.columns AS col ON col.table_schema = nspname AND
 col.table_name = relname AND
 col.column_name = attname
WHERE
 attr.attrelid = $9::oid
    AND attr.attnum > $10
  AND atttypid <> $11 AND
 relkind IN ($12, $13, $14, $15) AND
 NOT attisdropped 
ORDER BY attnum
0 min < 0.1% 2 ms 7 postgres 
CREATE INDEX IF NOT EXISTS brand_pattern_priority_idx ON brand_pattern (priority DESC)
0 min < 0.1% 0 ms 93 postgres 
SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, (wait_event IS NOT NULL) AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*pghero*/
0 min < 0.1% 2 ms 7 postgres 
-- Prevent duplicates across reruns:
-- This assumes (business, campaign, brand, occurred_at, source_ref) is stable enough to identify a touch.
CREATE UNIQUE INDEX IF NOT EXISTS bcte_dedupe_uniq
  ON business_campaign_touch_events (business_fk, campaign_id, brand_id, occurred_at, source_ref)
0 min < 0.1% 3 ms 4 postgres 
SELECT COUNT(*)::bigint AS c
         FROM business_campaign_touches bct
         LEFT JOIN business_payment_rollup bpr ON bpr.business_fk=bct.business_fk
         WHERE bct.last_touch_at < $1
           AND COALESCE(bpr.payment_count, $2)=$3
0 min < 0.1% 2 ms 5 postgres 
SELECT pg_database_size(current_database()) /*pghero*/
0 min < 0.1% 0 ms 93 postgres 
SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $2 AND n.nspname NOT IN ($3, $4) /*pghero*/
0 min < 0.1% 5 ms 2 postgres 
SELECT  nsp.nspname schema_name,
                        cls.relname table_name,
                        att.attname column_name,
                        att.atttypid::regtype::text type_name,
                        att.atthasdef AS has_default,
                        pg_get_expr(def.adbin, def.adrelid) as default
                FROM    pg_catalog.pg_attribute att
                        INNER JOIN pg_catalog.pg_class cls
                            ON att.attrelid = cls.oid
                        INNER JOIN pg_catalog.pg_namespace nsp
                            ON cls.relnamespace = nsp.oid
                        LEFT OUTER JOIN pg_attrdef def
                            ON def.adrelid = att.attrelid
                            AND def.adnum = att.attnum
                WHERE   cls.relkind = ANY($1)
                        AND NOT att.attisdropped
                        AND att.attnum  > $2
                        AND NOT cls.relispartition
                ORDER BY 1, 2, att.attnum
0 min < 0.1% 5 ms 2 postgres 
CREATE UNIQUE INDEX IF NOT EXISTS unmatched_payment_source_bubble_full_uniq
       ON unmatched_payment (source_app, bubble_id)
0 min < 0.1% 2 ms 4 postgres 
SELECT
    *
FROM
    (SELECT
        pg_catalog.format_type(t.oid,$1) AS typname,
        CASE WHEN typelem > $2 THEN typelem ELSE t.oid END as elemoid,
        typlen, typtype, t.oid, nspname,
        (SELECT COUNT($3) FROM pg_catalog.pg_type t2 WHERE t2.typname = t.typname) > $4 AS isdup,
        CASE WHEN t.typcollation != $5 THEN $6 ELSE $7 END AS is_collatable
    FROM
        pg_catalog.pg_type t
    JOIN
        pg_catalog.pg_namespace nsp ON typnamespace=nsp.oid
    WHERE
        (NOT (typname = $8 AND nspname = $9))
    AND
        typisdefined AND typtype IN ($10, $11, $12, $13, $14, $15)
AND NOT EXISTS (SELECT $16 FROM pg_catalog.pg_class WHERE relnamespace=typnamespace
AND relname = typname AND relkind != $17) AND
(typname NOT LIKE $18 OR NOT EXISTS (SELECT $19 FROM pg_catalog.pg_class WHERE
relnamespace=typnamespace AND relname = substring(typname FROM $20)::name
AND relkind != $21))
AND nsp.nspname != $22

    UNION SELECT $23, $24, $25, $26, $27, $28, $29, $30
    UNION SELECT $31, $32, $33, $34, $35, $36, $37, $38
    UNION SELECT $39, $40, $41, $42, $43, $44, $45, $46
    ) AS dummy
ORDER BY nspname <> $47, nspname <> $48, nspname, 1
0 min < 0.1% 1 ms 12 postgres 
SELECT set_config($1,$2,$3) FROM pg_show_all_settings() WHERE name = $4
0 min < 0.1% 0 ms 65 postgres 
INSERT INTO brand(key,name,brand_group_id)
       VALUES ($1,$2,$3)
       ON CONFLICT (key) DO UPDATE SET name=EXCLUDED.name, brand_group_id=EXCLUDED.brand_group_id
0 min < 0.1% 0 ms 70 postgres 
SELECT last_value FROM "public"."staging_business_staging_id_seq" UNION ALL SELECT last_value FROM "public"."validator_log_id_seq" UNION ALL SELECT last_value FROM "public"."business_audit_log_audit_id_seq" UNION ALL SELECT last_value FROM "public"."business_id_seq" UNION ALL SELECT last_value FROM "public"."campaign_id_seq" UNION ALL SELECT last_value FROM "public"."brand_group_id_seq" UNION ALL SELECT last_value FROM "public"."brand_id_seq" UNION ALL SELECT last_value FROM "public"."brand_pattern_id_seq" UNION ALL SELECT last_value FROM "public"."brand_override_id_seq" UNION ALL SELECT last_value FROM "public"."business_campaign_touches_id_seq" UNION ALL SELECT last_value FROM "public"."payment_id_seq" UNION ALL SELECT last_value FROM "public"."unmatched_payment_id_seq" UNION ALL SELECT last_value FROM "public"."business_campaign_touch_events_id_seq" /*pghero*/
0 min < 0.1% 9 ms 1 postgres 
SELECT n.nspname schema_name,
                        p.proname func_name,
                        p.proargnames,
                        COALESCE(proallargtypes::regtype[], proargtypes::regtype[])::text[],
                        p.proargmodes,
                        prorettype::regtype::text return_type,
                        p.prokind is_aggregate,
                        p.prokind is_window,
                        p.proretset is_set_returning,
                        pg_get_expr(proargdefaults, $1) AS arg_defaults
                FROM pg_catalog.pg_proc p
                        INNER JOIN pg_catalog.pg_namespace n
                            ON n.oid = p.pronamespace
                WHERE p.prorettype::regtype != $2::regtype
                ORDER BY 1, 2
0 min < 0.1% 1 ms 7 postgres 
-- Index to support "ORDER BY priority DESC"
DROP INDEX IF EXISTS brand_pattern_priority_idx
0 min < 0.1% 0 ms 93 postgres 
SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*pghero*/
0 min < 0.1% 1 ms 13 postgres 
INSERT INTO brand_pattern(brand_id, regex, priority)
     VALUES ($1,$2,$3)
     ON CONFLICT DO NOTHING
0 min < 0.1% 0 ms 18 postgres 
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,$1) AS datatype,
att.attnotnull as not_null, att.atthasdef as has_default_val, des.description, seq.seqtypid
FROM pg_catalog.pg_attribute att
    JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
    JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
    JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
    JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
    LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
    LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
    LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid=$2::regclass AND objid=cs.oid AND cs.relkind=$3) ON refobjid=att.attrelid AND refobjsubid=att.attnum
    LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
    LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
    LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid=$4::regclass)
    LEFT OUTER JOIN pg_catalog.pg_sequence seq ON cs.oid=seq.seqrelid
WHERE

    att.attrelid = $5::oid
    AND att.attnum > $6
    AND att.attisdropped IS FALSE
ORDER BY att.attnum
0 min < 0.1% 1 ms 14 postgres 
CREATE INDEX IF NOT EXISTS bct_campaign_lasttouch_idx
  ON business_campaign_touches (campaign_id, last_touch_at)
0 min < 0.1% 2 ms 5 postgres 
SELECT  rel.oid,
        (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = $1) AS triggercount,
        (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = $2 AND tgenabled = $3) AS has_enable_triggers,
        (CASE WHEN rel.relkind = $4 THEN $5 ELSE $6 END) AS is_partitioned,
        nsp.nspname AS schema,
        nsp.oid AS schemaoid,
        rel.relname AS name,
        CASE
    WHEN nsp.nspname LIKE $7 ESCAPE $8
        OR nsp.nspname = $9
        THEN $10
    ELSE $11 END as is_system
FROM    pg_class rel
INNER JOIN pg_namespace nsp ON rel.relnamespace= nsp.oid
    WHERE rel.relkind IN ($12,$13,$14,$15)
        AND NOT rel.relispartition
    ORDER BY nsp.nspname, rel.relname
0 min < 0.1% 2 ms 4 postgres 
SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ORDER BY 1, 2 /*pghero*/
0 min < 0.1% 0 ms 52 postgres 
INSERT INTO brand_override(domain, brand_id)
       VALUES ($1,$2)
       ON CONFLICT (domain) DO UPDATE SET brand_id=EXCLUDED.brand_id
0 min < 0.1% 3 ms 2 postgres 
SELECT
    pr.oid,
    pg_catalog.pg_get_function_identity_arguments(pr.oid) AS proargs,
    pr.proname AS name,
    nsp.nspname AS nspname,
    $1 AS object_type,
    $2 AS icon
FROM
    pg_catalog.pg_proc pr
JOIN pg_catalog.pg_namespace nsp ON nsp.oid=pr.pronamespace
JOIN pg_catalog.pg_type typ ON typ.oid=prorettype
JOIN pg_catalog.pg_namespace typns ON typns.oid=typ.typnamespace
JOIN pg_catalog.pg_language lng ON lng.oid=prolang
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=pr.oid AND des.classoid=$3::regclass)
WHERE
    pronamespace = $4::oid
    AND typname NOT IN ($5, $6)
    AND pr.prokind = $7
ORDER BY
    proname
0 min < 0.1% 0 ms 14 postgres 
CREATE TABLE IF NOT EXISTS brand (
  id               BIGSERIAL PRIMARY KEY,
  key              TEXT UNIQUE NOT NULL,     -- e.g., 'domainnetworks', 'geo'
  name             TEXT NOT NULL,
  brand_group_id   BIGINT REFERENCES brand_group(id),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 2 ms 4 postgres 
SELECT COUNT(*)::bigint AS c FROM business_campaign_touches
0 min < 0.1% 1 ms 9 postgres 
SELECT set_config($1,$2,$3) FROM pg_settings WHERE name = $4
0 min < 0.1% 0 ms 13 postgres 
INSERT INTO brand_group(key, description)
     VALUES ($1,$2)
     ON CONFLICT (key) DO UPDATE SET description=EXCLUDED.description
0 min < 0.1% 3 ms 2 postgres 
SELECT COUNT(*)::bigint AS c FROM business_campaign_touch_events
0 min < 0.1% 1 ms 7 postgres 
SELECT name, vartype, min_val, max_val, enumvals
FROM pg_catalog.pg_show_all_settings() WHERE context in ($1, $2)
0 min < 0.1% 2 ms 3 postgres 
-- ============================================================
-- 2) TOUCH ROLLUP (operational "last touch" queries)
-- ============================================================

CREATE TABLE IF NOT EXISTS business_campaign_touches (
  id                 BIGSERIAL PRIMARY KEY,
  business_fk        BIGINT NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  campaign_id        BIGINT NOT NULL REFERENCES campaign(id) ON DELETE CASCADE,
  brand_id           BIGINT NOT NULL REFERENCES brand(id) ON DELETE CASCADE,

  touch_count        INTEGER NOT NULL DEFAULT 0,
  first_touch_at     TIMESTAMPTZ,
  last_touch_at      TIMESTAMPTZ,

  last_touch_type    TEXT,
  last_touch_source  TEXT,
  metadata           JSONB,

  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT business_campaign_touches_uniq UNIQUE (business_fk, campaign_id, brand_id)
)
0 min < 0.1% 1 ms 10 postgres 
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,$1) AS datatype,
att.attnotnull as not_null,
CASE WHEN att.atthasdef OR att.attidentity != $2 OR ty.typdefault IS NOT NULL THEN $3
ELSE $4 END as has_default_val, des.description, seq.seqtypid
FROM pg_catalog.pg_attribute att
    JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
    JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
    JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
    JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
    LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
    LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
    LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid=$5::regclass AND objid=cs.oid AND cs.relkind=$6) ON refobjid=att.attrelid AND refobjsubid=att.attnum
    LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
    LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
    LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid=$7::regclass)
    LEFT OUTER JOIN pg_catalog.pg_sequence seq ON cs.oid=seq.seqrelid
WHERE

    att.attrelid = $8::oid
    AND att.attnum > $9
    AND att.attisdropped IS FALSE
ORDER BY att.attnum
0 min < 0.1% 1 ms 4 postgres 
SELECT COUNT(*)::bigint AS c FROM business
0 min < 0.1% 0 ms 11 postgres 
CREATE INDEX IF NOT EXISTS bct_business_id_text_idx
  ON business_campaign_touches (business_id_text)
0 min < 0.1% 0 ms 14 postgres 
-- ============================================================
-- 3) PAYMENTS (normalized_payments ingestion)
-- ============================================================

CREATE TABLE IF NOT EXISTS payment (
  id                BIGSERIAL PRIMARY KEY,

  -- linkage to business (nullable during migration)
  business_fk       BIGINT REFERENCES business(id) ON DELETE SET NULL,

  -- parsed/normalized identifiers
  business_id_text  TEXT,                 -- e.g., 'AADT146' or 'AAAA108' (what you parsed)
  campaign_code     TEXT,                 -- e.g., 'AAB21'
  pincode           TEXT,                 -- e.g., 'AAB21-AADT146' or 'DFS53496' or 'GA18984'
  brand_domain      TEXT,                 -- e.g., 'KRINERSINSURANCE.COM' or 'domainnetworks.com' (raw)
  brand_id          BIGINT REFERENCES brand(id),

  amount_cents      BIGINT,               -- your amount looks like integer dollars; keep name for uniformity
  paid_at           TIMESTAMPTZ,
  created_at        TIMESTAMPTZ,
  modified_at       TIMESTAMPTZ,

  refunded          BOOLEAN NOT NULL DEFAULT false,
  payment_type      TEXT,

  -- provenance
  source_app        TEXT NOT NULL,        -- dn/msd/geo/lc
  bubble_id         TEXT,                 -- Bubble thing id from source
  source_ref        TEXT,                 -- optional: could be bubble_id or mongo _id
  matched_business  BOOLEAN NOT NULL DEFAULT false,

  raw_data          JSONB,                -- raw_data blob
  normalization_meta JSONB,               -- normalization meta blob

  inserted_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 0 ms 14 postgres 
-- Useful uniqueness (best effort): (source_app, bubble_id) is often stable
CREATE UNIQUE INDEX IF NOT EXISTS payment_source_bubble_uniq
  ON payment (source_app, bubble_id)
  WHERE bubble_id IS NOT NULL
0 min < 0.1% 1 ms 7 postgres 
CREATE INDEX IF NOT EXISTS brand_pattern_priority_idx ON brand_pattern (priority)
0 min < 0.1% 0 ms 14 postgres 
CREATE TABLE IF NOT EXISTS brand_override (
  id               BIGSERIAL PRIMARY KEY,
  domain           TEXT UNIQUE NOT NULL,     -- store lowercase normalized domain
  brand_id         BIGINT NOT NULL REFERENCES brand(id) ON DELETE CASCADE,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 0 ms 13 postgres 
CREATE INDEX IF NOT EXISTS cmd_campaign_day_idx ON campaign_metrics_daily (campaign_id, day)
0 min < 0.1% 0 ms 14 postgres 
CREATE TABLE IF NOT EXISTS brand_group (
  id               BIGSERIAL PRIMARY KEY,
  key              TEXT UNIQUE NOT NULL,     -- e.g., 'geo'
  description      TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 0 ms 24 postgres 
/*pga4dash*/
SELECT
    pid,
    datname,
    usename,
    application_name,
    client_addr,
    pg_catalog.to_char(backend_start, $1) AS backend_start,
    state,
    wait_event_type || $2 || wait_event AS wait_event,
    array_to_string(pg_catalog.pg_blocking_pids(pid), $3) AS blocking_pids,
    query,
    pg_catalog.to_char(state_change, $4) AS state_change,
    pg_catalog.to_char(query_start, $5) AS query_start,
    pg_catalog.to_char(xact_start, $6) AS xact_start,
    backend_type,
    CASE WHEN state = $7 THEN ROUND((extract($8 from now() - query_start) / $9)::numeric, $10) ELSE $11 END AS active_since
FROM
    pg_catalog.pg_stat_activity
WHERE
    datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $12)ORDER BY pid
0 min < 0.1% 1 ms 4 postgres 
SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) ORDER BY 1, 2, 3 /*pghero*/
0 min < 0.1% 0 ms 14 postgres 
CREATE TABLE IF NOT EXISTS campaign (
  id               BIGSERIAL PRIMARY KEY,
  code             TEXT UNIQUE NOT NULL,     -- e.g., 'AAA01'
  name             TEXT NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 0 ms 14 postgres 
CREATE TABLE IF NOT EXISTS brand_pattern (
  id               BIGSERIAL PRIMARY KEY,
  brand_id         BIGINT NOT NULL REFERENCES brand(id) ON DELETE CASCADE,
  regex            TEXT NOT NULL,
  priority         INTEGER NOT NULL DEFAULT 100,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
)
0 min < 0.1% 2 ms 2 postgres 
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "average_time" DESC LIMIT $6 /*pghero*/
0 min < 0.1% 1 ms 3 postgres 
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "calls" DESC LIMIT $6 /*pghero*/
0 min < 0.1% 1 ms 3 postgres 
SELECT --nspname, collname,
    CASE WHEN length(nspname::text) > $1 AND length(collname::text) > $2  THEN
        pg_catalog.concat(pg_catalog.quote_ident(nspname), $3, pg_catalog.quote_ident(collname))
    ELSE $4 END AS copy_collation
FROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n
WHERE c.collnamespace=n.oid
ORDER BY nspname, collname
0 min < 0.1% 0 ms 8 postgres 
SELECT rel.oid, rel.relname AS name,
    (SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = $1) AS triggercount,
    (SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = $2 AND tgenabled = $3) AS has_enable_triggers,
    (CASE WHEN rel.relkind = $4 THEN $5 ELSE $6 END) AS is_partitioned,
    (SELECT count($7) FROM pg_catalog.pg_inherits WHERE inhrelid=rel.oid LIMIT $8) as is_inherits,
    (SELECT count($9) FROM pg_catalog.pg_inherits WHERE inhparent=rel.oid LIMIT $10) as is_inherited,
    des.description
FROM pg_catalog.pg_class rel
    LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=$11 AND des.classoid=$12::regclass)
    WHERE rel.relkind IN ($13,$14,$15,$16) AND rel.relnamespace = $17::oid
    AND NOT rel.relispartition
        ORDER BY rel.relname
0 min < 0.1% 0 ms 15 postgres 
SELECT bo.domain, b.key AS brand_key
     FROM brand_override bo
     JOIN brand b ON b.id=bo.brand_id
0 min < 0.1% 0 ms 13 postgres 
-- ============================================================
-- 5) DAILY METRICS (trends/comparisons)
-- ============================================================

CREATE TABLE IF NOT EXISTS campaign_metrics_daily (
  day           DATE NOT NULL,
  campaign_id   BIGINT NOT NULL REFERENCES campaign(id) ON DELETE CASCADE,
  brand_id      BIGINT REFERENCES brand(id),
  touches       BIGINT NOT NULL DEFAULT 0,
  paid_count    BIGINT NOT NULL DEFAULT 0,
  paid_cents    BIGINT NOT NULL DEFAULT 0,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (day, campaign_id, brand_id)
)
0 min < 0.1% 2 ms 2 postgres 
SELECT * FROM public.id_lookup
ORDER BY public_id ASC LIMIT $1