13. Payment infrastructure

export type ProductType = 'one_time' | 'subscription'
export type Currency = 'USD' | 'CNY' | 'EUR' | 'JPY' | 'GBP'
export type SubscriptionInterval = 'day' | 'week' | 'month' | 'year'

export const products = sqliteTable('products', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text('name').notNull(),
  description: text('description'),
  type: text('type').$type<ProductType>().notNull(),
  price: real('price').notNull(),
  currency: text('currency').$type<Currency>().notNull().default('USD'),
  interval: text('interval').$type<SubscriptionInterval>(), // only for subscription
  tokenAmount: integer('tokenAmount'), // If the product provides tokens
  active: integer('active', { mode: 'boolean' }).notNull().default(true),
  createdAt: integer('created_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull()
})
```

**Field explanation:**

1. **name**: product name, required field
2. **description**: product description
3. **type**: product type, using the `ProductType` enumeration type, which can be a one-time purchase (`one_time`) or subscription (`subscription`)
4. **price**: Product price, stored as a floating point number
5. **currency**: Currency type, using the `Currency` enumeration type, the default is USD
6. **interval**: Subscription period, only valid for subscription type products, can be day (`day`), week (`week`), month (`month`) or year (`year`)
7. **tokenAmount**: The number of tokens provided by the product, used for products that provide tokens
8. **active**: Whether the product is activated, Boolean value, default is true
9. **createdAt**: product creation time, automatically set to the current timestamp
10. **updatedAt**: product update time, automatically set to the current timestamp

### 2. Orders table (orders)

The order table records all transaction information, including one-time purchases and initial transactions for subscriptions.

```typescript
export type OrderStatus = 'pending' | 'completed' | 'failed' | 'refunded'

export type PaymentMethod = 'credit_card' | 'paypal' | 'upgrade.chat' | 'other'

export const orders = sqliteTable('orders', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text('userId')
    .notNull()
    .references(() => users.id),
  productId: text('productId')
    .notNull()
    .references(() => products.id),
  amount: real('amount').notNull(),
  currency: text('currency').$type<Currency>().notNull(),
  status: text('status').$type<OrderStatus>().notNull().default('pending'),
  paymentMethod: text('paymentMethod').$type<PaymentMethod>(),
  paymentIntentId: text('paymentIntentId'), // Transaction ID of payment gateway
  metadata: text('metadata'), // Store additional information in JSON format
  createdAt: integer('created_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull()
})
```

**Field explanation:**

1. **userId**: The ID of the user who placed the order, the foreign key is associated with users
2. **productId**: purchased product ID, foreign key associated with the products table
3. **amount**: order amount, stored as a floating point number
4. **currency**: Currency type, using the `Currency` enumeration type
5. **status**: This is used to track the complete life cycle of the order, the order status, using the `OrderStatus` enumeration type, the default is `pending`, which can be pending (`pending`), completed (`completed`), failed (`failed`) or refunded (`refunded`)
6. **paymentMethod**: Payment method, using the `PaymentMethod` enumeration type, which can be credit card (`credit_card`), PayPal (`paypal`), etc.
7. **paymentIntentId**: Transaction ID of the external payment gateway, used for integration with payment service providers
8. **metadata**: Additional metadata, stored in JSON format, can be used to store business-specific information
9. **createdAt**: order creation time, automatically set to the current timestamp
10. **updatedAt**: order update time, automatically set to the current timestamp

### 3. Subscriptions

The subscription table records the user's active subscription information and tracks the status and cycle of the subscription.

```typescript
export type SubscriptionStatus = 'active' | 'canceled' | 'expired' | 'past_due'

export const subscriptions = sqliteTable('subscriptions', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text('userId')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  productId: text('productId')
    .notNull()
    .references(() => products.id),
  orderId: text('orderId').references(() => orders.id),
  status: text('status').$type<SubscriptionStatus>().notNull().default('active'),
  currentPeriodStart: integer('current_period_start', { mode: 'timestamp_ms' }).notNull(),
  currentPeriodEnd: integer('current_period_end', { mode: 'timestamp_ms' }).notNull(),
  cancelAtPeriodEnd: integer('cancel_at_period_end', { mode: 'boolean' }).default(false),
  subscriptionId: text('subscriptionId'), // Subscription ID of payment gateway
  metadata: text('metadata'), // Store additional information in JSON format
  createdAt: integer('created_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull()
})
```

**Field explanation:**

1. **userId**: The ID of the subscriber, and the foreign key is associated with the users table
2. **productId**: Subscribed product ID, foreign key associated with the products table
3. **orderId**: Create the initial order ID of this subscription, and associate the foreign key to the orders table
4. **status**: Subscription status, using the `SubscriptionStatus` enumeration type, the default is `active`, which can be active (`active`), canceled (`canceled`), expired (`expired`) or overdue (`past_due`)
5. **currentPeriodStart**: The start time of the current subscription period, timestamp format
6. **currentPeriodEnd**: The end time of the current subscription period, timestamp format
7. **cancelAtPeriodEnd**: Whether to cancel the subscription at the end of the current period, Boolean value, default is false
8. **subscriptionId**: Subscription ID of the external payment gateway, used for integration with payment service providers
9. **metadata**: Additional metadata, stored in JSON format
10. **createdAt**: Subscription creation time, automatically set to the current timestamp
11. **updatedAt**: Subscription update time, automatically set to the current timestamp

### 4. Transaction history table (transactions)

The transaction history table records all changes in token balance, including purchase, use, refund and other operations.

```typescript
export const transactions = sqliteTable('transactions', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text('userId')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  orderId: text('orderId').references(() => orders.id),
  type: text('type').$type<TransactionType>().notNull(),
  amount: integer('amount').notNull(), //Token quantity, which can be positive (increase) or negative (consumption)
  createdAt: integer('created_at', { mode: 'timestamp_ms' })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull()
})
```

**Field explanation:**

1. **userId**: The user ID associated with the transaction, the foreign key is associated with the users table, and cascade deletion is set.
2. **orderId**: associated order ID (if applicable), foreign key associated to the orders table
3. **type**: transaction type, using the `TransactionType` enumeration type, which can be purchase (`purchase`), use (`usage`), refund (`refund`), subscription renewal (`subscription_renewal`), gift (`gift`) or promotion (`promotion`)
4. **amount**: token quantity change, integer type, positive number indicates increase, negative number indicates consumption
5. **createdAt**: Transaction creation time, automatically set to the current timestamp

### Relationships and data flows between tables

Let’s sort out the data flow and business logic between all tables

#### 1. **User purchases product**:

- Create `orders` table record with status `pending`
- After the payment is successful, update the `orders` table record status to `completed`
- Create `transactions` table record, type is `purchase`, amount is positive number
- Update the `userUsage` table and add `totalTokens`

**Data flow**: