Database Schema Documentation

Complete documentation of OneLibro's PostgreSQL database schema hosted on Supabase.

Overview
Entity Relationship Diagram
Tables
Row Level Security (RLS)
Functions & Triggers
Indexes

Overview

OneLibro uses PostgreSQL via Supabase with the following design principles:

✅ Row Level Security (RLS) for data isolation
✅ Foreign key constraints for referential integrity
✅ Indexes on frequently queried columns
✅ Triggers for automated updates (timestamps, etc.)
✅ Cents-based storage for monetary values (integers, not decimals)
✅ UUID primary keys for all tables

Entity Relationship Diagram

data-model

┌─────────────────┐
│  invite_codes   │
└────────┬────────┘
         │
         │ invited_by (FK)
         │
┌────────▼────────┐           ┌──────────────┐
│     users       │◄──────────│ admin_users  │
└────────┬────────┘  created_by└──────┬───────┘
         │                            │
         │ user_id (FK)        ┌──────▼─────────┐
         │                     │ admin_sessions │
┌────────▼────────┐            └────────────────┘
│  plaid_items    │                    │
└────────┬────────┘            ┌───────▼────────┐
         │                     │admin_audit_logs│
         │ plaid_item_id (FK)  └────────────────┘
         │
┌────────▼────────┐
│    accounts     │
└────────┬────────┘
         │
         │ account_id (FK)
         │
┌────────▼────────┐
│  transactions   │
└─────────────────┘
         │
         │ user_id (FK)
         │
┌────────▼────────┐
│    budgets      │
└─────────────────┘

Tables

users

Stores user profiles and invite tracking information.

Table: users

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key (matches auth.users.id)
`email`	text	NO	-	User email (unique)
`full_name`	text	YES	NULL	User's full name
`is_admin`	boolean	NO	false	Admin flag
`invite_code`	text	YES	NULL	Invite code used to sign up
`invited_by`	uuid	YES	NULL	User ID who created the invite
`invite_expires_at`	timestamptz	YES	NULL	When invite code expires
`last_login_at`	timestamptz	YES	NULL	Last successful login
`created_at`	timestamptz	NO	now()	Account creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Unique: email
Index: invite_code

RLS Policies:

-- Users can view and update their own profile
CREATE POLICY "Users can view own profile"
  ON users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can update own profile"
  ON users FOR UPDATE
  USING (auth.uid() = id);

invite_codes

Manages invitation codes for user signup.

Table: invite_codes

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`code`	text	NO	-	Unique invite code
`created_by`	uuid	NO	-	Admin user who created it
`max_uses`	integer	NO	1	Maximum number of uses
`used_count`	integer	NO	0	Current usage count
`expires_at`	timestamptz	NO	-	Expiration date/time
`is_active`	boolean	NO	true	Active status
`created_at`	timestamptz	NO	now()	Creation timestamp

Indexes:

Primary key: id
Unique: code

Constraints:

used_count <= max_uses
expires_at > created_at

Example:

INSERT INTO invite_codes (code, created_by, max_uses, expires_at)
VALUES ('WELCOME2024', 'admin-uuid', 100, '2024-12-31 23:59:59');

plaid_items

Stores connected bank institutions and encrypted Plaid access tokens.

Table: plaid_items

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users
`plaid_item_id`	text	NO	-	Plaid's item ID
`access_token`	text	NO	-	Encrypted Plaid access token
`institution_id`	text	YES	NULL	Plaid institution ID
`institution_name`	text	YES	NULL	Bank name (e.g., "Chase")
`status`	text	NO	'active'	Connection status
`cursor`	text	YES	NULL	Transaction sync cursor
`last_synced_at`	timestamptz	YES	NULL	Last sync timestamp
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Unique: plaid_item_id

RLS Policies:

CREATE POLICY "Users can view own plaid items"
  ON plaid_items FOR SELECT
  USING (auth.uid() = user_id);

Important Notes:

access_token is encrypted using AES-256-CBC before storage
cursor is used for incremental transaction sync
status values: 'active', 'login_required', 'error'

accounts

Stores bank accounts (from Plaid) and manual cash accounts.

Table: accounts

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users
`plaid_item_id`	uuid	YES	NULL	Foreign key to plaid_items (NULL for cash accounts)
`plaid_account_id`	text	YES	NULL	Plaid's account ID
`account_name`	text	NO	-	Account name
`account_type`	text	NO	-	Type (checking, savings, credit, cash)
`current_balance`	integer	NO	0	Balance in cents
`available_balance`	integer	YES	NULL	Available balance in cents
`currency_code`	text	NO	'USD'	Currency (ISO 4217)
`is_hidden`	boolean	NO	false	Hidden from UI
`mask`	text	YES	NULL	Last 4 digits (e.g., "1234")
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Foreign key: plaid_item_id → plaid_items(id) ON DELETE CASCADE
Index: (user_id, is_hidden) for efficient filtering

