264 lines
7.4 KiB
Markdown
264 lines
7.4 KiB
Markdown
# Database Schema
|
|
|
|
## Overview
|
|
|
|
The application uses **Supabase** as the database provider, which is built on PostgreSQL. This provides:
|
|
- Relational database capabilities
|
|
- Built-in authentication
|
|
- Row-level security
|
|
- Real-time capabilities
|
|
- Storage options for larger objects
|
|
|
|
## Tables and Relationships
|
|
|
|
### Users
|
|
Stores user account information and preferences.
|
|
|
|
```sql
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email TEXT UNIQUE NOT NULL,
|
|
full_name TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
credits INTEGER DEFAULT 5,
|
|
is_premium BOOLEAN DEFAULT FALSE,
|
|
preferences JSONB DEFAULT '{}'::JSONB,
|
|
last_login TIMESTAMP WITH TIME ZONE
|
|
);
|
|
```
|
|
|
|
### ApiKeys
|
|
Stores encrypted API keys for LLM services.
|
|
|
|
```sql
|
|
CREATE TABLE api_keys (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL,
|
|
key_encrypted TEXT NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
last_used TIMESTAMP WITH TIME ZONE,
|
|
UNIQUE(user_id, provider)
|
|
);
|
|
```
|
|
|
|
### Indicators
|
|
Stores the library of available technical indicators.
|
|
|
|
```sql
|
|
CREATE TABLE indicators (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT NOT NULL,
|
|
type TEXT NOT NULL, -- trend, entry, exit, stop_loss
|
|
parameters JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
defaults JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID REFERENCES users(id),
|
|
is_custom BOOLEAN DEFAULT FALSE,
|
|
is_public BOOLEAN DEFAULT TRUE,
|
|
accuracy_rating DECIMAL(3,2)
|
|
);
|
|
```
|
|
|
|
### CustomIndicators
|
|
Stores user-created custom indicators (premium feature).
|
|
|
|
```sql
|
|
CREATE TABLE custom_indicators (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT NOT NULL,
|
|
type TEXT NOT NULL, -- trend, entry, exit, stop_loss
|
|
parameters JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
pine_script TEXT NOT NULL,
|
|
is_public BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
### Strategies
|
|
Stores created trading strategies.
|
|
|
|
```sql
|
|
CREATE TABLE strategies (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
is_ai_generated BOOLEAN DEFAULT FALSE,
|
|
is_public BOOLEAN DEFAULT FALSE,
|
|
market_type TEXT, -- futures, stocks, crypto, forex
|
|
timeframe TEXT, -- 1m, 5m, 15m, 1h, 4h, 1d
|
|
config JSONB NOT NULL
|
|
);
|
|
```
|
|
|
|
### StrategyVersions
|
|
Stores versions of strategies for version control.
|
|
|
|
```sql
|
|
CREATE TABLE strategy_versions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
strategy_id UUID REFERENCES strategies(id) ON DELETE CASCADE,
|
|
version_number INTEGER NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
config JSONB NOT NULL,
|
|
notes TEXT,
|
|
UNIQUE(strategy_id, version_number)
|
|
);
|
|
```
|
|
|
|
### StrategyComponents
|
|
Links strategies to their component indicators with configuration.
|
|
|
|
```sql
|
|
CREATE TABLE strategy_components (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
strategy_id UUID REFERENCES strategies(id) ON DELETE CASCADE,
|
|
indicator_id UUID, -- Can reference either indicators or custom_indicators
|
|
is_custom BOOLEAN DEFAULT FALSE,
|
|
component_type TEXT NOT NULL, -- trend, entry, exit, stop_loss
|
|
parameters JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
position INTEGER NOT NULL, -- Order within the component type
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
### Subscriptions
|
|
Stores user subscription information.
|
|
|
|
```sql
|
|
CREATE TABLE subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL,
|
|
provider TEXT NOT NULL, -- stripe, paypal
|
|
provider_subscription_id TEXT,
|
|
plan_type TEXT NOT NULL,
|
|
current_period_start TIMESTAMP WITH TIME ZONE,
|
|
current_period_end TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
### Coupons
|
|
Stores discount coupon codes.
|
|
|
|
```sql
|
|
CREATE TABLE coupons (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
code TEXT UNIQUE NOT NULL,
|
|
discount_percent INTEGER NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
max_uses INTEGER,
|
|
current_uses INTEGER DEFAULT 0,
|
|
expiration_date TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
### UserCoupons
|
|
Tracks coupon usage by users.
|
|
|
|
```sql
|
|
CREATE TABLE user_coupons (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
coupon_id UUID REFERENCES coupons(id) ON DELETE CASCADE,
|
|
used_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, coupon_id)
|
|
);
|
|
```
|
|
|
|
## Indexes
|
|
|
|
```sql
|
|
-- For faster user lookups by email
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
|
|
-- For filtering indicators by type and category
|
|
CREATE INDEX idx_indicators_type_category ON indicators(type, category);
|
|
|
|
-- For retrieving user strategies
|
|
CREATE INDEX idx_strategies_user_id ON strategies(user_id);
|
|
|
|
-- For retrieving strategy versions
|
|
CREATE INDEX idx_strategy_versions_strategy_id ON strategy_versions(strategy_id);
|
|
|
|
-- For retrieving strategy components
|
|
CREATE INDEX idx_strategy_components_strategy_id ON strategy_components(strategy_id);
|
|
|
|
-- For coupon code lookups
|
|
CREATE INDEX idx_coupons_code ON coupons(code);
|
|
```
|
|
|
|
## Row Level Security (RLS)
|
|
|
|
Supabase provides row-level security to ensure users can only access their own data:
|
|
|
|
```sql
|
|
-- Users table
|
|
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY user_self_access ON users
|
|
FOR ALL USING (auth.uid() = id);
|
|
|
|
-- API Keys table
|
|
ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY api_keys_self_access ON api_keys
|
|
FOR ALL USING (auth.uid() = user_id);
|
|
|
|
-- Strategies table
|
|
ALTER TABLE strategies ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY strategies_self_access ON strategies
|
|
FOR ALL USING (auth.uid() = user_id OR is_public = TRUE);
|
|
|
|
-- Custom indicators table
|
|
ALTER TABLE custom_indicators ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY custom_indicators_self_access ON custom_indicators
|
|
FOR ALL USING (auth.uid() = user_id OR is_public = TRUE);
|
|
```
|
|
|
|
## Data Encryption
|
|
|
|
Sensitive data like API keys will be encrypted using Supabase's pgcrypto extension:
|
|
|
|
```sql
|
|
-- Setup pgcrypto extension if not already enabled
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- Function to encrypt API keys
|
|
CREATE OR REPLACE FUNCTION encrypt_api_key(key_text TEXT, secret TEXT)
|
|
RETURNS TEXT AS $$
|
|
BEGIN
|
|
RETURN encode(encrypt(key_text::bytea, secret::bytea, 'aes-cbc/pad:pkcs')::bytea, 'base64');
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Function to decrypt API keys
|
|
CREATE OR REPLACE FUNCTION decrypt_api_key(encrypted_key TEXT, secret TEXT)
|
|
RETURNS TEXT AS $$
|
|
BEGIN
|
|
RETURN convert_from(decrypt(decode(encrypted_key, 'base64')::bytea, secret::bytea, 'aes-cbc/pad:pkcs')::bytea, 'UTF8');
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
```
|
|
|
|
## Migrations
|
|
|
|
Migrations will be managed using Supabase migrations, which provide:
|
|
- Version control for database schema
|
|
- Rollback capability
|
|
- Environment-specific configurations
|