Queries

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 /*application='PgHero'*/

SELECT * FROM app_accrue_interest_for_period($1)

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 /*application='PgHero'*/

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)
 /*application='PgHero'*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_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 > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "total_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

INSERT INTO ledger_entries (
            id, account_id, entry_type, amount, balance_after, held_after,
            currency, idempotency_key, reference_type, reference_id,
            description, created_at, updated_at
        )
        SELECT
            v_entry_id,
            r.account_id,
            $23,
            v_amount,
            a.balance,
            a.held_balance,
            r.currency,
            format($24, r.account_id, p_period),
            $25,
            v_run_id,
            format($26, p_period),
            now(),
            now()
        FROM accounts a WHERE a.id = r.account_id

SELECT app_begin_balance_freeze(
        $9,
        v_locked_by,
        format($10, p_period),
        interval $11,
        $12
    )

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)
 /*application='PgHero'*/

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 /*application='PgHero'*/

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)
 /*application='PgHero'*/

INSERT INTO interest_accruals (
            id, run_id, account_id, period_date, principal, annual_rate,
            amount, currency, ledger_entry_id, created_at, updated_at
        ) VALUES (
            gen_random_uuid(), v_run_id, r.account_id, p_period, r.principal,
            r.annual_rate, v_amount, r.currency, v_entry_id, now(), now()
        )

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, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68)
 /*application='PgHero'*/

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 /*application='PgHero'*/

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 /*application='PgHero'*/

UPDATE accounts
        SET balance = balance + v_amount,
            version = version + $20,
            updated_at = now()
        WHERE id = r.account_id

UPDATE auth_sessions SET revoked_at=$1::TIMESTAMP WITH TIME ZONE, revoke_reason=$2::VARCHAR, updated_at=now() WHERE auth_sessions.revoked_at IS NULL AND auth_sessions.expires_at < $3::TIMESTAMP WITH TIME ZONE

INSERT INTO interest_accrual_runs (id, period_date, status, started_at, created_at, updated_at)
    VALUES (gen_random_uuid(), p_period, $2, now(), now(), now())
    ON CONFLICT (period_date) DO UPDATE
        SET status = $3,
            started_at = now(),
            error_message = $4,
            updated_at = now()
    RETURNING id

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, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64)
 /*application='PgHero'*/

SELECT a.id AS account_id,
               a.balance AS principal,
               a.currency,
               coalesce(sp.annual_rate, $1) AS annual_rate
        FROM accounts a
        LEFT JOIN savings_programs sp ON sp.id = a.program_id
        WHERE a.status = $2
          AND a.account_type IN ($3, $4, $5)
          AND a.balance > $6
        FOR UPDATE OF a

INSERT INTO maintenance_locks AS ml
        (name, locked_by, reason, blocks_balance_mutations, locked_at, expires_at)
    VALUES
        (p_name, p_locked_by, p_reason, p_blocks, now(), now() + p_ttl)
    ON CONFLICT (name) DO UPDATE
    SET locked_by = EXCLUDED.locked_by,
        reason = EXCLUDED.reason,
        blocks_balance_mutations = EXCLUDED.blocks_balance_mutations,
        locked_at = now(),
        expires_at = EXCLUDED.expires_at
    WHERE ml.expires_at < now()

INSERT INTO auth_sessions (user_id, device_id, refresh_token_hash, fingerprint_hash, fingerprint_payload, user_agent, ip_address, ip_prefix, locale, last_seen_at, expires_at, is_ephemeral, revoked_at, revoke_reason, id) VALUES ($1::UUID, $2::UUID, $3::VARCHAR, $4::VARCHAR, $5::JSONB, $6::VARCHAR, $7, $8::VARCHAR, $9::VARCHAR, $10::TIMESTAMP WITH TIME ZONE, $11::TIMESTAMP WITH TIME ZONE, $12, $13::TIMESTAMP WITH TIME ZONE, $14::VARCHAR, $15::UUID) RETURNING auth_sessions.created_at, auth_sessions.updated_at

