Database Documentation

The MyNATCA platform uses a dual-database architecture with Supabase (PostgreSQL) as the primary application database and MySQL as the source of truth for member data.

Database Architecture

Supabase (PostgreSQL) - Primary Database

Purpose: Main application database for all MyNATCA platform applications

Key Features:

Row Level Security (RLS) policies for data isolation
Real-time subscriptions for live updates
Edge functions for integrations
Automated backups and monitoring
REST and GraphQL APIs
Built-in authentication and authorization

Connection Details:

SUPABASE_URL=https://your-project-id.supabase.co
SUPABASE_KEY=your_service_role_key
SUPABASE_ANON_KEY=your_anonymous_key

Schema Organization

The platform uses a multi-schema architecture to separate concerns and support multiple environments:

Development Schemas:

dev - Development versions of shared tables (members, facilities, regions, positions)
discord_dev - Discord-specific tables for development (verification_requests, validation_logs, validation_summaries)

Production Schemas:

public - Production versions of shared tables (members, facilities, regions, positions)
discord - Discord-specific tables for production (verification_requests, validation_logs, validation_summaries)

Schema Configuration:

Critical: Both discord and discord_dev schemas must be exposed in Supabase Dashboard:

Navigate to Settings > API > Exposed schemas
Add discord and discord_dev to the list of exposed schemas
This allows the Discord bot to access verification tables directly

Historical Note: Previous versions stored all tables in the public schema. A cleanup migration removed old Discord-specific tables from public schema to improve organization.

MySQL - Source Database

Purpose: Legacy source database maintained by external systems

Key Features:

Read-only access for synchronization
Source of truth for member data
External system maintains data integrity
Scheduled sync operations transfer data to Supabase

Connection Details:

MYSQL_HOST=mysql.example.com
MYSQL_USER=mynatca_user
MYSQL_PASS=secure_password
MYSQL_DB=mynatca_members
MYSQL_PORT=3306

Core Tables

Members Table

Central table containing all NATCA member information.

CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  member_number TEXT UNIQUE NOT NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  facility_code TEXT,
  facility_name TEXT,
  region_code TEXT,
  region_name TEXT,
  member_type_id INTEGER,
  member_type TEXT,
  status TEXT DEFAULT 'active',
 
  -- Discord Integration
  discord_id TEXT,
  discord_username TEXT,
  discord_verified BOOLEAN DEFAULT FALSE,
 
  -- Auth0 Integration
  auth0_user_id TEXT,
  verified_at TIMESTAMPTZ,
  last_auth0_sync TIMESTAMPTZ,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_sync TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes for performance
CREATE INDEX idx_members_member_number ON members(member_number);
CREATE INDEX idx_members_discord_id ON members(discord_id);
CREATE INDEX idx_members_auth0_user_id ON members(auth0_user_id);
CREATE INDEX idx_members_facility_code ON members(facility_code);
CREATE INDEX idx_members_updated_at ON members(updated_at);

Positions Table

Member leadership positions and roles.

