rckrdmrd
a7cca885f0
Changes include: - Updated architecture documentation - Enhanced module definitions (OQI-001 to OQI-008) - ML integration documentation updates - Trading strategies documentation - Orchestration and inventory updates - Docker configuration updates 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
28 KiB
28 KiB
| id | title | type | status | priority | epic | project | version | dates | tags | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ET-MKT-001 | Especificacion de Base de Datos - Marketplace | technical-spec | Draft | High | OQI-009 | trading-platform | 1.0.0 |
|
|
ET-MKT-001: Especificacion de Base de Datos
Resumen
Este documento define el schema de base de datos para el modulo de Marketplace (OQI-009), incluyendo tablas para productos, compras, suscripciones, asesoria y visualizacion premium.
Schema
Schema Name: marketplace
CREATE SCHEMA IF NOT EXISTS marketplace;
Tablas
1. product_categories
Categorias de productos del marketplace.
CREATE TABLE marketplace.product_categories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
icon VARCHAR(50),
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_product_categories_slug ON marketplace.product_categories(slug);
CREATE INDEX idx_product_categories_active ON marketplace.product_categories(is_active);
-- Datos iniciales
INSERT INTO marketplace.product_categories (name, slug, description, icon, sort_order) VALUES
('Senales', 'signals', 'Paquetes de senales ML premium', 'signal', 1),
('Asesoria', 'advisory', 'Sesiones de consultoria 1:1', 'users', 2),
('Visualizacion', 'visualization', 'Herramientas graficas avanzadas', 'chart-bar', 3),
('Educacion', 'education', 'Cursos y materiales educativos', 'academic-cap', 4);
2. products
Catalogo de productos del marketplace.
CREATE TYPE marketplace.product_type AS ENUM (
'signal_pack',
'advisory_session',
'visualization_addon',
'course',
'ebook'
);
CREATE TYPE marketplace.billing_type AS ENUM (
'one_time',
'subscription'
);
CREATE TABLE marketplace.products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT,
short_description VARCHAR(500),
type marketplace.product_type NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
currency VARCHAR(3) DEFAULT 'USD',
billing_type marketplace.billing_type DEFAULT 'one_time',
subscription_interval VARCHAR(20), -- 'monthly', 'yearly'
category_id UUID REFERENCES marketplace.product_categories(id),
metadata JSONB DEFAULT '{}',
is_active BOOLEAN DEFAULT true,
is_featured BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_products_slug ON marketplace.products(slug);
CREATE INDEX idx_products_type ON marketplace.products(type);
CREATE INDEX idx_products_category ON marketplace.products(category_id);
CREATE INDEX idx_products_active ON marketplace.products(is_active);
CREATE INDEX idx_products_featured ON marketplace.products(is_featured) WHERE is_featured = true;
CREATE INDEX idx_products_price ON marketplace.products(price);
-- Busqueda full-text
CREATE INDEX idx_products_search ON marketplace.products
USING gin(to_tsvector('spanish', coalesce(name, '') || ' ' || coalesce(description, '')));
-- Productos iniciales
INSERT INTO marketplace.products (name, slug, type, price, billing_type, category_id, metadata, short_description) VALUES
('Basic Signal Pack', 'basic-signal-pack', 'signal_pack', 9.00, 'one_time',
(SELECT id FROM marketplace.product_categories WHERE slug = 'signals'),
'{"credits": 50, "validity_days": 30, "min_confidence": 80}',
'50 senales ML de alta confianza'),
('Pro Signal Pack', 'pro-signal-pack', 'signal_pack', 29.00, 'one_time',
(SELECT id FROM marketplace.product_categories WHERE slug = 'signals'),
'{"credits": 200, "validity_days": 60, "min_confidence": 80}',
'200 senales ML de alta confianza'),
('Unlimited Signals', 'unlimited-signals', 'signal_pack', 49.00, 'subscription',
(SELECT id FROM marketplace.product_categories WHERE slug = 'signals'),
'{"credits": -1, "min_confidence": 75}',
'Senales ML ilimitadas'),
('Asesoria 30 min', 'advisory-30', 'advisory_session', 49.00, 'one_time',
(SELECT id FROM marketplace.product_categories WHERE slug = 'advisory'),
'{"duration_minutes": 30}',
'Sesion de 30 minutos con asesor certificado'),
('Asesoria 60 min', 'advisory-60', 'advisory_session', 89.00, 'one_time',
(SELECT id FROM marketplace.product_categories WHERE slug = 'advisory'),
'{"duration_minutes": 60}',
'Sesion de 60 minutos + plan de accion'),
('Asesoria 90 min', 'advisory-90', 'advisory_session', 119.00, 'one_time',
(SELECT id FROM marketplace.product_categories WHERE slug = 'advisory'),
'{"duration_minutes": 90}',
'Sesion de 90 minutos + seguimiento'),
('Visualizacion Premium', 'visualization-premium', 'visualization_addon', 19.00, 'subscription',
(SELECT id FROM marketplace.product_categories WHERE slug = 'visualization'),
'{"features": ["ml_indicators", "unlimited_backtest", "unlimited_alerts", "multi_chart"]}',
'Indicadores ML exclusivos y herramientas avanzadas');
3. purchases
Registro de compras realizadas.
CREATE TYPE marketplace.purchase_status AS ENUM (
'pending',
'completed',
'failed',
'refunded',
'partially_refunded'
);
CREATE TABLE marketplace.purchases (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
product_id UUID NOT NULL REFERENCES marketplace.products(id),
quantity INTEGER DEFAULT 1,
unit_price DECIMAL(10, 2) NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
currency VARCHAR(3) DEFAULT 'USD',
status marketplace.purchase_status DEFAULT 'pending',
payment_id VARCHAR(255), -- ID de transaccion externa (Stripe)
payment_method VARCHAR(50),
metadata JSONB DEFAULT '{}',
completed_at TIMESTAMP WITH TIME ZONE,
refunded_at TIMESTAMP WITH TIME ZONE,
refund_amount DECIMAL(10, 2),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_purchases_user ON marketplace.purchases(user_id);
CREATE INDEX idx_purchases_product ON marketplace.purchases(product_id);
CREATE INDEX idx_purchases_status ON marketplace.purchases(status);
CREATE INDEX idx_purchases_payment ON marketplace.purchases(payment_id);
CREATE INDEX idx_purchases_created ON marketplace.purchases(created_at DESC);
4. subscriptions
Suscripciones activas de usuarios.
CREATE TYPE marketplace.subscription_status AS ENUM (
'active',
'canceled',
'past_due',
'paused',
'expired'
);
CREATE TABLE marketplace.subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
product_id UUID NOT NULL REFERENCES marketplace.products(id),
purchase_id UUID REFERENCES marketplace.purchases(id),
status marketplace.subscription_status DEFAULT 'active',
current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
cancel_at_period_end BOOLEAN DEFAULT false,
canceled_at TIMESTAMP WITH TIME ZONE,
cancellation_reason TEXT,
stripe_subscription_id VARCHAR(255),
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
-- Solo una suscripcion activa por producto por usuario
CONSTRAINT unique_active_subscription UNIQUE (user_id, product_id)
);
-- Indices
CREATE INDEX idx_subscriptions_user ON marketplace.subscriptions(user_id);
CREATE INDEX idx_subscriptions_product ON marketplace.subscriptions(product_id);
CREATE INDEX idx_subscriptions_status ON marketplace.subscriptions(status);
CREATE INDEX idx_subscriptions_period_end ON marketplace.subscriptions(current_period_end);
CREATE INDEX idx_subscriptions_stripe ON marketplace.subscriptions(stripe_subscription_id);
5. signal_credits
Creditos de senales de usuarios.
CREATE TABLE marketplace.signal_credits (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
product_id UUID NOT NULL REFERENCES marketplace.products(id),
purchase_id UUID REFERENCES marketplace.purchases(id),
subscription_id UUID REFERENCES marketplace.subscriptions(id),
initial_amount INTEGER NOT NULL,
remaining_amount INTEGER NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE,
is_unlimited BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_signal_credits_user ON marketplace.signal_credits(user_id);
CREATE INDEX idx_signal_credits_remaining ON marketplace.signal_credits(remaining_amount)
WHERE remaining_amount > 0;
CREATE INDEX idx_signal_credits_expires ON marketplace.signal_credits(expires_at);
6. signal_deliveries
Registro de senales entregadas a usuarios.
CREATE TYPE marketplace.delivery_channel AS ENUM (
'push',
'email',
'both',
'in_app'
);
CREATE TABLE marketplace.signal_deliveries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
signal_id UUID NOT NULL, -- FK a ml_signals
credit_id UUID REFERENCES marketplace.signal_credits(id),
delivery_channel marketplace.delivery_channel NOT NULL,
delivered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
read_at TIMESTAMP WITH TIME ZONE,
metadata JSONB DEFAULT '{}'
);
-- Indices
CREATE INDEX idx_signal_deliveries_user ON marketplace.signal_deliveries(user_id);
CREATE INDEX idx_signal_deliveries_signal ON marketplace.signal_deliveries(signal_id);
CREATE INDEX idx_signal_deliveries_date ON marketplace.signal_deliveries(delivered_at DESC);
7. advisors
Asesores financieros registrados.
CREATE TABLE marketplace.advisors (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) UNIQUE,
display_name VARCHAR(255) NOT NULL,
title VARCHAR(100),
bio TEXT,
short_bio VARCHAR(500),
specialties JSONB DEFAULT '[]',
experience_years INTEGER DEFAULT 0,
hourly_rate DECIMAL(10, 2),
languages JSONB DEFAULT '["es"]',
cal_username VARCHAR(100),
cal_event_type_id INTEGER,
profile_image_url VARCHAR(500),
rating DECIMAL(3, 2) DEFAULT 0.00,
review_count INTEGER DEFAULT 0,
completed_sessions INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT true,
is_verified BOOLEAN DEFAULT false,
verified_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_advisors_user ON marketplace.advisors(user_id);
CREATE INDEX idx_advisors_active ON marketplace.advisors(is_active);
CREATE INDEX idx_advisors_rating ON marketplace.advisors(rating DESC);
CREATE INDEX idx_advisors_specialties ON marketplace.advisors USING gin(specialties);
8. advisory_sessions
Sesiones de asesoria agendadas.
CREATE TYPE marketplace.session_status AS ENUM (
'scheduled',
'in_progress',
'completed',
'cancelled',
'no_show_client',
'no_show_advisor'
);
CREATE TABLE marketplace.advisory_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
advisor_id UUID NOT NULL REFERENCES marketplace.advisors(id),
product_id UUID NOT NULL REFERENCES marketplace.products(id),
purchase_id UUID REFERENCES marketplace.purchases(id),
duration_minutes INTEGER NOT NULL,
scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
ended_at TIMESTAMP WITH TIME ZONE,
status marketplace.session_status DEFAULT 'scheduled',
cal_event_id VARCHAR(255),
cal_booking_uid VARCHAR(255),
daily_room_name VARCHAR(255),
daily_room_url VARCHAR(500),
recording_url VARCHAR(500),
cancelled_at TIMESTAMP WITH TIME ZONE,
cancellation_reason TEXT,
refund_amount DECIMAL(10, 2),
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_advisory_sessions_user ON marketplace.advisory_sessions(user_id);
CREATE INDEX idx_advisory_sessions_advisor ON marketplace.advisory_sessions(advisor_id);
CREATE INDEX idx_advisory_sessions_scheduled ON marketplace.advisory_sessions(scheduled_at);
CREATE INDEX idx_advisory_sessions_status ON marketplace.advisory_sessions(status);
CREATE INDEX idx_advisory_sessions_cal ON marketplace.advisory_sessions(cal_booking_uid);
9. session_notes
Notas post-sesion de asesoria.
CREATE TABLE marketplace.session_notes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES marketplace.advisory_sessions(id) UNIQUE,
advisor_id UUID NOT NULL REFERENCES marketplace.advisors(id),
summary TEXT,
recommendations JSONB DEFAULT '[]',
resources JSONB DEFAULT '[]',
follow_up_actions JSONB DEFAULT '[]',
private_notes TEXT, -- Solo visible para el asesor
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_session_notes_session ON marketplace.session_notes(session_id);
CREATE INDEX idx_session_notes_advisor ON marketplace.session_notes(advisor_id);
10. advisor_reviews
Reviews de usuarios sobre asesores.
CREATE TABLE marketplace.advisor_reviews (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES marketplace.advisory_sessions(id) UNIQUE,
user_id UUID NOT NULL REFERENCES auth.users(id),
advisor_id UUID NOT NULL REFERENCES marketplace.advisors(id),
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
comment TEXT,
is_public BOOLEAN DEFAULT true,
advisor_response TEXT,
advisor_responded_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_advisor_reviews_advisor ON marketplace.advisor_reviews(advisor_id);
CREATE INDEX idx_advisor_reviews_user ON marketplace.advisor_reviews(user_id);
CREATE INDEX idx_advisor_reviews_rating ON marketplace.advisor_reviews(rating);
CREATE INDEX idx_advisor_reviews_public ON marketplace.advisor_reviews(is_public) WHERE is_public = true;
11. visualization_subscriptions
Vista materializada para suscripciones de visualizacion.
CREATE TABLE marketplace.visualization_subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) UNIQUE,
subscription_id UUID NOT NULL REFERENCES marketplace.subscriptions(id),
features JSONB DEFAULT '[]',
started_at TIMESTAMP WITH TIME ZONE NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_vis_sub_user ON marketplace.visualization_subscriptions(user_id);
CREATE INDEX idx_vis_sub_active ON marketplace.visualization_subscriptions(is_active);
CREATE INDEX idx_vis_sub_expires ON marketplace.visualization_subscriptions(expires_at);
12. user_chart_layouts
Layouts de charts guardados por usuarios.
CREATE TABLE marketplace.user_chart_layouts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
name VARCHAR(100) NOT NULL,
layout_config JSONB NOT NULL,
indicators JSONB DEFAULT '[]',
is_default BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_chart_layouts_user ON marketplace.user_chart_layouts(user_id);
CREATE INDEX idx_chart_layouts_default ON marketplace.user_chart_layouts(is_default)
WHERE is_default = true;
-- Trigger para asegurar solo un default por usuario
CREATE OR REPLACE FUNCTION marketplace.ensure_single_default_layout()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.is_default = true THEN
UPDATE marketplace.user_chart_layouts
SET is_default = false
WHERE user_id = NEW.user_id AND id != NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_single_default_layout
BEFORE INSERT OR UPDATE ON marketplace.user_chart_layouts
FOR EACH ROW
EXECUTE FUNCTION marketplace.ensure_single_default_layout();
13. indicator_alerts
Alertas de indicadores configuradas por usuarios.
CREATE TABLE marketplace.indicator_alerts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
indicator_id VARCHAR(50) NOT NULL,
symbol VARCHAR(20) NOT NULL,
conditions JSONB NOT NULL,
notification_channels JSONB DEFAULT '["push", "email"]',
is_active BOOLEAN DEFAULT true,
trigger_count INTEGER DEFAULT 0,
last_triggered_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_indicator_alerts_user ON marketplace.indicator_alerts(user_id);
CREATE INDEX idx_indicator_alerts_active ON marketplace.indicator_alerts(is_active);
CREATE INDEX idx_indicator_alerts_symbol ON marketplace.indicator_alerts(symbol);
14. backtest_results
Resultados de backtesting guardados.
CREATE TABLE marketplace.backtest_results (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
symbol VARCHAR(20) NOT NULL,
strategy_name VARCHAR(100),
strategy_config JSONB NOT NULL,
period_start TIMESTAMP WITH TIME ZONE NOT NULL,
period_end TIMESTAMP WITH TIME ZONE NOT NULL,
initial_capital DECIMAL(15, 2) NOT NULL,
final_capital DECIMAL(15, 2) NOT NULL,
total_return DECIMAL(10, 4),
total_trades INTEGER DEFAULT 0,
winning_trades INTEGER DEFAULT 0,
losing_trades INTEGER DEFAULT 0,
win_rate DECIMAL(5, 2),
profit_factor DECIMAL(5, 2),
max_drawdown DECIMAL(5, 2),
sharpe_ratio DECIMAL(5, 2),
trades JSONB DEFAULT '[]',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Indices
CREATE INDEX idx_backtest_user ON marketplace.backtest_results(user_id);
CREATE INDEX idx_backtest_symbol ON marketplace.backtest_results(symbol);
CREATE INDEX idx_backtest_created ON marketplace.backtest_results(created_at DESC);
Row Level Security (RLS)
-- Habilitar RLS en todas las tablas
ALTER TABLE marketplace.purchases ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.signal_credits ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.signal_deliveries ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.advisory_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.session_notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.advisor_reviews ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.user_chart_layouts ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.indicator_alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketplace.backtest_results ENABLE ROW LEVEL SECURITY;
-- Politicas para purchases
CREATE POLICY purchases_select_own ON marketplace.purchases
FOR SELECT USING (user_id = auth.uid());
CREATE POLICY purchases_insert_own ON marketplace.purchases
FOR INSERT WITH CHECK (user_id = auth.uid());
-- Politicas para subscriptions
CREATE POLICY subscriptions_select_own ON marketplace.subscriptions
FOR SELECT USING (user_id = auth.uid());
-- Politicas para signal_credits
CREATE POLICY signal_credits_select_own ON marketplace.signal_credits
FOR SELECT USING (user_id = auth.uid());
-- Politicas para advisory_sessions (usuarios ven las suyas, asesores ven donde son asesores)
CREATE POLICY advisory_sessions_select ON marketplace.advisory_sessions
FOR SELECT USING (
user_id = auth.uid() OR
advisor_id IN (SELECT id FROM marketplace.advisors WHERE user_id = auth.uid())
);
-- Politicas para session_notes
CREATE POLICY session_notes_select ON marketplace.session_notes
FOR SELECT USING (
session_id IN (SELECT id FROM marketplace.advisory_sessions WHERE user_id = auth.uid()) OR
advisor_id IN (SELECT id FROM marketplace.advisors WHERE user_id = auth.uid())
);
-- Politicas para layouts y alertas
CREATE POLICY chart_layouts_all_own ON marketplace.user_chart_layouts
FOR ALL USING (user_id = auth.uid());
CREATE POLICY indicator_alerts_all_own ON marketplace.indicator_alerts
FOR ALL USING (user_id = auth.uid());
CREATE POLICY backtest_results_all_own ON marketplace.backtest_results
FOR ALL USING (user_id = auth.uid());
Funciones y Triggers
Actualizar rating de asesor
CREATE OR REPLACE FUNCTION marketplace.update_advisor_rating()
RETURNS TRIGGER AS $$
BEGIN
UPDATE marketplace.advisors
SET
rating = (
SELECT COALESCE(AVG(rating), 0)
FROM marketplace.advisor_reviews
WHERE advisor_id = NEW.advisor_id
),
review_count = (
SELECT COUNT(*)
FROM marketplace.advisor_reviews
WHERE advisor_id = NEW.advisor_id
),
updated_at = NOW()
WHERE id = NEW.advisor_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_advisor_rating
AFTER INSERT OR UPDATE ON marketplace.advisor_reviews
FOR EACH ROW
EXECUTE FUNCTION marketplace.update_advisor_rating();
Decrementar creditos de senales
CREATE OR REPLACE FUNCTION marketplace.decrement_signal_credit(
p_user_id UUID,
p_signal_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
v_credit_id UUID;
v_remaining INTEGER;
BEGIN
-- Buscar credito con saldo disponible (primero los que expiran antes)
SELECT id, remaining_amount INTO v_credit_id, v_remaining
FROM marketplace.signal_credits
WHERE user_id = p_user_id
AND (remaining_amount > 0 OR is_unlimited = true)
AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY is_unlimited ASC, expires_at ASC NULLS LAST
LIMIT 1
FOR UPDATE;
IF v_credit_id IS NULL THEN
RETURN FALSE;
END IF;
-- Decrementar si no es unlimited
IF v_remaining > 0 THEN
UPDATE marketplace.signal_credits
SET remaining_amount = remaining_amount - 1,
updated_at = NOW()
WHERE id = v_credit_id;
END IF;
-- Registrar delivery
INSERT INTO marketplace.signal_deliveries (user_id, signal_id, credit_id, delivery_channel)
VALUES (p_user_id, p_signal_id, v_credit_id, 'both');
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
Diagrama ER
┌─────────────────────┐ ┌─────────────────────┐
│ product_categories │ │ products │
├─────────────────────┤ ├─────────────────────┤
│ id (PK) │◄────┤ category_id (FK) │
│ name │ │ id (PK) │
│ slug │ │ name, slug │
│ description │ │ type, price │
└─────────────────────┘ │ billing_type │
└──────────┬──────────┘
│
┌──────────────────────────┼──────────────────────────┐
│ │ │
▼ ▼ ▼
┌─────────────────────┐ ┌─────────────────────┐ ┌─────────────────────┐
│ purchases │ │ subscriptions │ │ signal_credits │
├─────────────────────┤ ├─────────────────────┤ ├─────────────────────┤
│ id (PK) │ │ id (PK) │ │ id (PK) │
│ user_id (FK) │ │ user_id (FK) │ │ user_id (FK) │
│ product_id (FK) │ │ product_id (FK) │ │ product_id (FK) │
│ status, total_price │ │ status, period_* │ │ remaining_amount │
└─────────────────────┘ └─────────────────────┘ └──────────┬──────────┘
│
▼
┌─────────────────────┐
│ signal_deliveries │
├─────────────────────┤
│ id (PK) │
│ user_id, signal_id │
│ credit_id (FK) │
└─────────────────────┘
┌─────────────────────┐ ┌─────────────────────┐ ┌─────────────────────┐
│ advisors │ │ advisory_sessions │ │ session_notes │
├─────────────────────┤ ├─────────────────────┤ ├─────────────────────┤
│ id (PK) │◄────┤ advisor_id (FK) │◄────┤ session_id (FK) │
│ user_id (FK) │ │ id (PK) │ │ id (PK) │
│ display_name │ │ user_id (FK) │ │ summary │
│ rating │ │ scheduled_at │ │ recommendations │
└─────────┬───────────┘ └─────────────────────┘ └─────────────────────┘
│
▼
┌─────────────────────┐
│ advisor_reviews │
├─────────────────────┤
│ id (PK) │
│ advisor_id (FK) │
│ session_id (FK) │
│ rating, comment │
└─────────────────────┘