CREATE TABLE support_ticket_messages (
	id UUID NOT NULL, 
	ticket_id UUID NOT NULL, 
	author_id UUID, 
	author_kind support_message_author NOT NULL, 
	body TEXT DEFAULT '' NOT NULL, 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(author_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(ticket_id) REFERENCES support_tickets (id) ON DELETE CASCADE
)

SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*application='PgHero'*/

SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AND (c.relpersistence <> $2 OR NOT pg_is_in_recovery()) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $3 AND n.nspname NOT IN ($4, $5) /*application='PgHero'*/

CREATE INDEX ix_support_ticket_attachments_message_id ON support_ticket_attachments (message_id)

INSERT INTO user_devices (user_id, fingerprint_hash, display_name, is_trusted, trusted_at, last_ip, last_seen_at, fingerprint_payload, id) VALUES ($1::UUID, $2::VARCHAR, $3::VARCHAR, $4, $5::TIMESTAMP WITH TIME ZONE, $6, $7::TIMESTAMP WITH TIME ZONE, $8::JSONB, $9::UUID) RETURNING user_devices.created_at, user_devices.updated_at

CREATE TABLE support_ticket_attachments (
	id UUID NOT NULL, 
	message_id UUID NOT NULL, 
	storage_path TEXT NOT NULL, 
	original_filename VARCHAR(512), 
	content_type VARCHAR(128), 
	size_bytes INTEGER DEFAULT '0' NOT NULL, 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(message_id) REFERENCES support_ticket_messages (id) ON DELETE CASCADE
)

SET SESSION timezone TO 'UTC'

SELECT pg_database_size(current_database()) /*application='PgHero'*/

SELECT app_end_balance_freeze($9, v_locked_by)

SELECT schemaname AS schema, relname AS table, indexrelname AS index, pg_relation_size(i.indexrelid) AS size_bytes, idx_scan as index_scans FROM pg_stat_user_indexes ui INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <= $1 AND pg_relation_size(i.indexrelid) >= $2 ORDER BY pg_relation_size(i.indexrelid) DESC, relname ASC /*application='PgHero'*/

UPDATE accounts SET balance=$1, version=$2::BIGINT, updated_at=now() WHERE accounts.id = $3::UUID

SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*application='PgHero'*/

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,$15,$16) ORDER BY 1, 2 /*application='PgHero'*/

CREATE INDEX ix_support_ticket_messages_ticket_id ON support_ticket_messages (ticket_id)

ALTER TABLE auth_sessions ADD COLUMN is_ephemeral BOOLEAN DEFAULT 'false' NOT NULL

SELECT users.email, users.password_hash, users.full_name, users.role, users.status, users.locale, users.phone, users.birth_date, users.address, users.avatar_path, users.avatar_content_type, users.avatar_updated_at, users.last_login_at, users.email_verified_at, users.cloud_password_hash, users.cloud_password_hint, users.kyc_status, users.kyc_submitted_at, users.kyc_reviewed_at, users.kyc_reviewed_by_id, users.kyc_reject_reason, users.kyc_client_message, users.referral_code, users.referred_by_id, users.id, users.created_at, users.updated_at 
FROM users 
WHERE users.id = $1::UUID

INSERT INTO accounts (user_id, program_id, account_type, status, id) VALUES ($1::UUID, $2::UUID, $3, $4, $5::UUID), ($6::UUID, $7::UUID, $8, $9, $10::UUID), ($11::UUID, $12::UUID, $13, $14, $15::UUID), ($16::UUID, $17::UUID, $18, $19, $20::UUID) RETURNING accounts.currency, accounts.balance, accounts.held_balance, accounts.version, accounts.created_at, accounts.updated_at, accounts.id

