Database Schema

Documentation of the WhiskyPay database structure

WhiskyPay Database Schema

WhiskyPay uses Supabase (PostgreSQL) for data storage. This page documents the database schema, explaining each table’s purpose and structure.

Tables Overview

The database consists of five primary tables:

1. users - Merchant information
2. tiers - Pricing tiers for each merchant
3. buyers - Customer purchase records
4. sessions - Payment session data
5. signatures - Transaction signature verification

Table Structures

users

Stores information about merchants who use the WhiskyPay system.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  public_key TEXT NOT NULL,
  saas_name TEXT NOT NULL,
  logo_url TEXT NOT NULL,
  address TEXT NOT NULL,
  call_back TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE INDEX idx_users_public_key ON users(public_key);

Column	Type	Description
id	UUID	Primary key, unique identifier for the merchant
created_at	TIMESTAMP	When the merchant was created
updated_at	TIMESTAMP	When the merchant was last updated
public_key	TEXT	Merchant’s Solana wallet public key
saas_name	TEXT	Name of the merchant’s SaaS/business
logo_url	TEXT	URL to the merchant’s logo
address	TEXT	Merchant’s Solana wallet address for receiving payments
call_back	TEXT	Webhook URL to notify after successful payments
email	TEXT	Merchant’s email address

tiers

Stores pricing information for each merchant’s subscription tiers or products.

CREATE TABLE tiers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  saas_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tier_name TEXT NOT NULL,
  price NUMERIC NOT NULL
);

CREATE INDEX idx_tiers_saas_id ON tiers(saas_id);

Column	Type	Description
id	UUID	Primary key
created_at	TIMESTAMP	When the tier was created
updated_at	TIMESTAMP	When the tier was last updated
saas_id	UUID	Foreign key to the users table
tier_name	TEXT	Name of the tier/plan (e.g., “Basic”, “Premium”)
price	NUMERIC	Price of the tier in USD

buyers

Records of customers who have made purchases.

CREATE TABLE buyers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  saas_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  plan TEXT NOT NULL,
  price NUMERIC NOT NULL,
  time TIMESTAMP WITH TIME ZONE DEFAULT now()
);

CREATE INDEX idx_buyers_saas_id ON buyers(saas_id);

Column	Type	Description
id	UUID	Primary key
created_at	TIMESTAMP	When the record was created
updated_at	TIMESTAMP	When the record was last updated
saas_id	UUID	Foreign key to the users table
email	TEXT	Customer’s email address
plan	TEXT	Plan/tier purchased
price	NUMERIC	Amount paid in USD
time	TIMESTAMP	When the purchase occurred

sessions

Stores payment session information.

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  saas_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  saas_name TEXT NOT NULL,
  time TIMESTAMP WITH TIME ZONE DEFAULT now(),
  email TEXT NOT NULL,
  address TEXT NOT NULL,
  logo_url TEXT NOT NULL,
  plan TEXT NOT NULL,
  price NUMERIC NOT NULL
);

CREATE INDEX idx_sessions_saas_id ON sessions(saas_id);

Column	Type	Description
id	UUID	Primary key, session identifier
created_at	TIMESTAMP	When the session was created
saas_id	UUID	Foreign key to the users table
saas_name	TEXT	Name of the merchant
time	TIMESTAMP	Session creation time
email	TEXT	Customer’s email address
address	TEXT	Merchant’s payment receiving address
logo_url	TEXT	URL to the merchant’s logo
plan	TEXT	Plan/tier being purchased
price	NUMERIC	Price in USD

signatures

Tracks transaction signatures to prevent double-spending and verify payments.

CREATE TABLE signatures (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  signature TEXT NOT NULL UNIQUE
);

CREATE INDEX idx_signatures_signature ON signatures(signature);

Column	Type	Description
id	UUID	Primary key
created_at	TIMESTAMP	When the signature was recorded
signature	TEXT	Solana transaction signature

Relationships

• A user (merchant) can have multiple tiers
• A user can have multiple buyers
• A user can create multiple sessions
• Each session is associated with one user

Migration Scripts

When migrating from other databases (such as MongoDB), you can use the following sample migration script:

// Sample migration pseudocode
async function migrateToSupabase() {
  // Connect to MongoDB
  const mongoClient = await connectToMongo();
  
  // Connect to Supabase
  const supabase = createClient(supabaseUrl, supabaseKey);
  
  // Fetch merchants from MongoDB
  const merchants = await mongoClient.collection('merchants').find().toArray();
  
  // Map and insert to Supabase
  for (const merchant of merchants) {
    // Insert merchant
    const { data: user } = await supabase
      .from('users')
      .insert({
        public_key: merchant.publicKey,
        saas_name: merchant.saasName,
        logo_url: merchant.logoUrl,
        address: merchant.address,
        call_back: merchant.callBack,
        email: merchant.email
      })
      .select();
      
    // Insert tiers
    for (const tier of merchant.tiers) {
      await supabase
        .from('tiers')
        .insert({
          saas_id: user[0].id,
          tier_name: tier.name,
          price: tier.price
        });
    }
    
    // ...similarly migrate other collections
  }
}

Database Setup

To create these tables in Supabase, you can use the SQL editor in the Supabase dashboard or run the schema through other PostgreSQL tools.

For a complete setup script, refer to the SQL files in the GitHub repository or check the migration-saas-table.sql and new-structure.sql files in the project root.