CREATE TABLE positions (
  id SERIAL PRIMARY KEY,
  member_number TEXT NOT NULL REFERENCES members(member_number),
  position_type TEXT NOT NULL, -- 'facrep', 'neb', 'committee', 'regional'
  facility_code TEXT,
  facility_name TEXT,
  position_title TEXT,
  start_date DATE,
  end_date DATE,
  active BOOLEAN DEFAULT TRUE,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_positions_member_number ON positions(member_number);
CREATE INDEX idx_positions_type ON positions(position_type);
CREATE INDEX idx_positions_facility ON positions(facility_code);
CREATE INDEX idx_positions_active ON positions(active);

Facilities Table

NATCA facilities and their details.

CREATE TABLE facilities (
  id SERIAL PRIMARY KEY,
  facility_code TEXT UNIQUE NOT NULL,
  facility_name TEXT NOT NULL,
  region_code TEXT,
  region_name TEXT,
  facility_type TEXT, -- 'TRACON', 'ARTCC', 'TOWER', etc.
  active BOOLEAN DEFAULT TRUE,
  member_count INTEGER DEFAULT 0,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_facilities_code ON facilities(facility_code);
CREATE INDEX idx_facilities_region ON facilities(region_code);
CREATE INDEX idx_facilities_active ON facilities(active);

Regions Table

NATCA regional organizations.

CREATE TABLE regions (
  id SERIAL PRIMARY KEY,
  region_code TEXT UNIQUE NOT NULL,
  region_name TEXT NOT NULL,
  active BOOLEAN DEFAULT TRUE,
  facility_count INTEGER DEFAULT 0,
  member_count INTEGER DEFAULT 0,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_regions_code ON regions(region_code);
CREATE INDEX idx_regions_active ON regions(active);

Verification Requests Table

Discord verification workflow tracking.

CREATE TABLE verification_requests (
  id SERIAL PRIMARY KEY,
  verification_id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
  discord_id TEXT NOT NULL,
  discord_username TEXT NOT NULL,
  member_number TEXT,
  status TEXT DEFAULT 'pending', -- 'pending', 'completed', 'expired', 'failed'
  auth0_user_id TEXT,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_verification_requests_verification_id ON verification_requests(verification_id);
CREATE INDEX idx_verification_requests_discord_id ON verification_requests(discord_id);
CREATE INDEX idx_verification_requests_status ON verification_requests(status);
CREATE INDEX idx_verification_requests_expires_at ON verification_requests(expires_at);

Sync Logs Table

Data synchronization tracking and auditing.

CREATE TABLE sync_logs (
  id SERIAL PRIMARY KEY,
  sync_id TEXT NOT NULL,
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL, -- 'sync_start', 'sync_complete', 'sync_error'
  records_processed INTEGER DEFAULT 0,
  records_succeeded INTEGER DEFAULT 0,
  records_failed INTEGER DEFAULT 0,
  duration_ms INTEGER,
  error_details JSONB,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_sync_logs_sync_id ON sync_logs(sync_id);
CREATE INDEX idx_sync_logs_table_name ON sync_logs(table_name);
CREATE INDEX idx_sync_logs_operation ON sync_logs(operation);
CREATE INDEX idx_sync_logs_created_at ON sync_logs(created_at);

Row Level Security (RLS)

Supabase RLS policies ensure proper data access control.

Members Table Policies

-- Enable RLS
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
 
-- Service role can access all data
CREATE POLICY "Service role full access" ON members
  FOR ALL USING (auth.role() = 'service_role');
 
-- Authenticated users can only see their own data
CREATE POLICY "Users can view own data" ON members
  FOR SELECT USING (
    auth.jwt() ->> 'https://mynatca.org/member_number' = member_number
  );
 
-- Users can update their own contact information
CREATE POLICY "Users can update own contact info" ON members
  FOR UPDATE USING (
    auth.jwt() ->> 'https://mynatca.org/member_number' = member_number
  ) WITH CHECK (
    auth.jwt() ->> 'https://mynatca.org/member_number' = member_number
  );

Positions Table Policies

-- Enable RLS
ALTER TABLE positions ENABLE ROW LEVEL SECURITY;
 
-- Service role can access all data
CREATE POLICY "Service role full access" ON positions
  FOR ALL USING (auth.role() = 'service_role');
 
-- Users can view their own positions
CREATE POLICY "Users can view own positions" ON positions
  FOR SELECT USING (
    auth.jwt() ->> 'https://mynatca.org/member_number' = member_number
  );
 
-- Admin users can view all positions
CREATE POLICY "Admin users can view all positions" ON positions
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM positions p
      WHERE p.member_number = auth.jwt() ->> 'https://mynatca.org/member_number'
      AND p.position_type IN ('neb', 'regional')
      AND p.active = true
    )
  );

Verification Requests Policies

-- Enable RLS
ALTER TABLE verification_requests ENABLE ROW LEVEL SECURITY;
 
-- Service role can manage all verification requests
CREATE POLICY "Service role can manage verification requests" ON verification_requests
  FOR ALL USING (auth.role() = 'service_role');
 
-- Public access for verification completion (during auth flow)
CREATE POLICY "Public can complete verification" ON verification_requests
  FOR UPDATE USING (
    status = 'pending' AND expires_at > NOW()
  ) WITH CHECK (
    status IN ('completed', 'failed')
  );

Hybrid Architecture Data Access Pattern

The platform uses a hybrid approach for database operations to optimize performance and maintain data integrity:

Read Operations (Direct Supabase Access)

Applications perform direct reads from Supabase for real-time data access:

Discord Bot & Member Hub:

Read member information directly from Supabase (dev or public schema)
Read facilities, regions, and positions from Supabase
No API intermediary for read operations
Optimizes performance by reducing network hops
Enables real-time data access with low latency

Benefits:

Faster response times for member lookups
Reduced load on Platform API
Direct access to PostgreSQL query optimization
Real-time updates via Supabase subscriptions

Write Operations (Platform API)

All write operations go through the Platform API for centralized validation and logging:

Discord Verification Flow:

Discord bot creates verification request via Platform API endpoint
Platform API validates member data and creates verification record
Platform API updates member Discord link upon verification completion
All writes logged and audited through Platform API

Benefits:

Centralized validation logic
Comprehensive audit logging
Business rule enforcement
Data integrity guarantees
Consistent error handling

Architecture Diagram

Migration from Full Sync Architecture

Previous Approach:

All operations went through Platform API
Sync process copied data to application-specific tables
Higher latency for read operations
Duplicate data storage

Current Hybrid Approach:

Direct reads from shared Supabase tables
Centralized writes through Platform API
Single source of truth in Supabase
Better performance and data consistency

Data Synchronization

Sync Process Overview

The sync system transfers data from MySQL to Supabase on a scheduled basis.

Sync Configuration

# Sync timing and batching
SYNC_BATCH_SIZE=1000
SYNC_RETRY_COUNT=3
SYNC_RETRY_DELAY=1000
SYNC_TIMEOUT=300000
SYNC_SCHEDULE=0 */6 * * *

# Sync health monitoring
HEALTH_CHECK_INTERVAL=300000
MAX_SYNC_AGE=86400000
MIN_SUCCESS_RATE=0.95

Sync Data Flow

Regions: Regional organization data
Facilities: Facility information and hierarchy
Members: Core member records and contact information
Positions: Leadership positions and appointments

Error Handling

Retry Logic: Failed records are retried with exponential backoff
Error Isolation: Single record failures don't stop batch processing
Validation: Data validation before insertion into Supabase
Monitoring: Comprehensive logging and alerting for sync issues

Database Maintenance

Automated Backups

Supabase provides automated daily backups with point-in-time recovery.

-- Manual backup creation
SELECT pg_dump('your_database_name');
 
-- Point-in-time recovery (Supabase dashboard)
-- Backups retained for 7 days (Starter) / 30 days (Pro)

Performance Optimization

Index Maintenance

-- Analyze table statistics
ANALYZE members;
ANALYZE positions;
ANALYZE facilities;
 
-- Check index usage
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename IN ('members', 'positions', 'facilities');
 
-- Vacuum tables
VACUUM ANALYZE members;
VACUUM ANALYZE positions;

Query Optimization

-- Explain query plans
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM members
WHERE facility_code = 'ZTL'
AND member_type_id = 6;
 
-- Check slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Data Cleanup

Expired Verification Requests

-- Clean up expired verification requests (run daily)
DELETE FROM verification_requests
WHERE status = 'pending'
AND expires_at < NOW() - INTERVAL '1 day';

Audit Log Rotation

-- Archive old sync logs (run monthly)
DELETE FROM sync_logs
WHERE created_at < NOW() - INTERVAL '90 days';

Security Considerations

Connection Security

All database connections use SSL/TLS encryption
Service role keys are rotated regularly
Database access is restricted by IP allowlisting when possible

Data Privacy

Personal information is handled according to privacy policies
RLS policies enforce proper data access controls
Audit logging tracks all data access and modifications

Access Control

Principle of least privilege for all database roles
Separate service accounts for different applications
Regular review of access permissions and policies

Monitoring and Alerting

Database Health Monitoring

-- Connection count
SELECT count(*) FROM pg_stat_activity;
 
-- Database size
SELECT pg_size_pretty(pg_database_size('postgres'));
 
-- Table sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Performance Metrics

Query response times
Connection pool utilization
Index hit ratios
Sync operation success rates

This comprehensive database documentation ensures proper understanding and maintenance of the MyNATCA platform's data infrastructure.

DigitalOcean App Platform Discord Bot Guide