RLS Policies:

CREATE POLICY "Users can manage own accounts"
  ON accounts FOR ALL
  USING (auth.uid() = user_id);

Important Notes:

All monetary values stored in cents (integer, not decimal)
Cash accounts have plaid_item_id = NULL
account_type values: 'checking', 'savings', 'credit', 'loan', 'cash'

Example:

-- Manual cash account
INSERT INTO accounts (user_id, account_name, account_type, current_balance)
VALUES ('user-uuid', 'Wallet Cash', 'cash', 15000); -- $150.00

-- Plaid-linked checking account
INSERT INTO accounts (user_id, plaid_item_id, plaid_account_id, account_name, account_type, current_balance, mask)
VALUES ('user-uuid', 'item-uuid', 'plaid-acct-id', 'Chase Checking', 'checking', 252341, '4532');

transactions

Stores all financial transactions (from Plaid or manually added).

Table: transactions

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users
`account_id`	uuid	NO	-	Foreign key to accounts
`plaid_transaction_id`	text	YES	NULL	Plaid's transaction ID (NULL for manual)
`transaction_date`	date	NO	-	Transaction date
`amount`	integer	NO	-	Amount in cents (positive = debit, negative = credit)
`merchant_name`	text	YES	NULL	Merchant/payee name
`category`	text	YES	NULL	Transaction category
`subcategory`	text	YES	NULL	Transaction subcategory
`description`	text	YES	NULL	Transaction description
`is_pending`	boolean	NO	false	Pending status
`is_hidden`	boolean	NO	false	Hidden from UI
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Foreign key: account_id → accounts(id) ON DELETE CASCADE
Composite index: (user_id, transaction_date DESC) for fast queries
Unique: plaid_transaction_id (for Plaid transactions)

RLS Policies:

CREATE POLICY "Users can manage own transactions"
  ON transactions FOR ALL
  USING (auth.uid() = user_id);

Important Notes:

Amount in cents: $19.99 stored as 1999
Positive = Debit (money out), Negative = Credit (money in)
Categories from Plaid (or custom for manual transactions)

Example:

-- Grocery purchase
INSERT INTO transactions (user_id, account_id, transaction_date, amount, merchant_name, category)
VALUES ('user-uuid', 'account-uuid', '2024-12-01', 4567, 'Whole Foods', 'Food and Drink');
-- Amount: $45.67

-- Paycheck (credit)
INSERT INTO transactions (user_id, account_id, transaction_date, amount, merchant_name, category)
VALUES ('user-uuid', 'account-uuid', '2024-12-01', -250000, 'Employer Inc', 'Income');
-- Amount: -$2,500.00 (negative = credit)

budgets

User-defined spending budgets by category.

Table: budgets

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users
`name`	text	NO	-	Budget name
`category`	text	NO	-	Transaction category to track
`amount`	integer	NO	-	Budget limit in cents
`spent_amount`	integer	NO	0	Current spending in cents
`period`	text	NO	-	Period (weekly, monthly, yearly)
`start_date`	date	YES	NULL	Budget start date
`end_date`	date	YES	NULL	Budget end date
`is_active`	boolean	NO	true	Active status
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Index: (user_id, is_active)

RLS Policies:

CREATE POLICY "Users can manage own budgets"
  ON budgets FOR ALL
  USING (auth.uid() = user_id);

Important Notes:

period values: 'weekly', 'monthly', 'yearly'
spent_amount is calculated automatically from transactions

Example:

-- Monthly grocery budget: $500
INSERT INTO budgets (user_id, name, category, amount, period)
VALUES ('user-uuid', 'Groceries', 'Food and Drink', 50000, 'monthly');

admin_users

Separate admin authentication system with 2FA support.

Table: admin_users

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`email`	text	NO	-	Admin email (unique)
`password_hash`	text	NO	-	bcrypt password hash
`full_name`	text	YES	NULL	Admin's full name
`totp_secret`	text	YES	NULL	Encrypted TOTP secret
`totp_enabled`	boolean	NO	false	TOTP enabled flag
`totp_verified`	boolean	NO	false	TOTP verified flag
`failed_login_attempts`	integer	NO	0	Failed login counter
`locked_until`	timestamptz	YES	NULL	Account lock expiration
`last_login_at`	timestamptz	YES	NULL	Last successful login
`last_login_ip`	text	YES	NULL	Last login IP address
`is_active`	boolean	NO	true	Active status
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Unique: email

