# IPTV Panel - Database Schema Documentation ## Database SQLite database file: database/iptv_panel.sqlite Database connection file: database/db.js Database schema file: database/schema.sql --- # Tables ## supports Stores support team members. Used for assigning clients/services to support users. Fields: - id - name - email - is_active - created_at Example supports: - yasmin - hafsa --- ## mailers Stores mailers/affiliates. Mailers generate campaign clicks and receive commissions from conversions. Fields: - id - name - email - commission_rate - is_active - created_at Example mailers: - 29 = FERDAOUS ADAIBOUNI - 21 = IMAN BENHELIMA --- ## users Stores login users. Roles: - admin - support - mailer Fields: - id - username - password_hash - role - support_id - mailer_id - is_active - created_at Logic: - Admin has no support_id or mailer_id required. - Support user is linked to support_id. - Mailer user is linked to mailer_id. --- ## clients Stores client/contact information. A client is the customer profile, not the IPTV service itself. Fields: - id - email - phone - country - website - support_id - mailer_id - tracking_token - visitor_id - source_user_id - source_list_id - source_email_id - source_campaign_id - first_click_at - last_click_at - user_agent - ip_address - created_at - updated_at Websites: - king - trex - xtream --- ## services Stores IPTV access records. This table stores: - free tests - subscriptions - renewals A client can have multiple services. Example: Client - Service 1: test - Service 2: subscription from test - Service 3: renewal Fields: - id - client_id - service_type - subscription_source - previous_service_id - converted_from_service_id - panel_name - website - status - subscription_period - device_count - start_date - expiration_date - reminder_sent - followup_enabled - post_4_followup_mode - created_at - updated_at Service types: - test - subscription Subscription sources: - direct - from_test - renewal Statuses: - active - expired - cancelled Subscription periods: - 1_month - 3_months - 6_months - 1_year - 2_years - 3_years - 5_years Device count: - 1 - 2 - 3 Important logic: - A free test is always a separate service row. - A paid subscription created from a test is a new service row. - A renewal is a new service row linked to the previous service. --- ## payments Stores payment records. Fields: - id - client_id - service_id - amount - currency - payment_method - payment_status - payment_date - external_order_id - refund_date - notes - created_at - updated_at Payment statuses: - pending - completed - refunded --- ## charges Stores business expenses. Fields: - id - title - category - amount - currency - charge_type - charge_date - notes - created_at Categories: - mailer_payout - iptv_panel_credit - hosting_vps - domain - other Charge types: - monthly - yearly - one_time --- ## tracking_events Stores all tracking/funnel events. This is the main table for mailer attribution. Event types: - landing_click - free_trial_submit - buy_now_click - checkout_start - purchase_completed - refund Click classification fields: - is_unique - is_duplicate - is_bot - is_proxy - is_suspicious - is_valid - invalid_reason Invalid clicks are not deleted. They are stored and marked. --- ## click_audits Stores reasons why a click was marked valid, invalid, duplicate, bot, or suspicious. Example checks: - duplicate_check - bot_check - proxy_check - velocity_check --- ## conversions Stores sales/conversions attributed to mailers. Conversion statuses: - pending - completed - refunded Important: - Completed conversion creates commission. - Refunded conversion cancels commission. --- ## commissions Stores mailer commission records. Commission statuses: - pending - approved - paid - cancelled --- ## reminders Stores reminder/follow-up records. Reminder types: - test_expiration - subscription_renewal --- ## email_logs Stores sent/failed email logs. --- ## client_notes Stores notes/history for clients. --- ## settings Stores panel settings.