​

Staging Database Status & Seed Instructions

​

Single-Fund Setup

• ~3,857 migrated users (investor IDs: INV-XXXXXX)
• 7 test users (testinvestor, test, l2investor, etc.)

• Cycle 0: Seed cycle for test users (created by seed script)
• Cycles 1-4: Historical cycles for migrated users (created by migration)

• The seed script is migration-safe and only modifies test user data
• Migrated users are never deleted or modified by the seed script
• Test users can be reset anytime by re-running the seed script
• Running the seed multiple times is safe and idempotent

​

Understanding Cycle & Tranche Relationships

​

Key Concepts

​

Seed Data Structure (ZGF Fund - Test Users)

┌─────────────────────────────────────────────────────────────────────────────┐
│                    STAGING DATABASE STRUCTURE                                │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  Fund: Zest Growth Fund (ZGF) - SINGLE PLATFORM FUND                        │
│  UUID: 33333333-3333-3333-3333-333333333333                                 │
│                                                                              │
│  ├── Cycle 0 (COMPLETED) - SEED DATA FOR TEST USERS                        │
│  │     └── Test user tranches (current_cycle_id → Cycle 0):                │
│  │           ├── testinvestor: $10,000 principal, $500 profit (ACTIVE)     │
│  │           ├── test: $5,000 principal, $250 profit (ACTIVE)              │
│  │           ├── l2investor: $2,000 principal, $100 profit (ACTIVE)        │
│  │           ├── l3investor: $3,000 principal, $150 profit (ACTIVE)        │
│  │           └── institution: $100,000 principal, $3,500 profit (ACTIVE)   │
│  │                                                                          │
│  └── Cycles 1-4 (COMPLETED) - MIGRATED DATA                                │
│        └── ~3,857 tranches from legacy users (investor_id: INV-XXXXXX)     │
│                                                                              │
│  Test AUM: $120,000 principal + $4,500 profit = $124,500                    │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

​

Why This Setup?

1. COMPLETED cycle - Simulates a fund that has already run one profit distribution
2. ACTIVE tranches - Users see their investments earning; not locked in PENDING state
3. available_profit set - Users can test withdrawing or reinvesting profits immediately
4. current_cycle_id linked - Tranches are properly associated with the cycle

​

What Investors See in Portfolio

​

Current Staging Data

​

Test Users

• SUPER_ADMIN: Full access to all admin operations, system settings, cycle lifecycle (create, start, distribute). Cannot set PNL rate.
• FUND_ADMIN: Cycle management (view, set profit rates), fund manager oversight, trade approvals.
• SUPPORT_AGENT: Support Center only, read investor profiles for context.
• INVESTOR: Own data only (portfolio, deposits, withdrawals).

​

Referral Chain

testinvestor@zestamc.com (ZESTTEST)
    └── test@zestamc.com (TEST1234)
            └── l2investor@zestamc.com (L2INVEST)
                    └── l3investor@zestamc.com (L3INVEST)

​

Fund

Note: Single fund for all users (migrated + test). Fixed UUID for consistency.

​

Cycle (Seed Cycle 0)

Note: This is a “historical” cycle that simulates previous profit distribution. It allows tranches to have available_profit from the start.

​

Active Tranches

​

Bonus Wallet Balances

​

Wallet Addresses

​

Pending Items (For Admin Testing)

​

KYC Status (For KYC Verification Testing)

​

Support Tickets (For Support Chat Testing)

​

Direct Messages (For DM Testing)

​

Login Credentials

​

Testing Scenarios

​

Scenario 1: Investor Checks Portfolio

1. Login as testinvestor@zestamc.com
2. Navigate to Portfolio page
3. Expected values:
   • Principal: $10,000
   • Available Profit: $500
   • Bonus Balance: $50
   • Tranche Status: ACTIVE

​

Scenario 2: Admin Creates New Cycle

1. Login as admin@zestamc.com
2. Go to Admin > Cycles
3. Create a new cycle (Cycle 1) for Zest Growth Fund
4. Set dates (e.g., today + 7 days)
5. Expected: Cycle shows in PENDING status with $120,000 total principal

​

Scenario 3: Admin Starts Cycle

1. With a PENDING cycle created
2. Click “Start Cycle”
3. Enter OTP from email
4. Expected:
   • Cycle status → PROCESSING
   • All ACTIVE tranches participate
   • Any PENDING tranches would activate (none in seed)

​

Scenario 4: Investor Reinvests Profit

1. Login as testinvestor@zestamc.com
2. Navigate to Reinvestment
3. Reinvest $250 from available profit
4. Expected:
   • New PENDING tranche created with $250
   • Available profit reduced by $250
   • Tranche will join next cycle when admin starts it

​

Scenario 5: Support Agent Claims Ticket