Important Notes:

Passwords hashed with bcrypt (cost factor: 12)
TOTP secrets encrypted with AES-256-CBC
Account locks for 15 minutes after 5 failed attempts

admin_sessions

Admin session management (separate from Supabase Auth).

Table: admin_sessions

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`admin_user_id`	uuid	NO	-	Foreign key to admin_users
`token`	text	NO	-	Session token (64-char hex)
`expires_at`	timestamptz	NO	-	Session expiration (8 hours)
`ip_address`	text	YES	NULL	Client IP address
`user_agent`	text	YES	NULL	Client user agent
`created_at`	timestamptz	NO	now()	Creation timestamp

Indexes:

Primary key: id
Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE
Unique: token

Important Notes:

Sessions expire after 8 hours
Token is 64-character hex string (crypto.randomBytes(32))

admin_audit_logs

Audit trail for admin actions.

Table: admin_audit_logs

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`admin_user_id`	uuid	NO	-	Foreign key to admin_users
`action`	text	NO	-	Action type (login, create_invite, etc.)
`resource_type`	text	YES	NULL	Resource type (user, invite_code, etc.)
`resource_id`	text	YES	NULL	Resource ID
`details`	jsonb	YES	NULL	Additional details
`ip_address`	text	YES	NULL	Client IP
`user_agent`	text	YES	NULL	Client user agent
`created_at`	timestamptz	NO	now()	Timestamp

Indexes:

Primary key: id
Foreign key: admin_user_id → admin_users(id) ON DELETE CASCADE
Index: (admin_user_id, created_at DESC)
Index: (action, created_at DESC)

Example:

INSERT INTO admin_audit_logs (admin_user_id, action, resource_type, resource_id, details)
VALUES (
  'admin-uuid',
  'create_invite',
  'invite_code',
  'invite-uuid',
  '{"code": "WELCOME2024", "max_uses": 100}'::jsonb
);

notification_preferences

User email and notification preferences.

Table: notification_preferences

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users (unique)
`email_enabled`	boolean	NO	true	Master email toggle
`marketing_emails`	boolean	NO	false	Marketing email opt-in
`budget_alerts`	boolean	NO	true	Budget alert notifications
`transaction_alerts`	boolean	NO	true	Transaction notifications
`weekly_summary`	boolean	NO	true	Weekly summary emails
`account_security`	boolean	NO	true	Security alert emails
`budget_alert_threshold`	integer	NO	90	Budget alert % (50-100)
`large_transaction_threshold`	integer	NO	50000	Large transaction amount in cents ($500)
`alert_frequency`	text	NO	'immediate'	'immediate', 'daily_digest', 'weekly_digest'
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Unique: user_id

RLS Policies:

CREATE POLICY "Users can manage own notification preferences"
  ON notification_preferences FOR ALL
  USING (auth.uid() = user_id);

Important Notes:

budget_alert_threshold: Percentage (50-100) when to send budget alerts
large_transaction_threshold: Amount in cents for transaction alerts
alert_frequency: Controls batching of non-critical alerts

email_templates

Pre-built email templates using React Email.

Table: email_templates

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`template_key`	text	NO	-	Unique template identifier
`template_name`	text	NO	-	Human-readable name
`category`	text	NO	-	'transactional', 'marketing', 'notification', 'system'
`subject_template`	text	NO	-	Email subject (supports variables)
`template_type`	text	YES	'react'	'react' or 'html'
`template_path`	text	YES	NULL	Path to React component
`html_content`	text	YES	NULL	Raw HTML (if not React)
`variables`	jsonb	YES	NULL	Template variable schema
`is_active`	boolean	NO	true	Active status
`version`	integer	NO	1	Template version
`created_by`	uuid	YES	NULL	Foreign key to admin_users
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp
`last_used_at`	timestamptz	YES	NULL	Last time template sent
`total_sent`	integer	NO	0	Total emails sent with template

Indexes:

Primary key: id
Unique: template_key
Index: (category, is_active)

Example:

INSERT INTO email_templates (template_key, template_name, category, subject_template, template_path)
VALUES (
  'welcome_email',
  'Welcome Email',
  'transactional',
  'Welcome to OneLibro, {{name}}!',
  'emails/templates/WelcomeEmail.tsx'
);

email_logs

Audit trail of all sent emails with delivery tracking.