INSERT INTO auth_sessions (user_id, device_id, refresh_token_hash, fingerprint_hash, fingerprint_payload, user_agent, ip_address, ip_prefix, locale, last_seen_at, expires_at, revoked_at, revoke_reason, id) VALUES ($1::UUID, $2::UUID, $3::VARCHAR, $4::VARCHAR, $5::JSONB, $6::VARCHAR, $7, $8::VARCHAR, $9::VARCHAR, $10::TIMESTAMP WITH TIME ZONE, $11::TIMESTAMP WITH TIME ZONE, $12::TIMESTAMP WITH TIME ZONE, $13::VARCHAR, $14::UUID) RETURNING auth_sessions.created_at, auth_sessions.updated_at

EXISTS (
            SELECT $20 FROM interest_accruals ia
            WHERE ia.account_id = r.account_id AND ia.period_date = p_period
        )

SELECT users.email, users.password_hash, users.full_name, users.role, users.status, users.locale, users.phone, users.birth_date, users.address, users.avatar_path, users.avatar_content_type, users.avatar_updated_at, users.last_login_at, users.email_verified_at, users.cloud_password_hash, users.cloud_password_hint, users.kyc_status, users.kyc_submitted_at, users.kyc_reviewed_at, users.kyc_reviewed_by_id, users.kyc_reject_reason, users.kyc_client_message, users.referral_code, users.referred_by_id, users.id, users.created_at, users.updated_at 
FROM users 
WHERE users.email = $1::VARCHAR

INSERT INTO ledger_entries (account_id, entry_type, amount, balance_after, held_after, currency, idempotency_key, reference_type, reference_id, description, created_by_user_id, meta, id) VALUES ($1::UUID, $2, $3, $4, $5, $6::VARCHAR, $7::VARCHAR, $8::VARCHAR, $9::UUID, $10::VARCHAR, $11::UUID, $12::JSONB, $13::UUID) RETURNING ledger_entries.created_at, ledger_entries.updated_at

SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas($1)) AND c.relname = $2 AND c.relkind IN ($3,$4) /*application='PgHero'*/

SELECT auth_sessions.user_id AS auth_sessions_user_id, auth_sessions.device_id AS auth_sessions_device_id, auth_sessions.refresh_token_hash AS auth_sessions_refresh_token_hash, auth_sessions.fingerprint_hash AS auth_sessions_fingerprint_hash, auth_sessions.fingerprint_payload AS auth_sessions_fingerprint_payload, auth_sessions.user_agent AS auth_sessions_user_agent, auth_sessions.ip_address AS auth_sessions_ip_address, auth_sessions.ip_prefix AS auth_sessions_ip_prefix, auth_sessions.locale AS auth_sessions_locale, auth_sessions.last_seen_at AS auth_sessions_last_seen_at, auth_sessions.expires_at AS auth_sessions_expires_at, auth_sessions.is_ephemeral AS auth_sessions_is_ephemeral, auth_sessions.revoked_at AS auth_sessions_revoked_at, auth_sessions.revoke_reason AS auth_sessions_revoke_reason, auth_sessions.id AS auth_sessions_id, auth_sessions.created_at AS auth_sessions_created_at, auth_sessions.updated_at AS auth_sessions_updated_at 
FROM auth_sessions 
WHERE auth_sessions.id = $1::UUID

UPDATE users SET last_login_at=$1::TIMESTAMP WITH TIME ZONE, updated_at=now() WHERE users.id = $2::UUID

SELECT id                   FROM interest_accrual_runs
    WHERE period_date = p_period AND status = $2

SELECT slot_name, database, active FROM pg_replication_slots /*application='PgHero'*/

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,$14,$15) ORDER BY 1, 2, 3 /*application='PgHero'*/

BEGIN ISOLATION LEVEL READ COMMITTED

