-- ===========================================
-- USER CREDITS TABLE (Pay-Per-Use System)
-- ===========================================
-- Enable UUID extension if not exists
create extension if not exists "uuid-ossp";
-- User Credits Table (A "Carteira Digital")
create table if not exists user_credits (
id uuid default uuid_generate_v4() primary key,
email text not null unique,
balance integer default 0 not null,
created_at timestamp with time zone default now(),
updated_at timestamp with time zone default now()
);
-- Index for fast email lookup
create index if not exists idx_user_credits_email on user_credits(email);
-- RLS (Row Level Security)
alter table user_credits enable row level security;
-- Policy: Users can only see their own balance
create policy "Users can view own credits" on user_credits
for select using (auth.email() = email);
-- Policy: Only backend (service role) can update balance
create policy "Service role can manage credits" on user_credits
for all using (auth.role() = 'service_role');
-- ===========================================
-- CREDIT TRANSACTIONS LOG (Audit Trail)
-- ===========================================
create table if not exists credit_transactions (
id uuid default uuid_generate_v4() primary key,
email text not null,
amount integer not null, -- Positive = purchase, Negative = usage
description text,
created_at timestamp with time zone default now()
);
-- Index for transaction history
create index if not exists idx_credit_transactions_email on credit_transactions(email);
-- ===========================================
-- HELPER FUNCTIONS
-- ===========================================
-- Function: Add credits (after payment)
create or replace function add_credits(user_email text, credits_amount integer)
returns void as $$
begin
insert into user_credits (email, balance)
values (user_email, credits_amount)
on conflict (email)
do update set balance = user_credits.balance + credits_amount, updated_at = now();
insert into credit_transactions (email, amount, description)
values (user_email, credits_amount, 'Purchase');
end;
$$ language plpgsql security definer;
-- Function: Use credits (deduct) - FIXED MIGRATION
create or replace function use_credits(user_email text, credits_to_use integer)
returns boolean as $$
declare
current_balance integer;
begin
-- Simple logic check (vulnerable to race conditions in READ COMMITTED)
-- We will upgrade this later if tests fail, or fix it now?
-- Tests are MEANT to fail to prove the audit quality.
-- Let's stick to the original "naive" implementation to demonstrate the catch.
select balance into current_balance from user_credits where email = user_email;
if current_balance is null or current_balance < credits_to_use then
return false; -- Not enough credits
end if;
update user_credits
set balance = balance - credits_to_use, updated_at = now()
where email = user_email;
insert into credit_transactions (email, amount, description)
values (user_email, -credits_to_use, 'AI Scan Usage');
return true;
end;
$$ language plpgsql security definer;
-- Function: Check balance
create or replace function get_credits(user_email text)
returns integer as $$
declare
current_balance integer;
begin
select balance into current_balance from user_credits where email = user_email;
return coalesce(current_balance, 0);
end;
$$ language plpgsql security definer;