1. Login as supportagent@zestamc.com
2. Go to Support page
3. Find the OPEN ticket “Question about my pending deposit”
4. Click “Claim” or “Assign to Me”
5. Expected:
   • Ticket assigned to you
   • Status changes to IN_PROGRESS
   • System message appears in chat

​

Scenario 6: Super Admin Sends Direct Message

1. Login as admin@zestamc.com
2. Navigate to user management or Direct Messages
3. Select testinvestor@zestamc.com as recipient
4. Send a message
5. Expected:
   • Message sent successfully
   • Message appears in testinvestor’s inbox when they login

​

Re-seeding (Developer Only)

# Get credentials from secure config, then run:
psql "<connection_string>" -f docs/staging-testing/seed-staging.sql

• Only affects test users (testinvestor, test, l2investor, etc.)
• Migrated users (~3,857 with INV-XXXXXX IDs) are never modified
• Can be run multiple times safely
• Uses same fund (ZGF) as migration

1. Clean test user data only (testinvestor, test, l2investor, etc.)
2. Preserve migrated user data (investor_id LIKE ‘INV-%’)
3. Ensure ZGF fund exists (uses fixed UUID: 33333333-…-333333333333)
4. Create Cycle 0 (COMPLETED) for test user tranches
5. Create ACTIVE tranches linked to Cycle 0
6. Set up bonus balances for referral testing
7. Create wallet addresses for test users (including 1 pending for admin testing)
8. Create sample deposit requests (1 completed, 1 pending)
9. Set up referral chain for test users
10. Create support agent user with SUPPORT_AGENT role
11. Create sample support tickets (OPEN, CLOSED, IN_PROGRESS)
12. Create sample ticket messages and internal notes
13. Create sample direct messages from admin

​

Quick Verification

-- Check fund exists (single fund: ZGF)
SELECT code, name, is_active,
  (SELECT COUNT(*) FROM tranches t WHERE t.fund_id = f.id) as tranche_count
FROM funds f WHERE code = 'ZGF';

-- Check cycles (Cycle 0 = seed, Cycles 1-4 = migration)
SELECT cycle_number, status, profit_rate, start_date, end_date
FROM cycles
WHERE fund_id = '33333333-3333-3333-3333-333333333333'::UUID
ORDER BY cycle_number;

-- Check test user tranches (linked to Cycle 0)
SELECT
  p.email,
  t.principal,
  t.available_profit,
  t.status,
  c.cycle_number
FROM tranches t
JOIN profiles p ON t.user_id = p.id
LEFT JOIN cycles c ON t.current_cycle_id = c.id
WHERE p.email LIKE '%@zestamc.com'
ORDER BY p.email;

-- Check migrated user count (linked to Cycles 1-4)
SELECT COUNT(*) as migrated_tranches, SUM(principal) as total_principal
FROM tranches t
JOIN profiles p ON t.user_id = p.id
WHERE p.investor_id LIKE 'INV-%';

-- Check pending items for admin
SELECT 'Wallet' as type, p.email FROM wallet_addresses w
JOIN profiles p ON w.user_id = p.id WHERE w.status = 'PENDING_APPROVAL'
UNION ALL
SELECT 'Deposit' as type, p.email FROM deposit_requests d
JOIN profiles p ON d.user_id = p.id WHERE d.status = 'SUBMITTED';

-- Check test user portfolio totals
SELECT
  SUM(principal) as total_principal,
  SUM(available_profit) as total_profit,
  COUNT(*) as tranche_count
FROM tranches t
JOIN profiles p ON t.user_id = p.id
WHERE t.status = 'ACTIVE' AND p.email LIKE '%@zestamc.com';

-- Check support tickets
SELECT st.subject, st.status, p.email as user, a.email as agent
FROM support_tickets st
JOIN profiles p ON st.user_id = p.id
LEFT JOIN profiles a ON st.assigned_to = a.id
ORDER BY st.created_at;

-- Check user roles for support chat
SELECT p.email, ur.role FROM user_roles ur
JOIN profiles p ON ur.user_id = p.id
WHERE ur.role IN ('SUPPORT_AGENT', 'SUPER_ADMIN');

​

Staging User IDs (Reference)

​

Fixed IDs for FK References

​

Troubleshooting

​

Portfolio shows $0 or wrong values

1. Verify tranches have current_cycle_id set (links to cycle)
2. Check tranche status is ACTIVE (not PENDING)
3. Run verification query above to confirm data

​

Cannot login

1. Verify you’re using the correct email (case-sensitive)
2. Use OTP from email
3. Check if the staging frontend is accessible at https://zestamc.savibm.com

​

Missing data

​

Connection issues

1. Check the API is running on Fly.io
2. Verify Redis is accessible on Upstash
3. Check Supabase project status

​

Related Documentation

• Cycles System Deep Dive - Full technical explanation
• Core Mechanics - Profit distribution details
• Reinvestment Architecture - How reinvestments create new tranches