Skip to main content
The Money Tracker database is built on PostgreSQL with Supabase. This page documents all tables, columns, relationships, and constraints.

​
Core Tables

​
users

Extends Supabase auth.users table to store additional user information.
​
id
UUID
required
Primary key. References auth.users(id) with CASCADE delete
​
email
VARCHAR(255)
User’s email address (redundant with auth.users but useful for queries)
​
name
VARCHAR(255)
User’s display name
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when user record was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Triggers:
  • update_users_updated_at: Automatically updates updated_at before each update
  • on_auth_user_created: Automatically creates user record when auth.users entry is created

​
transactions

Stores all financial transactions extracted from emails or manually added by users.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id). Owner of this transaction
​
user_oauth_token_id
UUID
References user_oauth_tokens(id) with CASCADE delete. The Gmail account that received the transaction email (NULL for manual transactions)
​
source_email
VARCHAR(255)
required
Email address of the sender (merchant, bank, etc.)
​
source_message_id
VARCHAR(255)
Gmail message ID. Ensures each email only creates one transaction
​
amount
NUMERIC(20,2)
required
Transaction amount. Supports values from -99,999,999,999,999.99 to 99,999,999,999,999.99
​
currency
VARCHAR(3)
default:"USD"
required
Currency code (e.g., ‘USD’, ‘EUR’, ‘GBP’)
​
transaction_type
VARCHAR(10)
required
Type of transaction. Must be ‘income’ or ‘expense’
​
transaction_description
TEXT
required
Description of the transaction
​
transaction_date
DATE
required
Date when the transaction occurred
​
merchant
VARCHAR(255)
required
Name of the merchant or entity
​
category
TEXT
required
Transaction category. Must be one of: ‘salary’, ‘entertainment’, ‘investment’, ‘food’, ‘transport’, ‘services’, ‘health’, ‘education’, ‘housing’, ‘clothing’, ‘other’
​
extraction_confidence
NUMERIC(3,2)
AI extraction confidence score (0.00 to 1.00)
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when transaction was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_transactions_user_id on user_id
  • idx_transactions_source_message_id on source_message_id
  • idx_transactions_date on transaction_date
  • idx_transactions_type on transaction_type
  • idx_transactions_category on category
  • idx_transactions_created_at on created_at
  • idx_transactions_user_oauth_token_id on user_oauth_token_id
Constraints:
  • transactions_source_message_id_key: UNIQUE on source_message_id
  • transactions_category_check: CHECK category in allowed values
  • check_amount_range: CHECK amount within valid range
Realtime: Enabled with REPLICA IDENTITY FULL

​
user_oauth_tokens

Stores encrypted OAuth tokens for Gmail API access.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
access_token
TEXT
DEPRECATED: Use access_token_encrypted instead
​
access_token_encrypted
BYTEA
Encrypted OAuth access token (using pgcrypto)
​
refresh_token
TEXT
DEPRECATED: Use refresh_token_encrypted instead
​
refresh_token_encrypted
BYTEA
Encrypted OAuth refresh token (using pgcrypto)
​
token_type
VARCHAR(50)
default:"Bearer"
OAuth token type
​
expires_at
TIMESTAMPTZ
Token expiration timestamp
​
scope
TEXT
OAuth scopes granted
​
gmail_email
VARCHAR(255)
Gmail address associated with these tokens
​
is_active
BOOLEAN
Whether this token is currently active
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when token was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_user_oauth_tokens_user_id on user_id
Constraints:
  • UNIQUE on (user_id, gmail_email)

​
gmail_watches

Tracks active Gmail API push notification watches.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
gmail_email
VARCHAR(255)
required
Gmail address being watched
​
watch_id
VARCHAR(255)
Gmail API watch ID
​
topic_name
VARCHAR(255)
required
Google Cloud Pub/Sub topic name
​
label_ids
TEXT[]
Gmail labels being watched (e.g., [‘INBOX’])
​
expiration
TIMESTAMPTZ
When this watch expires
​
expires_at
TIMESTAMPTZ
Alternative expiration field
​
history_id
VARCHAR(255)
Last processed Gmail history ID
​
is_active
BOOLEAN
default:"true"
Whether this watch is active
​
active
BOOLEAN
Alternative active status field
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when watch was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_gmail_watches_user_id on user_id
  • idx_gmail_watches_watch_id on watch_id