CREATE TABLE offers (
	id UUID NOT NULL, 
	number VARCHAR(64) NOT NULL, 
	company VARCHAR(255) NOT NULL, 
	kind VARCHAR(255) NOT NULL, 
	photo_url TEXT NOT NULL, 
	raised NUMERIC(20, 8) DEFAULT '0' NOT NULL, 
	goal NUMERIC(20, 8) NOT NULL, 
	annual_rate_pct NUMERIC(8, 4) NOT NULL, 
	term_days INTEGER NOT NULL, 
	closes_at DATE NOT NULL, 
	rating INTEGER DEFAULT '4' NOT NULL, 
	lot_price NUMERIC(20, 8) NOT NULL, 
	currency VARCHAR(16) DEFAULT 'USDT' NOT NULL, 
	is_active BOOLEAN DEFAULT 'true' NOT NULL, 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT ck_offers_raised_non_negative CHECK (raised >= 0), 
	CONSTRAINT ck_offers_goal_positive CHECK (goal > 0), 
	CONSTRAINT ck_offers_lot_price_positive CHECK (lot_price > 0), 
	CONSTRAINT ck_offers_term_days_positive CHECK (term_days > 0), 
	CONSTRAINT ck_offers_rating_range CHECK (rating >= 1 AND rating <= 5)
)

SELECT $2 FROM ONLY "public"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

SELECT user_devices.user_id, user_devices.fingerprint_hash, user_devices.display_name, user_devices.is_trusted, user_devices.trusted_at, user_devices.last_ip, user_devices.last_seen_at, user_devices.fingerprint_payload, user_devices.id, user_devices.created_at, user_devices.updated_at 
FROM user_devices 
WHERE user_devices.user_id = $1::UUID AND user_devices.fingerprint_hash = $2::VARCHAR

CREATE TABLE program_placements (
	id UUID NOT NULL, 
	user_id UUID NOT NULL, 
	account_id UUID NOT NULL, 
	transfer_id UUID, 
	amount NUMERIC(20, 8) NOT NULL, 
	remaining_amount NUMERIC(20, 8) NOT NULL, 
	currency VARCHAR(16) DEFAULT 'USDT' NOT NULL, 
	term_months INTEGER NOT NULL, 
	annual_rate NUMERIC(8, 6) NOT NULL, 
	matures_at TIMESTAMP WITH TIME ZONE NOT NULL, 
	status placement_status DEFAULT 'active' NOT NULL, 
	closed_at TIMESTAMP WITH TIME ZONE, 
	idempotency_key VARCHAR(128), 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT ck_placements_amount_positive CHECK (amount > 0), 
	CONSTRAINT ck_placements_term_months_positive CHECK (term_months >= 1), 
	FOREIGN KEY(account_id) REFERENCES accounts (id) ON DELETE RESTRICT, 
	FOREIGN KEY(transfer_id) REFERENCES transfers (id) ON DELETE SET NULL, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE RESTRICT, 
	CONSTRAINT uq_placements_idempotency UNIQUE (idempotency_key)
)

SET client_min_messages TO 'warning' /*application='PgHero'*/

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,$15,$16,$17,$18,$19,$20) ORDER BY 1, 2 /*application='PgHero'*/

CREATE TABLE offer_participations (
	id UUID NOT NULL, 
	user_id UUID NOT NULL, 
	offer_id UUID NOT NULL, 
	account_id UUID NOT NULL, 
	lots INTEGER NOT NULL, 
	lot_price NUMERIC(20, 8) NOT NULL, 
	amount NUMERIC(20, 8) NOT NULL, 
	payout_at_maturity NUMERIC(20, 8) NOT NULL, 
	currency VARCHAR(16) DEFAULT 'USDT' NOT NULL, 
	term_days INTEGER NOT NULL, 
	matures_at TIMESTAMP WITH TIME ZONE NOT NULL, 
	status offer_participation_status DEFAULT 'active' NOT NULL, 
	claimed_at TIMESTAMP WITH TIME ZONE, 
	idempotency_key VARCHAR(128), 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT ck_offer_part_lots_positive CHECK (lots >= 1), 
	CONSTRAINT ck_offer_part_amount_positive CHECK (amount > 0), 
	FOREIGN KEY(account_id) REFERENCES accounts (id) ON DELETE RESTRICT, 
	FOREIGN KEY(offer_id) REFERENCES offers (id) ON DELETE RESTRICT, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE RESTRICT, 
	CONSTRAINT uq_offer_part_idempotency UNIQUE (idempotency_key)
)

