Supabase Database Guide

Complete reference for the Supabase database powering UberLotto v2. Covers all tables, Row Level Security policies, database functions, connection configuration, and TypeScript interfaces.

Project Information

Property	Value
Project ID	iwmsnkgimodfucvwecvc
Organization	Dynflux

Tables Overview

Table	RLS	Description
jackpots	Yes	Current lottery jackpot amounts
past_drawings	Yes	Historical drawing results (last 90 days)
payment_transactions	Yes	Unified payment records (MoonPay + Plisio)
payment_transaction_events	Yes	Immutable payment status audit log
webhook_nonces	Yes	Replay attack prevention nonces
security_events	Yes	Security audit log

Table Definitions

jackpots

Current jackpot amounts for lottery games. Updated by an external data pipeline.

CREATE TABLE jackpots (
  id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  game_name TEXT,
  slug TEXT NOT NULL,
  jackpot_numeric NUMERIC,             -- Raw numeric value
  jackpot_alpha_numeric TEXT,          -- Display format: "$1.5 Billion"
  time_stamp_iso TIMESTAMP WITHOUT TIME ZONE
);

Primary Key: idRLS: Enabled

---

past_drawings

Last 90 days of drawing results for all supported games (Powerball, Mega Millions, Megabucks, Lucky for Life, Gimme 5, Lotto America).

CREATE TABLE past_drawings (
  id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  game_name TEXT,
  slug TEXT,
  draw_date DATE,
  drawing_time_et TIME WITHOUT TIME ZONE,
  draw_datetime_iso TIMESTAMP WITHOUT TIME ZONE,
  winning_numbers TEXT,
  bonus_ball NUMERIC,
  multiplier TEXT,
  jackpot_status TEXT,                 -- 'Rollover' or winner state initials
  drawing_official_number NUMERIC UNIQUE,
  drawing_official_link TEXT,
  jackpot_usd NUMERIC
);

Primary Key: idUnique: drawing_official_numberRLS: Enabled

TIP

The slug column links each drawing to its corresponding Shopify product via the custom.game_slug metafield. Both jackpots.slug and past_drawings.slug must match the product metafield exactly.

---

payment_transactions

Unified payment records for all cryptocurrency payment providers (MoonPay and Plisio). Replaces the legacy wallet_load_credit and pending_transactions tables.

CREATE TABLE payment_transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id TEXT,                              -- Shopify GID: gid://shopify/Customer/123
  user_email TEXT NOT NULL,
  user_name TEXT,
  user_lastname TEXT,
  order_number TEXT UNIQUE NOT NULL,         -- UL-xxx (Plisio), MP-xxx (MoonPay)
  provider TEXT NOT NULL
    CHECK (provider IN ('moonpay', 'plisio')),
  provider_txn_id TEXT,                      -- Provider's transaction ID
  provider_invoice_url TEXT,
  amount NUMERIC NOT NULL
    CHECK (amount > 0 AND amount <= 10000),
  amount_usd NUMERIC(12, 2),
  currency TEXT NOT NULL DEFAULT 'USD',
  crypto_currency TEXT,
  wallet_address TEXT,
  tx_hash TEXT,
  conversion_rate NUMERIC,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN (
      'pending', 'processing', 'awaiting_confirmation',
      'completed', 'failed', 'cancelled', 'expired',
      'refunded', 'partially_refunded', 'error'
    )),
  error_code TEXT,
  error_message TEXT,
  metadata JSONB,
  ip_address TEXT,                           -- GDPR-masked
  user_agent TEXT,                           -- Truncated to 512 chars
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ
);

Primary Key: id (UUID) Unique: order_numberRLS: Enabled

Indexes:

Index	Column(s)	Purpose
idx_pt_user_email	user_email	User transaction lookups
idx_pt_user_id	user_id (partial, WHERE NOT NULL)	Shopify customer lookups
idx_pt_active_status	status (partial, WHERE status IN pending/processing/awaiting_confirmation)	Active transaction queries
idx_pt_provider_status	(provider, status)	Provider-specific filtering
idx_pt_created_at	created_at DESC	Time-based queries
idx_pt_provider_txn_id	provider_txn_id (partial, WHERE NOT NULL)	Provider transaction lookups
idx_pt_order_number	order_number	Order lookups
idx_pt_expires_pending	expires_at (partial, WHERE status = 'pending')	Stale transaction cleanup

