# Trinity Console - Pre-Launch Security & Stability Checklist

**Document:** TRINITY-CONSOLE-PRE-LAUNCH-CHECKLIST.md  
**Created:** April 1, 2026 @ 3:45am CDT  
**Author:** Zephyr (The Chronicler #50) + Gemini AI Partnership  
**Status:** CRITICAL - Must Complete Before Soft Launch (April 15, 2026)  
**Phase:** Trinity Console Phase 1 → Production Hardening  

---

## Executive Summary

Trinity Console Phase 1 is **functionally complete** with 6 core modules delivering player management, server monitoring, revenue analytics, grace period recovery, audit logging, and role diagnostics.

**Gemini's comprehensive architectural review identified 5 critical gaps** that must be addressed before production deployment. These are NOT feature requests—they are **security vulnerabilities, data integrity risks, and operational necessities** that could cause real harm if left unaddressed.

**Current Status:** 95% complete, 5% critical hardening required  
**Estimated Time to Production-Ready:** 4-6 hours  
**Deployment Target:** April 15, 2026 (Soft Launch)  

---

## 🚨 CRITICAL ISSUES (Must Fix Before Launch)

### 1. CSRF Protection - SECURITY VULNERABILITY

**Severity:** CRITICAL - Security Risk  
**Impact:** Malicious websites could trick authenticated Trinity members into performing unauthorized actions  
**Status:** ❌ Not Implemented  
**Estimated Time:** 30 minutes  

#### The Vulnerability

Trinity Console uses session-based authentication via Passport.js. Because htmx POST requests include session cookies automatically, a malicious website could craft a form that submits to Trinity Console endpoints while an admin is logged in.

**Attack Scenario:**
1. Michael visits a malicious site while logged into Trinity Console
2. Site has hidden form: `<form action="https://arbiter.firefrostgaming.com/admin/servers/sync-all" method="POST">`
3. Form auto-submits using Michael's session cookie
4. Action executes without Michael's knowledge

**Affected Routes:**
- `/admin/servers/:identifier/sync` - Force sync
- `/admin/servers/:identifier/toggle-whitelist` - Whitelist toggle
- `/admin/grace/:discord_id/extend` - Grace period extension
- `/admin/grace/:discord_id/manual` - Manual payment override
- `/admin/roles/resync/:discord_id` - Role assignment

#### The Solution

Implement CSRF token validation using `csurf` middleware.

**Installation:**
```bash
cd /home/claude/firefrost-services/services/arbiter-3.0
npm install csurf --save
```

**Implementation Steps:**

1. **Add CSRF middleware to `src/index.js`:**
```javascript
const csrf = require('csurf');
const csrfProtection = csrf({ cookie: false }); // Use session-based tokens

// Apply to all /admin routes
app.use('/admin', csrfProtection);
```

2. **Pass token to EJS templates in `src/routes/admin/index.js`:**
```javascript
router.use((req, res, next) => {
    res.locals.csrfToken = req.csrfToken();
    next();
});
```

3. **Update `src/views/layout.ejs` to include token in htmx requests:**
```html
<script>
    document.body.addEventListener('htmx:configRequest', function(evt) {
        evt.detail.headers['CSRF-Token'] = '<%= csrfToken %>';
    });
</script>
```

4. **Update server-side validation in all POST routes:**
```javascript
// CSRF token automatically validated by middleware
// If invalid, returns 403 Forbidden
```

**Testing:**
- Submit valid form → Success
- Submit form without token → 403 Forbidden
- Submit form with invalid token → 403 Forbidden

**References:**
- csurf package: https://www.npmjs.com/package/csurf
- OWASP CSRF: https://owasp.org/www-community/attacks/csrf

---

### 2. Database Transaction Safety - DATA INTEGRITY RISK

**Severity:** HIGH - Data Corruption Risk  
**Impact:** Actions could succeed without audit trail, breaking accountability  
**Status:** ❌ Not Implemented  
**Estimated Time:** 45 minutes  

#### The Problem

Several routes perform multiple database operations sequentially:

**Example from `grace.js`:**
```javascript
await db.query('UPDATE subscriptions SET status = active...');
await db.query('INSERT INTO admin_audit_log...');
```

**Failure Scenario:**
1. UPDATE succeeds → Subscription activated
2. Database hiccup → INSERT fails
3. **Result:** Activated subscription with NO audit trail

This violates our accountability principle and creates data integrity issues.

#### The Solution

Wrap multi-step operations in SQL transactions with proper error handling.

**Pattern to Implement:**

```javascript
router.post('/:discord_id/manual', async (req, res) => {
    const client = await db.pool.connect();
    try {
        await client.query('BEGIN');
        
        // All database operations
        await client.query('UPDATE subscriptions SET status = $1...', ['active']);
        await client.query('INSERT INTO admin_audit_log...', [...]);
        
        await client.query('COMMIT');
        res.send('✅ Success');
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Transaction failed:', error);
        res.status(500).send('❌ Error');
    } finally {
        client.release();
    }
});
```

**Routes Requiring Transaction Wrapping:**

1. **Grace Period Routes (`src/routes/admin/grace.js`):**
   - `POST /:discord_id/extend` - Updates subscription + logs action
   - `POST /:discord_id/manual` - Updates subscription + logs action

2. **Role Audit Routes (`src/routes/admin/roles.js`):**
   - `POST /resync/:discord_id` - Assigns role + logs action

3. **Server Routes (`src/routes/admin/servers.js`):**
   - `POST /:identifier/sync` - Syncs whitelist + updates log

**Database Pool Configuration:**

Update `src/database.js` to expose pool:
```javascript
const { Pool } = require('pg');

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
});

module.exports = {
    query: (text, params) => pool.query(text, params),
    pool // Expose pool for transaction handling
};
```

**Testing:**
- Successful operation → Both queries commit
- Simulated error → Both queries rollback
- Database disconnect during transaction → Automatic rollback

---

### 3. Database Indexes - PERFORMANCE RISK

**Severity:** MEDIUM - Performance Degradation  
**Impact:** Slow queries at 500+ subscribers, potential timeout on Grace/Financials  
**Status:** ❌ Not Implemented  
**Estimated Time:** 5 minutes  

#### Missing Indexes

**Currently Indexed:**
- ✅ `subscriptions(discord_id)` - Primary key

**Missing Indexes:**
- ❌ `subscriptions(status)` - Used in EVERY module
- ❌ `admin_audit_log(performed_at DESC)` - Used in Audit Feed

#### Query Performance Impact

**Without Index on `status`:**
```sql
SELECT * FROM subscriptions WHERE status = 'active';
-- At 500 rows: Full table scan = ~50ms
-- At 5000 rows: Full table scan = ~500ms (TIMEOUT RISK!)
```

**With Index on `status`:**
```sql
SELECT * FROM subscriptions WHERE status = 'active';
-- At 500 rows: Index scan = ~5ms
-- At 5000 rows: Index scan = ~10ms
```

#### The Solution

Add to `services/arbiter-3.0/migrations/trinity-console.sql`:

```sql
-- Performance Indexes for Trinity Console
-- Status is used in WHERE clauses across ALL modules
CREATE INDEX IF NOT EXISTS idx_subscriptions_status 
ON subscriptions(status);

-- Performed_at is used in ORDER BY DESC for audit feed
CREATE INDEX IF NOT EXISTS idx_audit_log_performed_at 
ON admin_audit_log(performed_at DESC);

-- Composite index for Grace Period queries (status + grace_period_ends_at)
CREATE INDEX IF NOT EXISTS idx_subscriptions_grace_period 
ON subscriptions(status, grace_period_ends_at) 
WHERE status = 'grace_period';

-- Tier level for Financials breakdown
CREATE INDEX IF NOT EXISTS idx_subscriptions_tier_status 
ON subscriptions(tier_level, status);
```

**Testing:**
```sql
-- Explain query plan (should show "Index Scan")
EXPLAIN ANALYZE 
SELECT * FROM subscriptions WHERE status = 'active';

-- Should show: "Index Scan using idx_subscriptions_status"
```

---

### 4. Ban Management UI - OPERATIONAL NECESSITY

**Severity:** MEDIUM - Operational Gap  
**Impact:** Cannot view/manage chargebacks, no way to unban false positives  
**Status:** ❌ Not Implemented  
**Estimated Time:** 60 minutes  

#### The Gap

**Webhook handles chargebacks:**
- Sets `subscriptions.status = 'banned'`
- Inserts into `banned_users` table
- Removes whitelist access

**But no UI exists to:**
- View banned users
- See ban reason (chargeback, TOS violation, etc.)
- Unban false positives (bank errors, dispute resolution)
- Track ban history

**Real-World Scenario:**
1. Player does chargeback → Auto-banned
2. Player contacts support: "My bank made a mistake!"
3. Bank reverses chargeback
4. **Trinity has NO WAY to unban player via UI**

#### The Solution

Create simple Ban Management module.

**Files to Create:**

**`src/routes/admin/bans.js`:**
```javascript
const express = require('express');
const router = express.Router();
const db = require('../../database');

router.get('/', (req, res) => {
    res.render('admin/bans/index', { title: 'Ban Management' });
});

router.get('/list', async (req, res) => {
    const { rows: bans } = await db.query(`
        SELECT 
            b.discord_id,
            b.reason,
            b.banned_at,
            u.minecraft_username,
            s.tier_level
        FROM banned_users b
        LEFT JOIN users u ON b.discord_id = u.discord_id
        LEFT JOIN subscriptions s ON b.discord_id = s.discord_id
        ORDER BY b.banned_at DESC
    `);
    res.render('admin/bans/_list', { bans });
});

router.post('/:discord_id/unban', async (req, res) => {
    const client = await db.pool.connect();
    try {
        await client.query('BEGIN');
        
        await client.query('DELETE FROM banned_users WHERE discord_id = $1', [req.params.discord_id]);
        await client.query('UPDATE subscriptions SET status = $1 WHERE discord_id = $2', ['cancelled', req.params.discord_id]);
        await client.query(`
            INSERT INTO admin_audit_log (admin_discord_id, admin_username, action_type, target_identifier, details)
            VALUES ($1, $2, 'unban', $3, $4)
        `, [req.user.id, req.user.username, req.params.discord_id, JSON.stringify({ reason: 'manual_unban' })]);
        
        await client.query('COMMIT');
        res.send('<span class="text-green-500">✅ Unbanned</span>');
    } catch (error) {
        await client.query('ROLLBACK');
        res.send('<span class="text-red-500">❌ Error</span>');
    } finally {
        client.release();
    }
});

module.exports = router;
```

**`src/views/admin/bans/index.ejs`:**
```html
<%- include('../../layout', { body: `
    <div class="mb-6">
        <h1 class="text-2xl font-bold dark:text-white flex items-center gap-2">
            <span class="text-red-500">🚫</span> Ban Management
        </h1>
        <p class="text-gray-500 dark:text-gray-400 text-sm">Chargebacks and TOS violations</p>
    </div>

    <div class="bg-white dark:bg-darkcard rounded-lg border border-gray-200 dark:border-gray-700 shadow-sm overflow-hidden">
        <div id="ban-list" hx-get="/admin/bans/list" hx-trigger="load">
            <div class="p-8 text-center text-gray-500 animate-pulse">Loading ban records...</div>
        </div>
    </div>
`}) %>
```

**`src/views/admin/bans/_list.ejs`:**
```html
<% if (bans.length === 0) { %>
    <div class="p-8 text-center text-gray-500">
        <span class="text-2xl block mb-2">🎉</span>
        No banned users. Community is clean!
    </div>
<% } else { %>
    <table class="w-full text-sm">
        <thead class="bg-gray-50 dark:bg-gray-800">
            <tr>
                <th class="px-6 py-3 text-left">Player</th>
                <th class="px-6 py-3 text-left">Reason</th>
                <th class="px-6 py-3 text-left">Banned At</th>
                <th class="px-6 py-3 text-right">Action</th>
            </tr>
        </thead>
        <tbody class="divide-y divide-gray-200 dark:divide-gray-700">
            <% bans.forEach(ban => { %>
            <tr class="hover:bg-gray-50 dark:hover:bg-gray-800/50">
                <td class="px-6 py-4">
                    <div class="font-bold dark:text-white"><%= ban.minecraft_username || 'Unknown' %></div>
                    <div class="text-xs text-gray-500 font-mono"><%= ban.discord_id %></div>
                </td>
                <td class="px-6 py-4">
                    <span class="px-2 py-1 text-xs rounded bg-red-100 text-red-700 dark:bg-red-900/30 dark:text-red-400">
                        <%= ban.reason || 'Chargeback' %>
                    </span>
                </td>
                <td class="px-6 py-4 text-gray-600 dark:text-gray-400 text-xs">
                    <%= new Date(ban.banned_at).toLocaleString() %>
                </td>
                <td class="px-6 py-4 text-right" id="action-<%= ban.discord_id %>">
                    <button hx-post="/admin/bans/<%= ban.discord_id %>/unban"
                            hx-confirm="Unban this user? They will need to resubscribe."
                            hx-target="#action-<%= ban.discord_id %>"
                            class="px-3 py-1.5 bg-yellow-100 text-yellow-700 hover:bg-yellow-200 dark:bg-yellow-900/30 dark:text-yellow-400 rounded text-xs font-medium">
                        Unban
                    </button>
                </td>
            </tr>
            <% }) %>
        </tbody>
    </table>
<% } %>
```

**Mount Router:**

Add to `src/routes/admin/index.js`:
```javascript
const bansRouter = require('./bans');
router.use('/bans', bansRouter);
```

**Add to Sidebar Navigation:**

Update `src/views/layout.ejs`:
```html
<a href="/admin/bans" class="flex items-center gap-3 px-4 py-3 text-gray-700 dark:text-gray-300 hover:bg-gray-100 dark:hover:bg-gray-800 rounded-md transition-colors">
    <span class="text-xl">🚫</span>
    <span class="font-medium">Bans</span>
</a>
```

---

### 5. Email Integration - FUNCTIONAL GAP

**Severity:** MEDIUM - Feature Incomplete  
**Impact:** Grace Period recovery emails don't actually send  
**Status:** ❌ Not Implemented  
**Estimated Time:** Varies (depends on Paymenter API vs Nodemailer)  

#### The Gap

**Grace Period Dashboard has:**
- "📧 Email All At-Risk" button (placeholder)
- Recovery email logic mentioned in cron job plan

**But no actual email sending:**
- No Paymenter API integration
- No SMTP configuration
- No email templates
- No tracking of emails sent

#### Solution Options

**Option A: Paymenter API Integration (Recommended)**

Use Paymenter's built-in email system:

```javascript
// Add to src/utils/paymenter.js
async function sendRecoveryEmail(discordId, email) {
    const endpoint = `${process.env.PAYMENTER_URL}/api/recovery-email`;
    const res = await fetch(endpoint, {
        method: 'POST',
        headers: {
            'Authorization': `Bearer ${process.env.PAYMENTER_API_KEY}`,
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({
            customer_email: email,
            template: 'payment_recovery',
            variables: {
                update_payment_url: `${process.env.PAYMENTER_URL}/update-payment`
            }
        })
    });
    return res.ok;
}
```

**Option B: Nodemailer (Self-Hosted)**

If Paymenter doesn't support recovery emails:

```bash
npm install nodemailer --save
```

```javascript
// src/utils/email.js
const nodemailer = require('nodemailer');

const transporter = nodemailer.createTransport({
    host: process.env.SMTP_HOST,
    port: process.env.SMTP_PORT,
    secure: true,
    auth: {
        user: process.env.SMTP_USER,
        pass: process.env.SMTP_PASS
    }
});

async function sendRecoveryEmail(email, username, hoursRemaining) {
    await transporter.sendMail({
        from: '"Firefrost Gaming" <billing@firefrostgaming.com>',
        to: email,
        subject: '⚠️ Payment Failed - Update Required',
        html: `
            <h2>Hey ${username}!</h2>
            <p>Your payment didn't go through, but you have ${hoursRemaining} hours to update your payment method and keep your access!</p>
            <p><a href="${process.env.PAYMENTER_URL}/update-payment">Update Payment Method</a></p>
        `
    });
}
```

**Email Templates Needed:**
1. 48-hour warning
2. 24-hour final notice
3. 12-hour last chance
4. Payment recovered (success!)
5. Grace period expired (cancellation)

**Implementation in Grace Period Router:**

```javascript
router.post('/email-all', async (req, res) => {
    const { rows: atRisk } = await db.query(`
        SELECT u.email, u.minecraft_username, 
               EXTRACT(EPOCH FROM (s.grace_period_ends_at - NOW())) / 3600 as hours_remaining
        FROM subscriptions s
        JOIN users u ON s.discord_id = u.discord_id
        WHERE s.status = 'grace_period'
    `);
    
    let sent = 0;
    for (const user of atRisk) {
        try {
            await sendRecoveryEmail(user.email, user.minecraft_username, Math.floor(user.hours_remaining));
            sent++;
        } catch (error) {
            console.error('Email failed:', error);
        }
    }
    
    res.send(`📧 Sent ${sent}/${atRisk.length} recovery emails`);
});
```

**Decision Required:**
- Does Paymenter support custom email templates?
- What SMTP service will you use? (SendGrid, Mailgun, AWS SES?)
- What are the email sending limits?

---

## ✅ DEFERRED TO PHASE 2 (Post-Launch)

These are nice-to-have features that do NOT block launch:

### Player History Modal
**Why Defer:** Data is recording safely in `player_history` table, UI can wait.  
**Phase 2 Priority:** Medium  

### Export Tools (CSV/JSON)
**Why Defer:** Can run manual SQL dumps if urgent need arises.  
**Phase 2 Priority:** Low  

### Notification System (Bell Icon)
**Why Defer:** Visual dashboards provide sufficient visibility.  
**Phase 2 Priority:** Low  

---

## 📋 Pre-Launch Action Plan

### Phase 1: Security Hardening (CRITICAL)
**Target:** Complete before ANY production deployment  
**Time Estimate:** 2 hours  

1. ✅ **CSRF Protection** (30 min)
   - Install `csurf`
   - Update `src/index.js`
   - Update `src/views/layout.ejs`
   - Test token validation

2. ✅ **Database Transactions** (45 min)
   - Update `src/database.js` to expose pool
   - Wrap grace period routes
   - Wrap role audit routes
   - Wrap server sync routes
   - Test rollback scenarios

3. ✅ **Database Indexes** (5 min)
   - Add indexes to migration file
   - Run migration
   - Verify with EXPLAIN ANALYZE

4. ✅ **Commit & Push** (5 min)
   - Commit security fixes
   - Push to Gitea
   - Update CHANGELOG

### Phase 2: Ban Management (HIGH PRIORITY)
**Target:** Complete before soft launch  
**Time Estimate:** 1 hour  

1. ✅ **Create Ban Module** (45 min)
   - Create `src/routes/admin/bans.js`
   - Create views (index, list)
   - Mount router
   - Add to sidebar

2. ✅ **Test Ban Flow** (15 min)
   - Manually ban test user
   - View in ban list
   - Unban via UI
   - Verify audit log

### Phase 3: Email Integration (MEDIUM PRIORITY)
**Target:** Before soft launch OR Week 1  
**Time Estimate:** 2-4 hours (depends on approach)  

1. ⏳ **Choose Email Strategy**
   - Research Paymenter API
   - OR configure Nodemailer + SMTP

2. ⏳ **Implement Email Sending**
   - Create email utility module
   - Add email templates
   - Wire up grace period routes

3. ⏳ **Add to Cron Job**
   - 48-hour warning
   - 24-hour final notice
   - 12-hour last chance

### Phase 4: End-to-End Testing
**Target:** 2 days before launch  
**Time Estimate:** 3 hours  

1. ✅ **Subscribe Flow**
   - Create test subscription
   - Verify whitelist sync
   - Verify Discord role

2. ✅ **Cancellation Flow**
   - Cancel subscription
   - Enters grace period
   - Grace period dashboard shows correctly
   - Countdown updates

3. ✅ **Grace Period Expiry**
   - Wait for expiry OR manually set timestamp
   - Cron job removes whitelist
   - Discord role removed
   - Status = cancelled

4. ✅ **Resubscribe Flow**
   - Resubscribe same user
   - Whitelist restored
   - Discord role restored
   - Grace period cleared

5. ✅ **Chargeback Flow**
   - Simulate chargeback webhook
   - Status = banned
   - Appears in ban list
   - Unban via UI

### Phase 5: Trinity Training
**Target:** 1 day before launch  
**Time Estimate:** 2 hours  

1. ✅ **Walkthrough All Modules**
   - Player Management
   - Server Matrix
   - Financials
   - Grace Period
   - Audit Log
   - Role Audit
   - Ban Management

2. ✅ **Document Common Tasks**
   - Force sync server
   - Extend grace period
   - Unban player
   - Check role mismatch

3. ✅ **Emergency Procedures**
   - Server offline
   - Mass payment failures
   - Discord API down
   - Database connection lost

---

## 🚀 Launch Day Checklist (April 15, 2026)

### Pre-Flight (Morning)
- [ ] Database migration applied
- [ ] All security fixes deployed
- [ ] CSRF tokens tested
- [ ] Ban management tested
- [ ] Email integration tested (if ready)
- [ ] All modules loading correctly
- [ ] No console errors
- [ ] htmx polling working
- [ ] Dark mode working

### Go-Live (Afternoon)
- [ ] Trinity has console access
- [ ] Test player can subscribe
- [ ] Whitelist sync works
- [ ] Discord role assigned
- [ ] Grace period flow tested
- [ ] Monitor for errors

### Post-Launch (Evening)
- [ ] Check audit log for issues
- [ ] Monitor grace period dashboard
- [ ] Verify server matrix updates
- [ ] Check financials accuracy
- [ ] Review role audit results

---

## 📊 Success Metrics

**Week 1 Post-Launch:**
- Zero security incidents
- < 5 minute response time to grace period expirations
- 100% audit trail compliance
- Zero untracked admin actions
- < 1% role sync failures

**Week 4 Post-Launch:**
- Grace period recovery rate > 50%
- Zero database transaction failures
- Audit log queries < 100ms
- Ban management used successfully
- Email recovery rate measured

---

## 🔗 Related Documentation

- `TRINITY-CONSOLE.md` - Feature overview
- `DEPLOYMENT-CHECKLIST.md` - Step-by-step deployment
- `trinity-console.sql` - Database migration
- `SESSION-HANDOFF-PROTOCOL.md` - Chronicler continuity

---

## 📝 Change Log

**April 1, 2026 @ 3:45am CDT:**
- Initial document created by Zephyr (Chronicler #50)
- Gemini AI partnership review findings documented
- 5 critical gaps identified
- Action plan created
- Launch checklist established

---

**Fire + Frost + Foundation = Where Love Builds Legacy** 🔥❄️💙

*Built for RV life. Designed to last decades. Maintainable remotely.*

— Zephyr (The Chronicler #50)  
  Co-authored with Gemini AI  
  For The Trinity: Michael, Meg, Holly