SELECT $2 FROM ONLY "public"."ledger_entries" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

CREATE TABLE referral_rewards (
	id UUID NOT NULL, 
	referrer_id UUID NOT NULL, 
	invitee_id UUID NOT NULL, 
	base_amount NUMERIC(20, 8) NOT NULL, 
	reward_amount NUMERIC(20, 8) NOT NULL, 
	currency VARCHAR(16) DEFAULT 'USDT' NOT NULL, 
	source_type VARCHAR(64) NOT NULL, 
	source_id UUID NOT NULL, 
	status referral_reward_status DEFAULT 'accrued' NOT NULL, 
	idempotency_key VARCHAR(128), 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT ck_referral_base_positive CHECK (base_amount > 0), 
	CONSTRAINT ck_referral_reward_positive CHECK (reward_amount > 0), 
	FOREIGN KEY(invitee_id) REFERENCES users (id) ON DELETE RESTRICT, 
	FOREIGN KEY(referrer_id) REFERENCES users (id) ON DELETE RESTRICT, 
	CONSTRAINT uq_referral_reward_idempotency UNIQUE (idempotency_key)
)

SELECT set_config($1, $2, $3)

INSERT INTO users (email, password_hash, full_name, role, status, phone, birth_date, address, avatar_path, avatar_content_type, avatar_updated_at, last_login_at, email_verified_at, cloud_password_hash, cloud_password_hint, kyc_status, kyc_submitted_at, kyc_reviewed_at, kyc_reviewed_by_id, kyc_reject_reason, kyc_client_message, referral_code, referred_by_id, id) VALUES ($1::VARCHAR, $2::VARCHAR, $3::VARCHAR, $4, $5, $6::VARCHAR, $7::DATE, $8::VARCHAR, $9::VARCHAR, $10::VARCHAR, $11::TIMESTAMP WITH TIME ZONE, $12::TIMESTAMP WITH TIME ZONE, $13::TIMESTAMP WITH TIME ZONE, $14::VARCHAR, $15::VARCHAR, $16, $17::TIMESTAMP WITH TIME ZONE, $18::TIMESTAMP WITH TIME ZONE, $19::UUID, $20::VARCHAR, $21::VARCHAR, $22::VARCHAR, $23::UUID, $24::UUID) RETURNING users.locale, users.created_at, users.updated_at

UPDATE auth_sessions SET ip_address=$1, last_seen_at=$2::TIMESTAMP WITH TIME ZONE, updated_at=now() WHERE auth_sessions.id = $3::UUID

SELECT kyc_documents.user_id, kyc_documents.doc_type, kyc_documents.storage_path, kyc_documents.original_filename, kyc_documents.content_type, kyc_documents.status, kyc_documents.reject_reason, kyc_documents.id, kyc_documents.created_at, kyc_documents.updated_at 
FROM kyc_documents 
WHERE kyc_documents.user_id = $1::UUID ORDER BY kyc_documents.created_at ASC

INSERT INTO support_ticket_messages (ticket_id, author_id, author_kind, body, id) VALUES ($1::UUID, $2::UUID, $3, $4::VARCHAR, $5::UUID) RETURNING support_ticket_messages.created_at, support_ticket_messages.updated_at

CREATE TYPE support_message_author AS ENUM ('client', 'staff')

SELECT pg_advisory_xact_lock($1)

SELECT accounts.user_id, accounts.program_id, accounts.account_type, accounts.currency, accounts.balance, accounts.held_balance, accounts.status, accounts.version, accounts.id, accounts.created_at, accounts.updated_at 
FROM accounts 
WHERE accounts.user_id = $1::UUID ORDER BY accounts.account_type

SELECT payment_providers.code, payment_providers.name, payment_providers.is_active, payment_providers.config, payment_providers.id, payment_providers.created_at, payment_providers.updated_at 
FROM payment_providers 
WHERE payment_providers.code = $1::VARCHAR

