> ## Documentation Index
> Fetch the complete documentation index at: https://whiskypeak.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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:

```typescript
// 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](https://github.com/whiskypay/payment-gateway) or check the `migration-saas-table.sql` and `new-structure.sql` files in the project root.