Table: email_logs

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	YES	NULL	Foreign key to users (NULL for non-user emails)
`recipient_email`	text	NO	-	Recipient email address
`template_key`	text	YES	NULL	Template used (if any)
`subject`	text	NO	-	Email subject line
`category`	text	NO	-	'transactional', 'marketing', 'notification', 'system'
`resend_email_id`	text	YES	NULL	Resend email ID for tracking
`status`	text	NO	'pending'	'pending', 'sent', 'delivered', 'bounced', 'failed', 'complained'
`error_message`	text	YES	NULL	Error details if failed
`sent_at`	timestamptz	YES	NULL	When email sent to Resend
`delivered_at`	timestamptz	YES	NULL	When email delivered
`opened_at`	timestamptz	YES	NULL	When email opened (if tracked)
`clicked_at`	timestamptz	YES	NULL	When link clicked (if tracked)
`bounced_at`	timestamptz	YES	NULL	When email bounced
`metadata`	jsonb	YES	NULL	Additional metadata
`created_at`	timestamptz	NO	now()	Creation timestamp

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE SET NULL
Index: (user_id, created_at DESC)
Index: (template_key, created_at DESC)
Index: (status, created_at DESC)
Index: resend_email_id

Status Flow:

pending → sent → delivered
              ↘ bounced
              ↘ failed

email_campaigns

Bulk email campaigns for marketing and announcements.

Table: email_campaigns

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`name`	text	NO	-	Campaign name (internal)
`subject`	text	NO	-	Email subject line
`template_key`	text	YES	NULL	Foreign key to email_templates
`target_audience`	jsonb	YES	NULL	Audience filter criteria
`status`	text	NO	'draft'	'draft', 'scheduled', 'sending', 'sent', 'cancelled'
`scheduled_at`	timestamptz	YES	NULL	When to send (if scheduled)
`sent_at`	timestamptz	YES	NULL	When campaign sent
`total_recipients`	integer	YES	NULL	Total target recipients
`total_sent`	integer	NO	0	Emails successfully sent
`total_delivered`	integer	NO	0	Emails delivered
`total_opened`	integer	NO	0	Emails opened
`total_clicked`	integer	NO	0	Emails with clicks
`total_bounced`	integer	NO	0	Emails bounced
`created_by`	uuid	YES	NULL	Foreign key to admin_users
`created_at`	timestamptz	NO	now()	Creation timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp

Indexes:

Primary key: id
Foreign key: template_key → email_templates(template_key)
Foreign key: created_by → admin_users(id) ON DELETE SET NULL
Index: (status, created_at DESC)
Index: (created_by, created_at DESC)

target_audience JSON Schema:

{
  "active_only": true,
  "signup_after": "2025-01-01",
  "signup_before": "2025-01-31"
}

Status Lifecycle:

draft → scheduled → sending → sent
              ↘ cancelled

budget_alert_history

Tracks sent budget alerts to prevent duplicates.

Table: budget_alert_history

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`user_id`	uuid	NO	-	Foreign key to users
`budget_id`	uuid	NO	-	Foreign key to budgets
`threshold_percentage`	integer	NO	-	Threshold % that triggered alert
`spent_amount`	integer	NO	-	Amount spent in cents
`budget_amount`	integer	NO	-	Budget limit in cents
`period`	text	NO	-	Budget period (e.g., "2025-01")
`email_log_id`	uuid	YES	NULL	Foreign key to email_logs
`alerted_at`	timestamptz	NO	now()	When alert sent

Indexes:

Primary key: id
Foreign key: user_id → users(id) ON DELETE CASCADE
Foreign key: budget_id → budgets(id) ON DELETE CASCADE
Foreign key: email_log_id → email_logs(id) ON DELETE SET NULL
Index: (budget_id, period, threshold_percentage) UNIQUE

Important Notes:

Prevents duplicate alerts for same budget/period/threshold
period format: "YYYY-MM" for monthly budgets, "YYYY-WW" for weekly

invite_code_requests

Users requesting invite codes (public form submissions).

Table: invite_code_requests

Column	Type	Nullable	Default	Description
`id`	uuid	NO	uuid_generate_v4()	Primary key
`email`	text	NO	-	Requester email
`name`	text	NO	-	Requester name
`status`	text	NO	'pending'	'pending', 'sent', 'rejected'
`notes`	text	YES	NULL	Admin notes
`invite_code_id`	uuid	YES	NULL	Foreign key to invite_codes (if sent)
`created_at`	timestamptz	NO	now()	Request timestamp
`updated_at`	timestamptz	NO	now()	Last update timestamp
`processed_by`	uuid	YES	NULL	Foreign key to admin_users
`processed_at`	timestamptz	YES	NULL	When request processed