WARNING

The status column accepts 10 values reflecting a full payment lifecycle. The amount column has guardrails: must be greater than 0 and no more than 10,000.

Column	Description
order_number	Unique order ID: UL-xxx for Plisio, MP-xxx for MoonPay
provider	Payment provider: 'moonpay' or 'plisio'
status	Full lifecycle: pending → processing → completed / failed / expired etc.
ip_address	Client IP, GDPR-masked (e.g., 192.168.xxx.xxx)
user_agent	Browser user agent, truncated to 512 characters
metadata	Flexible JSONB for provider-specific data

---

payment_transaction_events

Immutable audit log for payment status transitions. Automatically populated by a database trigger on payment_transactions status changes.

CREATE TABLE payment_transaction_events (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  transaction_id UUID NOT NULL
    REFERENCES payment_transactions(id) ON DELETE CASCADE,
  previous_status TEXT
    CHECK (previous_status IN (
      'pending', 'processing', 'awaiting_confirmation',
      'completed', 'failed', 'cancelled', 'expired',
      'refunded', 'partially_refunded', 'error'
    )),
  new_status TEXT
    CHECK (new_status IN (
      'pending', 'processing', 'awaiting_confirmation',
      'completed', 'failed', 'cancelled', 'expired',
      'refunded', 'partially_refunded', 'error'
    )),
  changed_by TEXT,                           -- 'webhook', 'system', 'cron', 'manual', 'user'
  change_reason TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Primary Key: id (BIGINT GENERATED ALWAYS AS IDENTITY) Foreign Key: transaction_id → payment_transactions(id) ON DELETE CASCADE RLS: Enabled

Indexes:

Index	Column(s)	Purpose
idx_pte_transaction_id	transaction_id	Event lookups by transaction
idx_pte_txn_created	(transaction_id, created_at DESC)	Chronological event history

TIP

Events are automatically created by the trg_log_status_change trigger. A maximum of 50 events per transaction is enforced by trg_guard_max_events to prevent abuse.

---

webhook_nonces

Stores webhook nonces for replay attack prevention. Each nonce auto-expires after 5 minutes.

CREATE TABLE webhook_nonces (
  id BIGSERIAL PRIMARY KEY,
  nonce_hash TEXT UNIQUE NOT NULL,     -- SHA-256 hash of txn_id:status:amount:order_number
  txn_id TEXT NOT NULL,
  status TEXT,
  amount TEXT,
  order_number TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL      -- 5 minutes from creation
);

Primary Key: id (BIGSERIAL) Unique: nonce_hashRLS: Enabled

Indexes:

Index	Column(s)	Purpose
idx_webhook_nonces_nonce_hash	nonce_hash	Fast duplicate lookup
idx_webhook_nonces_expires_at	expires_at	Efficient cleanup queries
idx_webhook_nonces_txn_id	txn_id (partial)	Transaction lookups
idx_webhook_nonces_created_at	created_at DESC	Recent nonce queries

---

security_events

Security audit log for the Plisio payment gateway. GDPR compliant with data masking on IP addresses and emails.

CREATE TABLE security_events (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  severity TEXT NOT NULL
    CHECK (severity IN ('info', 'warning', 'error', 'critical')),
  source TEXT NOT NULL,
  client_ip TEXT,                      -- Masked: 192.168.xxx.xxx
  user_email TEXT,                     -- Masked: te***t@example.com
  txn_id TEXT,
  order_number TEXT,
  amount NUMERIC(12, 2),
  currency TEXT,
  user_agent TEXT,
  status TEXT,
  error_message TEXT,
  event_data JSONB,                    -- Flexible storage for additional context
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Primary Key: id (BIGSERIAL) RLS: Enabled

Indexes:

Index	Column(s)	Purpose
idx_security_events_event_type	event_type	Filter by event type
idx_security_events_severity	severity	Filter by severity
idx_security_events_created_at	created_at DESC	Time-based queries
idx_security_events_txn_id	txn_id (partial)	Transaction lookups
idx_security_events_client_ip	client_ip (partial)	IP-based searches
idx_security_events_status	status	Status filtering
idx_security_events_source	source	Source filtering
idx_security_events_type_created	(event_type, created_at DESC)	Composite time+type

Event Types:

Event Type	Severity	Description
hmac_failure	error	Invalid webhook signature
hmac_success	info	Signature verified
replay_detected	critical	Duplicate webhook blocked
rate_limit_violation	warning	Rate limit exceeded
payment_completed	info	Successful payment
payment_failed	error	Failed payment
webhook_received	info	Webhook received

Row Level Security (RLS)

All tables have RLS enabled. The service_role bypasses RLS automatically in Supabase.

Standard Policy (All Tables)

CREATE POLICY "Service role full access" ON <table_name>
  FOR ALL USING (auth.role() = 'service_role');

Authenticated User Policy: payment_transactions

Authenticated users can read their own transactions:

CREATE POLICY "Users read own transactions" ON payment_transactions
  FOR SELECT USING (
    auth.role() = 'authenticated'
    AND user_email = (auth.jwt() ->> 'email')
  );

Authenticated User Policy: payment_transaction_events

Authenticated users can read events for their own transactions:

CREATE POLICY "Users read own events" ON payment_transaction_events
  FOR SELECT USING (
    auth.role() = 'authenticated'
    AND transaction_id IN (
      SELECT id FROM payment_transactions
      WHERE user_email = (auth.jwt() ->> 'email')
    )
  );

Additional Policy: security_events

Admin users have read-only access to security events:

CREATE POLICY "Admin read access" ON security_events
  FOR SELECT USING (
    auth.role() = 'authenticated' AND
    (auth.jwt() ->> 'role')::text = 'admin'
  );

Database Functions

Trigger Functions

set_updated_at()

Automatically sets updated_at to NOW() on row updates. Used by payment_transactions.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

fn_log_transaction_status_change()

Automatically logs status transitions to payment_transaction_events. Reads session variables app.changed_by and app.change_reason for audit context.

-- Triggered on UPDATE of payment_transactions when status changes
-- Reads: current_setting('app.changed_by', true)
-- Reads: current_setting('app.change_reason', true)
-- Inserts a row into payment_transaction_events

fn_guard_max_events_per_transaction()

Anti-abuse guard that prevents more than 50 events per transaction. Raises an exception if the limit is reached.

fn_guard_immutable_completed()

Prevents reopening settled transactions. Once a transaction reaches completed, only a transition to refunded is allowed. Transactions in refunded status are fully locked.

Triggers

Trigger	Table	Event	Description
trg_payment_transactions_updated_at	payment_transactions	BEFORE UPDATE	Auto-sets updated_at via set_updated_at()
trg_log_status_change	payment_transactions	AFTER UPDATE (status)	Logs status change to payment_transaction_events
trg_guard_max_events	payment_transaction_events	BEFORE INSERT	Limits to 50 events per transaction
trg_guard_immutable_completed	payment_transactions	BEFORE UPDATE (status)	Prevents reopening completed/refunded transactions

Utility Functions

expire_stale_transactions()

Cron function that marks pending transactions past their expires_at as expired.

-- Finds payment_transactions WHERE status = 'pending' AND expires_at < NOW()
-- Updates status to 'expired'
-- Run via pg_cron on a schedule

archive_old_events(interval DEFAULT '90 days')

Cleanup function that removes old transaction events beyond the retention period.

SELECT archive_old_events();              -- Default: 90 days
SELECT archive_old_events('180 days');    -- Custom retention

get_transaction_summary(email TEXT)

Returns aggregated transaction statistics for a given user email. Useful for admin dashboards.

SELECT * FROM get_transaction_summary('user@example.com');

check_cancellation_rate(email TEXT)

Anti-abuse function that returns FALSE if the user has 10 or more cancellations in the last hour.

SELECT check_cancellation_rate('user@example.com');
-- Returns FALSE if abuse threshold exceeded

Security Functions (Existing)

cleanup_expired_nonces()

Deletes expired webhook nonces. Run every 5 minutes via cron.

CREATE OR REPLACE FUNCTION cleanup_expired_nonces()
RETURNS void AS $$
BEGIN
  DELETE FROM webhook_nonces WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

cleanup_old_security_events()

Removes security events older than 1 year. Run monthly.

CREATE OR REPLACE FUNCTION cleanup_old_security_events()
RETURNS void AS $$
BEGIN
  DELETE FROM security_events WHERE created_at < NOW() - INTERVAL '1 year';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

get_recent_security_events()

Query function for monitoring dashboards with optional filtering.

SELECT * FROM get_recent_security_events();                          -- Last 24h
SELECT * FROM get_recent_security_events(NULL, 'critical', 50, 1);   -- Critical, last hour
SELECT * FROM get_recent_security_events('hmac_failure', NULL, 100, 6); -- HMAC failures, 6h

Connection Configuration

Network Restrictions

Type	CIDRs	Description
Developer IP	178.148.227.175/32	Local development
Cloudflare IPv4	15 ranges	Shopify Oxygen workers
Cloudflare IPv6	7 ranges	Shopify Oxygen workers

DANGER

The database restricts connections to known IP ranges. When deploying to a new environment, ensure its IP range is added to the Supabase network restrictions.

Connection Strings

Direct Connection:

postgresql://postgres:[PASSWORD]@db.iwmsnkgimodfucvwecvc.supabase.co:5432/postgres

Connection Pooler (Recommended for production):

postgresql://postgres:[PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres

TypeScript Interfaces

Located in app/shared-types/supabase.ts. These interfaces are shared between client and server code.

PaymentStatus

type PaymentStatus =
  | 'pending'
  | 'processing'
  | 'awaiting_confirmation'
  | 'completed'
  | 'failed'
  | 'cancelled'
  | 'expired'
  | 'refunded'
  | 'partially_refunded'
  | 'error';

PaymentProvider

type PaymentProvider = 'moonpay' | 'plisio';

PaymentTransaction

interface PaymentTransaction {
  id: string;                                // UUID
  user_id: string | null;
  user_email: string;
  user_name: string | null;
  user_lastname: string | null;
  order_number: string;
  provider: PaymentProvider;
  provider_txn_id: string | null;
  provider_invoice_url: string | null;
  amount: number;
  amount_usd: number | null;
  currency: string;
  crypto_currency: string | null;
  wallet_address: string | null;
  tx_hash: string | null;
  conversion_rate: number | null;
  status: PaymentStatus;
  error_code: string | null;
  error_message: string | null;
  metadata: Record<string, unknown> | null;
  ip_address: string | null;
  user_agent: string | null;
  created_at: string;
  updated_at: string;
  completed_at: string | null;
  expires_at: string | null;
}

PaymentTransactionInsert

type PaymentTransactionInsert = Omit<
  PaymentTransaction,
  'id' | 'created_at' | 'updated_at'
>;

PaymentTransactionUpdate

type PaymentTransactionUpdate = Partial<
  Omit<PaymentTransaction, 'id' | 'created_at'>
>;

PaymentTransactionEvent

interface PaymentTransactionEvent {
  id: number;
  transaction_id: string;
  previous_status: PaymentStatus | null;
  new_status: PaymentStatus;
  changed_by: string | null;
  change_reason: string | null;
  metadata: Record<string, unknown> | null;
  created_at: string;
}

JackpotRecord

interface JackpotRecord {
  id: number;
  game_name: string;
  slug: string;
  jackpot_numeric: string | number;
  jackpot_alpha_numeric: string | null;
  time_stamp_iso: string;
  created_at: string;
}

PastDrawingRecord

interface PastDrawingRecord {
  id: number;
  game_name: string;
  slug: string;
  draw_date: string;
  drawing_time_et: string | null;
  draw_datetime_iso: string | null;
  winning_numbers: string;
  bonus_ball: number | null;
  multiplier: string | null;
  jackpot_status: string | null;
  drawing_official_number: number | null;
  drawing_official_link: string | null;
  jackpot_usd: number | null;
  created_at: string;
}

Best Practices

1. Always use the service role key for server-side operations
2. Never expose the service role key to client-side code
3. Monitor security_events regularly for anomalies (especially critical severity)
4. Run cleanup functions on schedule (see Monitoring & Maintenance)
5. Keep TypeScript interfaces in app/shared-types/supabase.ts in sync with schema changes
6. Use the anon key for read-only operations (jackpots, past drawings)