Constraints:
  • UNIQUE on (user_id, gmail_email)
  • UNIQUE on watch_id

​
pubsub_subscriptions

Manages Google Cloud Pub/Sub subscription configurations.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
subscription_name
VARCHAR(255)
required
Pub/Sub subscription name
​
topic_name
VARCHAR(255)
required
Pub/Sub topic name
​
push_endpoint
TEXT
required
Webhook URL for push notifications
​
ack_deadline_seconds
INTEGER
default:"60"
Message acknowledgment deadline
​
is_active
BOOLEAN
default:"true"
Whether this subscription is active
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when subscription was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_pubsub_subscriptions_user_id on user_id
  • idx_pubsub_subscriptions_subscription_name on subscription_name
Constraints:
  • UNIQUE on subscription_name
  • UNIQUE on (user_id, topic_name)

​
Processing Tables

​
seeds

Tracks email import jobs (“seed” operations) that fetch historical transactions from Gmail.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
user_oauth_token_id
UUID
required
References user_oauth_tokens(id) with CASCADE delete
​
status
seed_status
default:"pending"
required
Status enum: ‘pending’, ‘processing’, ‘completed’, or ‘failed’
​
error_message
TEXT
Error details if status is ‘failed’
​
last_processed_index
INTEGER
default:"0"
Last processed email index for resumable operations
​
message_ids
TEXT[]
default:"{}"
Array of Gmail message IDs to process
​
total_emails
INTEGER
Total number of emails retrieved from Gmail
​
transactions_found
INTEGER
Number of new transactions found and inserted
​
total_skipped
INTEGER
Number of emails skipped (already processed or not in INBOX)
​
emails_processed_by_ai
INTEGER
Number of emails sent to AI for analysis
​
created_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when seed job was created
​
updated_at
TIMESTAMPTZ
default:"NOW()"
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_seeds_user_id on user_id
  • idx_seeds_status on status
  • idx_seeds_user_oauth_token_id on user_oauth_token_id
Comments:
Simple tracking of seed status to prevent duplicate seeds. Seed status: pending (in progress), completed, or failed.

​
discarded_emails

Caches emails that were analyzed but don’t contain transactions. Prevents re-processing and saves AI costs.
​
id
UUID
required
Primary key, auto-generated
​
user_oauth_token_id
UUID
required
References user_oauth_tokens(id) with CASCADE delete
​
message_id
VARCHAR(255)
required
Gmail message ID of the discarded email
​
reason
TEXT
Why the email was discarded (e.g., “No transaction found”, “Promotional email”)
​
discarded_at
TIMESTAMPTZ
default:"NOW()"
Timestamp when email was discarded
Indexes:
  • idx_discarded_emails_lookup on (user_oauth_token_id, message_id)
  • idx_discarded_emails_token on user_oauth_token_id
  • idx_discarded_emails_discarded_at on discarded_at DESC
Constraints:
  • UNIQUE on (user_oauth_token_id, message_id)
Comments:
Stores emails that were analyzed but did not contain transactions. Prevents re-processing the same emails in future seed jobs, saving time and AI costs.

​
Notification Tables

​
notification_categories

Defines categories for grouping notification types.
​
id
UUID
required
Primary key, auto-generated
​
key
TEXT
required
Unique category identifier (e.g., ‘system’)
​
label_i18n_key
TEXT
required
Internationalization key for category label
​
is_active
BOOLEAN
default:"true"
required
Whether this category is active
​
created_at
TIMESTAMPTZ
default:"now()"
required
Timestamp when category was created
​
updated_at
TIMESTAMPTZ
default:"now()"
required
Timestamp of last update (auto-updated by trigger)

​
notification_types