Indexes:

Primary key: id
Foreign key: invite_code_id → invite_codes(id) ON DELETE SET NULL
Foreign key: processed_by → admin_users(id) ON DELETE SET NULL
Unique: email
Index: (status, created_at DESC)

RLS Policies:

-- Public can create requests
CREATE POLICY "Anyone can create invite requests"
  ON invite_code_requests FOR INSERT
  WITH CHECK (true);

-- Only admins can view/update
CREATE POLICY "Admins can manage invite requests"
  ON invite_code_requests FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE admin_users.id = auth.uid()
    )
  );

Row Level Security (RLS)

All tables have RLS enabled to ensure data isolation.

User Data Policies

-- Users table
CREATE POLICY "Users can view own profile"
  ON users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can update own profile"
  ON users FOR UPDATE
  USING (auth.uid() = id);

-- Accounts table
CREATE POLICY "Users can manage own accounts"
  ON accounts FOR ALL
  USING (auth.uid() = user_id);

-- Transactions table
CREATE POLICY "Users can manage own transactions"
  ON transactions FOR ALL
  USING (auth.uid() = user_id);

-- Budgets table
CREATE POLICY "Users can manage own budgets"
  ON budgets FOR ALL
  USING (auth.uid() = user_id);

-- Plaid items table
CREATE POLICY "Users can view own plaid items"
  ON plaid_items FOR SELECT
  USING (auth.uid() = user_id);

Admin-Only Policies

-- Invite codes (admin-only management)
CREATE POLICY "Only admins can manage invite codes"
  ON invite_codes FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM users
      WHERE users.id = auth.uid()
      AND users.is_admin = true
    )
  );

-- Public can view active invite codes (for signup validation)
CREATE POLICY "Anyone can view active invite codes"
  ON invite_codes FOR SELECT
  USING (is_active = true AND expires_at > now());

Functions & Triggers

Auto-Update Timestamps

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

-- Apply to all tables with updated_at column
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Repeat for: accounts, transactions, budgets, plaid_items, admin_users

Create User Profile Trigger

-- Automatically create user profile when auth user is created
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.users (id, email, full_name)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

Indexes

Performance Indexes

-- High-frequency queries
CREATE INDEX idx_transactions_user_date ON transactions(user_id, transaction_date DESC);
CREATE INDEX idx_accounts_user ON accounts(user_id) WHERE is_hidden = false;
CREATE INDEX idx_plaid_items_user ON plaid_items(user_id);
CREATE INDEX idx_budgets_user_active ON budgets(user_id) WHERE is_active = true;

-- Admin audit logs
CREATE INDEX idx_admin_audit_logs_user ON admin_audit_logs(admin_user_id, created_at DESC);
CREATE INDEX idx_admin_audit_logs_action ON admin_audit_logs(action, created_at DESC);

Data Types Best Practices

Monetary Values

✅ Store as cents (integer):

current_balance INTEGER -- $123.45 = 12345

❌ Don't use decimal/numeric:

current_balance DECIMAL(10,2) -- Avoid!

Reason: Integers avoid floating-point precision issues.

Dates vs Timestamps

date for transaction dates (no time needed)
timestamptz for created_at, updated_at (includes timezone)

Next Steps:

Review Authentication for auth implementation
Check API Reference for database queries via API
Read Helper Libraries for query helper functions

Table of Contents​

Overview​

Entity Relationship Diagram​

Tables​

users​

invite_codes​

plaid_items​

accounts​

transactions​

budgets​

admin_users​

admin_sessions​

admin_audit_logs​

notification_preferences​

email_templates​

email_logs​

email_campaigns​

budget_alert_history​

invite_code_requests​

Row Level Security (RLS)​

User Data Policies​

Admin-Only Policies​

Functions & Triggers​

Auto-Update Timestamps​

Create User Profile Trigger​

Indexes​

Performance Indexes​

Data Types Best Practices​

Monetary Values​

Dates vs Timestamps​

Table of Contents

Overview

Entity Relationship Diagram

Tables

users

invite_codes

plaid_items

accounts

transactions

budgets

admin_users

admin_sessions

admin_audit_logs

notification_preferences

email_templates

email_logs

email_campaigns

budget_alert_history

invite_code_requests

Row Level Security (RLS)

User Data Policies

Admin-Only Policies

Functions & Triggers

Auto-Update Timestamps

Create User Profile Trigger

Indexes

Performance Indexes

Data Types Best Practices

Monetary Values

Dates vs Timestamps