Database Schema - All Tables and Fields
1. support_tickets
Purpose: Customer support ticket management
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| user_id | UUID | User who created the ticket | ||
| status | TEXT | ✓ | "open" | Ticket status |
| priority | TEXT | ✓ | "medium" | Ticket priority level |
| subject | TEXT | ✓ | Ticket subject line | |
| description | TEXT | ✓ | Detailed ticket description | |
| assigned_to | UUID | FK to admin_users.id | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp | |
| updated_at | TIMESTAMP WITH TIME ZONE | now() | Last update timestamp | |
| attachments | JSONB | File attachments |
2. orders
Purpose: Currency exchange order tracking
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| user_id | UUID | User who placed the order | ||
| from_currency | TEXT | ✓ | Source currency | |
| to_currency | TEXT | ✓ | Target currency | |
| from_amount | NUMERIC | ✓ | Amount to exchange from | |
| to_amount | NUMERIC | ✓ | Amount to exchange to | |
| status | TEXT | ✓ | "pending" | Order status |
| transaction_hash | TEXT | Blockchain transaction hash | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp | |
| updated_at | TIMESTAMP WITH TIME ZONE | now() | Last update timestamp | |
| receiving_address | TEXT | Destination address |
3. rotating_addresses
Purpose: Cryptocurrency address management
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| currency | TEXT | ✓ | Cryptocurrency type | |
| address | TEXT | ✓ | Wallet address | |
| private_key | TEXT | ✓ | Private key (encrypted) | |
| order_id | UUID | FK to orders.id | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp | |
| used_at | TIMESTAMP WITH TIME ZONE | When address was used |
4. admin_users
Purpose: Administrative user management
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| TEXT | ✓ | Admin email address | ||
| role | TEXT | ✓ | Admin role/permission level | |
| last_login | TIMESTAMP WITH TIME ZONE | Last login timestamp | ||
| two_factor_enabled | BOOLEAN | false | 2FA status | |
| login_attempts | INTEGER | 0 | Failed login count | |
| locked_until | TIMESTAMP WITH TIME ZONE | Account lock expiry | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp |
5. ticket_messages
Purpose: Support ticket conversation messages
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| ticket_id | UUID | FK to support_tickets.id | ||
| sender_id | UUID | Message sender ID | ||
| is_admin | BOOLEAN | false | Whether sender is admin | |
| message | TEXT | ✓ | Message content | |
| attachments | JSONB | File attachments | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp |
6. admin_audit_logs
Purpose: Administrative action logging
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | UUID | ✓ | gen_random_uuid() | Primary Key |
| admin_id | UUID | FK to admin_users.id | ||
| action | TEXT | ✓ | Action performed | |
| details | JSONB | ✓ | Action details | |
| ip_address | TEXT | Admin's IP address | ||
| user_agent | TEXT | Admin's user agent | ||
| created_at | TIMESTAMP WITH TIME ZONE | now() | Creation timestamp |
7. reserves
Purpose: Reserve amount tracking
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
| id | TEXT | ✓ | Primary Key | |
| amount | NUMERIC | ✓ | Reserve amount | |
| updated_at | TIMESTAMP WITH TIME ZONE | now() | Last update timestamp |
Available RPC Functions
is_valid_admin
- Purpose: Validate admin credentials
- Parameters: Object (structure not specified)
- Methods: GET, POST
update_reserves
- Purpose: Update reserve amounts
- Parameters: Object (structure not specified)
- Methods: GET, POST
needs_rotating_address
- Purpose: Check if a rotating address is needed
- Parameters:
currency(TEXT, required)amount(NUMERIC, required)
- Methods: GET, POST
Foreign Key Relationships
support_tickets.assigned_to→admin_users.idrotating_addresses.order_id→orders.idticket_messages.ticket_id→support_tickets.idadmin_audit_logs.admin_id→admin_users.id