INSERT INTO email_otp_challenges (user_id, purpose, code_hash, expires_at, used_at, sent_to, id) VALUES ($1::UUID, $2, $3::VARCHAR, $4::TIMESTAMP WITH TIME ZONE, $5::TIMESTAMP WITH TIME ZONE, $6::VARCHAR, $7::UUID) RETURNING email_otp_challenges.attempt_count, email_otp_challenges.created_at, email_otp_challenges.updated_at

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, (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 > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "average_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

CREATE INDEX ix_users_referred_by_id ON users (referred_by_id)

INSERT INTO deposits (user_id, account_id, provider_id, external_id, amount, currency, status, tx_hash, raw_callback, confirmed_at, ledger_entry_id, id) VALUES ($1::UUID, $2::UUID, $3::UUID, $4::VARCHAR, $5, $6::VARCHAR, $7, $8::VARCHAR, $9::JSONB, $10::TIMESTAMP WITH TIME ZONE, $11::UUID, $12::UUID) RETURNING deposits.created_at, deposits.updated_at

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,$14,$15,$16,$17,$18,$19) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT users.email, users.password_hash, users.full_name, users.role, users.status, users.locale, users.phone, users.last_login_at, users.email_verified_at, users.cloud_password_hash, users.cloud_password_hint, users.kyc_status, users.kyc_submitted_at, users.kyc_reviewed_at, users.kyc_reviewed_by_id, users.kyc_reject_reason, users.kyc_client_message, users.id, users.created_at, users.updated_at 
FROM users 
WHERE users.email = $1::VARCHAR

SELECT $2 FROM ONLY "public"."user_devices" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_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 > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "calls" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

SELECT $2 FROM ONLY "public"."interest_accrual_runs" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

ALTER TABLE users ADD CONSTRAINT fk_users_referred_by_id FOREIGN KEY(referred_by_id) REFERENCES users (id) ON DELETE SET NULL

CREATE UNIQUE INDEX ix_users_referral_code ON users (referral_code)

UPDATE interest_accrual_runs
    SET status = $11,
        finished_at = now(),
        accounts_processed = v_count,
        total_accrued = v_total,
        updated_at = now()
    WHERE id = v_run_id

SELECT $2 FROM ONLY "public"."users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

SELECT schemaname AS schema, relname AS table, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_dead_tup AS dead_rows, n_live_tup AS live_rows FROM pg_stat_user_tables ORDER BY 1, 2 /*application='PgHero'*/

NOT EXISTS (
        SELECT $3 FROM maintenance_locks
        WHERE name = p_name
          AND locked_by = p_locked_by
          AND expires_at > now()
    )

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,$15) ORDER BY 1, 2 /*application='PgHero'*/

SELECT auth_sessions.user_id, auth_sessions.device_id, auth_sessions.refresh_token_hash, auth_sessions.fingerprint_hash, auth_sessions.fingerprint_payload, auth_sessions.user_agent, auth_sessions.ip_address, auth_sessions.ip_prefix, auth_sessions.locale, auth_sessions.last_seen_at, auth_sessions.expires_at, auth_sessions.is_ephemeral, auth_sessions.revoked_at, auth_sessions.revoke_reason, auth_sessions.id, auth_sessions.created_at, auth_sessions.updated_at 
FROM auth_sessions 
WHERE auth_sessions.refresh_token_hash = $1::VARCHAR

SELECT users.email, users.password_hash, users.full_name, users.role, users.status, users.locale, users.phone, users.birth_date, users.address, users.avatar_path, users.avatar_content_type, users.avatar_updated_at, users.last_login_at, users.email_verified_at, users.cloud_password_hash, users.cloud_password_hint, users.kyc_status, users.kyc_submitted_at, users.kyc_reviewed_at, users.kyc_reviewed_by_id, users.kyc_reject_reason, users.kyc_client_message, users.referral_code, users.referred_by_id, users.id, users.created_at, users.updated_at 
FROM users 
WHERE users.role = $1 ORDER BY users.created_at DESC 
 LIMIT $2::INTEGER