Defines specific types of notifications that can be sent to users.
​
id
UUID
required
Primary key, auto-generated
​
category_id
UUID
required
References notification_categories(id) with RESTRICT delete
​
key
TEXT
required
Unique notification type identifier
​
label_i18n_key
TEXT
required
Internationalization key for type label
​
description_i18n_key
TEXT
required
Internationalization key for description
​
title_i18n_key
TEXT
required
Internationalization key for notification title
​
body_i18n_key
TEXT
required
Internationalization key for notification body
​
default_importance
notification_importance
default:"normal"
required
Default importance level: ‘low’, ‘normal’, ‘high’, or ‘critical’
​
is_active
BOOLEAN
default:"true"
required
Whether this notification type is active
​
created_at
TIMESTAMPTZ
default:"now()"
required
Timestamp when type was created
​
updated_at
TIMESTAMPTZ
default:"now()"
required
Timestamp of last update (auto-updated by trigger)
Built-in Types:
  • seed_completed_with_transactions: Seed job completed with new transactions found
  • seed_completed_no_new: Seed job completed with no new transactions
  • seed_failed: Seed job failed
  • gmail_reconnect_required: Gmail connection needs re-authentication
  • gmail_watch_expiring: Gmail watch is about to expire
  • gmail_watch_renew_failed: Failed to renew Gmail watch
  • gmail_sync_error: Error during Gmail synchronization

​
user_notification_preferences

Stores user preferences for each notification type.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
notification_type_id
UUID
required
References notification_types(id) with CASCADE delete
​
is_enabled
BOOLEAN
default:"true"
required
Whether user wants to receive this notification type
​
is_muted
BOOLEAN
default:"false"
required
Whether notifications are temporarily muted
​
muted_until
TIMESTAMPTZ
When to unmute notifications (if muted)
​
created_at
TIMESTAMPTZ
default:"now()"
required
Timestamp when preference was created
​
updated_at
TIMESTAMPTZ
default:"now()"
required
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_user_notification_preferences_lookup on (user_id, notification_type_id)
  • idx_user_notification_preferences_state on (user_id, is_enabled, is_muted)
Constraints:
  • user_notification_preferences_unique: UNIQUE on (user_id, notification_type_id)

​
notifications

Stores individual notification messages sent to users.
​
id
UUID
required
Primary key, auto-generated
​
user_id
UUID
required
References users(id) with CASCADE delete
​
notification_type_id
UUID
required
References notification_types(id) with RESTRICT delete
​
title_i18n_key
TEXT
required
Internationalization key for notification title
​
body_i18n_key
TEXT
required
Internationalization key for notification body
​
i18n_params
JSONB
default:"{}"
required
Parameters for internationalization template
​
metadata
JSONB
default:"{}"
required
Additional notification metadata
​
read_at
TIMESTAMPTZ
Timestamp when notification was read (NULL if unread)
​
is_archived
BOOLEAN
default:"false"
required
Whether notification is archived
​
is_muted
BOOLEAN
default:"false"
required
Whether notification is muted
​
importance
notification_importance
default:"normal"
required
Importance level: ‘low’, ‘normal’, ‘high’, or ‘critical’
​
action_path
TEXT
App route/path for notification action
​
icon_key
TEXT
Icon identifier for notification
​
avatar_url
TEXT
URL for avatar image
​
dedupe_key
TEXT
Unique key for deduplication
​
created_at
TIMESTAMPTZ
default:"now()"
required
Timestamp when notification was created
​
updated_at
TIMESTAMPTZ
default:"now()"
required
Timestamp of last update (auto-updated by trigger)
Indexes:
  • idx_notifications_user_created_at on (user_id, created_at DESC)
  • idx_notifications_user_read_at on (user_id, read_at)
  • idx_notifications_user_archived on (user_id, is_archived)
  • idx_notifications_user_muted on (user_id, is_muted)
  • idx_notifications_user_importance on (user_id, importance)
  • idx_notifications_dedupe on (user_id, notification_type_id, dedupe_key) WHERE dedupe_key IS NOT NULL
Realtime: Enabled via supabase_realtime publication

​
Enums

​
seed_status

Status values for seed jobs. 
CREATE TYPE seed_status AS ENUM (
  'pending',
  'processing', 
  'completed',
  'failed'
);

​
notification_importance

Importance levels for notifications. 
CREATE TYPE notification_importance AS ENUM (
  'low',
  'normal',
  'high',
  'critical'
);

​
Extensions

​
pgcrypto

Used for encrypting OAuth tokens. 
CREATE EXTENSION IF NOT EXISTS pgcrypto;

​
pg_cron

Used for scheduling recurring jobs (e.g., Gmail watch renewal). 
CREATE EXTENSION IF NOT EXISTS pg_cron;

Build docs developers (and LLMs) love

Get started for freeTalk to us