2837480e17
- 6 tables: structures, ranks, nodes, commissions, bonuses, rank_history - 5 enums: structure_type, node_status, commission_type, commission_status, bonus_type - LTREE extension for hierarchical path queries - 24 RLS policies for multi-tenancy - GIST index for LTREE path column Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
88 lines
4.9 KiB
SQL
88 lines
4.9 KiB
SQL
-- =============================================
|
|
-- Indexes: mlm
|
|
-- Module: SAAS-021 MLM (Multi-Level Marketing)
|
|
-- =============================================
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- structures indexes
|
|
-- ─────────────────────────────────────────────
|
|
CREATE INDEX idx_structures_tenant ON mlm.structures (tenant_id);
|
|
CREATE INDEX idx_structures_active ON mlm.structures (tenant_id, is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_structures_type ON mlm.structures (tenant_id, type);
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- ranks indexes
|
|
-- ─────────────────────────────────────────────
|
|
CREATE INDEX idx_ranks_tenant ON mlm.ranks (tenant_id);
|
|
CREATE INDEX idx_ranks_structure ON mlm.ranks (structure_id);
|
|
CREATE INDEX idx_ranks_level ON mlm.ranks (structure_id, level);
|
|
CREATE INDEX idx_ranks_active ON mlm.ranks (structure_id, is_active) WHERE is_active = true;
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- nodes indexes (CRITICAL for performance)
|
|
-- ─────────────────────────────────────────────
|
|
-- Tenant isolation
|
|
CREATE INDEX idx_nodes_tenant ON mlm.nodes (tenant_id);
|
|
|
|
-- Structure lookup
|
|
CREATE INDEX idx_nodes_structure ON mlm.nodes (structure_id);
|
|
|
|
-- User lookup
|
|
CREATE INDEX idx_nodes_user ON mlm.nodes (user_id);
|
|
|
|
-- Hierarchy lookups
|
|
CREATE INDEX idx_nodes_parent ON mlm.nodes (parent_id);
|
|
CREATE INDEX idx_nodes_sponsor ON mlm.nodes (sponsor_id);
|
|
|
|
-- LTREE path index (for ancestor/descendant queries)
|
|
CREATE INDEX idx_nodes_path ON mlm.nodes USING GIST (path);
|
|
|
|
-- Depth-based queries
|
|
CREATE INDEX idx_nodes_depth ON mlm.nodes (structure_id, depth);
|
|
|
|
-- Status filtering
|
|
CREATE INDEX idx_nodes_status ON mlm.nodes (structure_id, status);
|
|
CREATE INDEX idx_nodes_active ON mlm.nodes (structure_id) WHERE status = 'active';
|
|
|
|
-- Rank lookups
|
|
CREATE INDEX idx_nodes_rank ON mlm.nodes (rank_id);
|
|
|
|
-- Invite code lookup
|
|
CREATE INDEX idx_nodes_invite_code ON mlm.nodes (invite_code) WHERE invite_code IS NOT NULL;
|
|
|
|
-- Combined for common queries
|
|
CREATE INDEX idx_nodes_structure_parent ON mlm.nodes (structure_id, parent_id);
|
|
CREATE INDEX idx_nodes_structure_user ON mlm.nodes (structure_id, user_id);
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- commissions indexes
|
|
-- ─────────────────────────────────────────────
|
|
CREATE INDEX idx_commissions_tenant ON mlm.commissions (tenant_id);
|
|
CREATE INDEX idx_commissions_node ON mlm.commissions (node_id);
|
|
CREATE INDEX idx_commissions_source_node ON mlm.commissions (source_node_id);
|
|
CREATE INDEX idx_commissions_type ON mlm.commissions (tenant_id, type);
|
|
CREATE INDEX idx_commissions_level ON mlm.commissions (tenant_id, level);
|
|
CREATE INDEX idx_commissions_status ON mlm.commissions (tenant_id, status);
|
|
CREATE INDEX idx_commissions_period ON mlm.commissions (period_id);
|
|
CREATE INDEX idx_commissions_pending ON mlm.commissions (tenant_id) WHERE status = 'pending';
|
|
CREATE INDEX idx_commissions_created ON mlm.commissions (tenant_id, created_at DESC);
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- bonuses indexes
|
|
-- ─────────────────────────────────────────────
|
|
CREATE INDEX idx_bonuses_tenant ON mlm.bonuses (tenant_id);
|
|
CREATE INDEX idx_bonuses_node ON mlm.bonuses (node_id);
|
|
CREATE INDEX idx_bonuses_rank ON mlm.bonuses (rank_id);
|
|
CREATE INDEX idx_bonuses_type ON mlm.bonuses (tenant_id, type);
|
|
CREATE INDEX idx_bonuses_status ON mlm.bonuses (tenant_id, status);
|
|
CREATE INDEX idx_bonuses_period ON mlm.bonuses (period_id);
|
|
CREATE INDEX idx_bonuses_pending ON mlm.bonuses (tenant_id) WHERE status = 'pending';
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- rank_history indexes
|
|
-- ─────────────────────────────────────────────
|
|
CREATE INDEX idx_rank_history_tenant ON mlm.rank_history (tenant_id);
|
|
CREATE INDEX idx_rank_history_node ON mlm.rank_history (node_id);
|
|
CREATE INDEX idx_rank_history_rank ON mlm.rank_history (rank_id);
|
|
CREATE INDEX idx_rank_history_achieved ON mlm.rank_history (node_id, achieved_at DESC);
|