SELECT deposits.user_id, deposits.account_id, deposits.provider_id, deposits.external_id, deposits.amount, deposits.currency, deposits.status, deposits.tx_hash, deposits.raw_callback, deposits.confirmed_at, deposits.ledger_entry_id, deposits.id, deposits.created_at, deposits.updated_at 
FROM deposits 
WHERE deposits.external_id = $1::VARCHAR

SELECT accounts.user_id, accounts.program_id, accounts.account_type, accounts.currency, accounts.balance, accounts.held_balance, accounts.status, accounts.version, accounts.id, accounts.created_at, accounts.updated_at 
FROM accounts 
WHERE accounts.user_id = $1::UUID AND accounts.account_type = $2

SELECT program_placements.user_id, program_placements.account_id, program_placements.transfer_id, program_placements.amount, program_placements.remaining_amount, program_placements.currency, program_placements.term_months, program_placements.annual_rate, program_placements.matures_at, program_placements.status, program_placements.closed_at, program_placements.idempotency_key, program_placements.id, program_placements.created_at, program_placements.updated_at 
FROM program_placements 
WHERE program_placements.account_id = $1::UUID AND program_placements.status = $2 AND program_placements.matures_at > $3::TIMESTAMP WITH TIME ZONE AND program_placements.remaining_amount > $4::INTEGER

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) ORDER BY 1, 2 /*application='PgHero'*/

CREATE TYPE offer_participation_status AS ENUM ('active', 'claimed', 'cancelled')

INSERT INTO program_placements (user_id, account_id, transfer_id, amount, remaining_amount, currency, term_months, annual_rate, matures_at, status, closed_at, idempotency_key, id) VALUES ($1::UUID, $2::UUID, $3::UUID, $4, $5, $6::VARCHAR, $7::INTEGER, $8, $9::TIMESTAMP WITH TIME ZONE, $10, $11::TIMESTAMP WITH TIME ZONE, $12::VARCHAR, $13::UUID) RETURNING program_placements.created_at, program_placements.updated_at

SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = $1::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[$2::VARCHAR, $3::VARCHAR, $4::VARCHAR, $5::VARCHAR, $6::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $7::VARCHAR

SELECT user_devices.user_id, user_devices.fingerprint_hash, user_devices.display_name, user_devices.is_trusted, user_devices.trusted_at, user_devices.last_ip, user_devices.last_seen_at, user_devices.fingerprint_payload, user_devices.id, user_devices.created_at, user_devices.updated_at 
FROM user_devices 
WHERE user_devices.user_id = $1::UUID AND user_devices.fingerprint_hash = $2::VARCHAR AND user_devices.is_trusted IS true

INSERT INTO transfers (user_id, from_account_id, to_account_id, amount, currency, status, idempotency_key, id) VALUES ($1::UUID, $2::UUID, $3::UUID, $4, $5::VARCHAR, $6, $7::VARCHAR, $8::UUID) RETURNING transfers.created_at, transfers.updated_at

CREATE INDEX ix_offer_participations_status ON offer_participations (status)

SHOW search_path /*application='PgHero'*/

INSERT INTO deposits (user_id, account_id, provider_id, external_id, amount, currency, status, tx_hash, confirmed_at, ledger_entry_id, id) VALUES ($1::UUID, $2::UUID, $3::UUID, $4::VARCHAR, $5, $6::VARCHAR, $7, $8::VARCHAR, $9::TIMESTAMP WITH TIME ZONE, $10::UUID, $11::UUID) RETURNING deposits.created_at, deposits.updated_at

CREATE INDEX ix_program_placements_user_id ON program_placements (user_id)

ALTER TABLE users ADD COLUMN birth_date DATE

CREATE INDEX ix_referral_rewards_referrer_id ON referral_rewards (referrer_id)