Technical Notes - Mad Monkey Project

PostgreSQL 16 - Main Application Database

Database Name: madmonkey

Host: 10.0.0.250:5432 (bennybeen)

User: mm_user

Connection String: postgresql://mm_user:PASSWORD@10.0.0.250:5432/madmonkey

Table: cards

QR card inventory and tracking

CREATE TABLE cards (
    qr_id VARCHAR(10) PRIMARY KEY,              -- e.g., 'N042' or 'T007'
    type VARCHAR(10) NOT NULL,                  -- 'nest' or 'traveler'
    status VARCHAR(20) DEFAULT 'active',        -- 'active', 'lost', 'retired'
    current_slug VARCHAR(50),                   -- Current Shlink short URL slug
    move_code VARCHAR(20) UNIQUE,               -- Unique code for traveler cards
    last_scan_ts TIMESTAMP,                     -- Last scan timestamp
    last_scan_city VARCHAR(100),                -- Last known city
    total_scans INTEGER DEFAULT 0,              -- Total scan count
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_cards_type ON cards(type);
CREATE INDEX idx_cards_status ON cards(status);
CREATE INDEX idx_cards_last_scan_ts ON cards(last_scan_ts);

Table: scans

Individual scan event logs

CREATE TABLE scans (
    scan_id SERIAL PRIMARY KEY,
    qr_id VARCHAR(10) NOT NULL REFERENCES cards(qr_id),
    ts TIMESTAMP DEFAULT NOW(),                 -- Scan timestamp
    ip_anon VARCHAR(45),                        -- Anonymized IP (last octet removed)
    user_agent TEXT,                            -- Browser user agent
    geo_city VARCHAR(100),                      -- City from IP geolocation
    geo_region VARCHAR(100),                    -- State/province
    geo_country VARCHAR(2),                     -- ISO country code
    geo_lat DECIMAL(10, 7),                     -- Latitude
    geo_lon DECIMAL(10, 7),                     -- Longitude
    referer TEXT,                               -- HTTP referer if present
    device_type VARCHAR(20)                     -- 'mobile', 'desktop', 'tablet'
);

CREATE INDEX idx_scans_qr_id ON scans(qr_id);
CREATE INDEX idx_scans_ts ON scans(ts);
CREATE INDEX idx_scans_geo_city ON scans(geo_city);

Table: entries

Contest entry records

CREATE TABLE entries (
    entry_id SERIAL PRIMARY KEY,
    qr_id VARCHAR(10) NOT NULL REFERENCES cards(qr_id),
    email VARCHAR(255) NOT NULL,                -- Subscriber email
    name VARCHAR(100),                          -- Subscriber name
    user_city VARCHAR(100),                     -- User-reported "where found"
    verified BOOLEAN DEFAULT FALSE,             -- Double opt-in confirmed
    month_bucket VARCHAR(7),                    -- 'YYYY-MM' for monthly drawing
    created_at TIMESTAMP DEFAULT NOW(),
    verified_at TIMESTAMP,
    listmonk_subscriber_id INTEGER,             -- Reference to Listmonk
    UNIQUE(email, month_bucket)                 -- One entry per email per month
);

CREATE INDEX idx_entries_email ON entries(email);
CREATE INDEX idx_entries_verified ON entries(verified);
CREATE INDEX idx_entries_month_bucket ON entries(month_bucket);

Table: moves

Traveler card movement tracking

CREATE TABLE moves (
    move_id SERIAL PRIMARY KEY,
    traveler_id VARCHAR(10) NOT NULL REFERENCES cards(qr_id),
    rev INTEGER NOT NULL,                       -- Movement revision number
    mover_email VARCHAR(255),                   -- Who moved the card
    pickup_city VARCHAR(100),                   -- Where card was picked up
    drop_city VARCHAR(100),                     -- Where card was dropped off
    pickup_ts TIMESTAMP,
    drop_ts TIMESTAMP,
    distance_km DECIMAL(10, 2),                 -- Calculated distance
    notes TEXT,                                 -- User notes about the move
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_moves_traveler_id ON moves(traveler_id);
CREATE INDEX idx_moves_rev ON moves(rev);

Table: nests

Venue/location tracking for nest cards

CREATE TABLE nests (
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,                 -- Venue name
    address TEXT,                               -- Street address
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(20),
    contact_name VARCHAR(100),                  -- Venue contact person
    contact_email VARCHAR(255),
    contact_phone VARCHAR(20),
    health_score INTEGER DEFAULT 0,             -- Performance metric (scans/week)
    cards_placed INTEGER DEFAULT 0,             -- Number of cards at venue
    last_activity TIMESTAMP,                    -- Last scan from this venue
    created_at TIMESTAMP DEFAULT NOW(),
    notes TEXT
);

CREATE INDEX idx_nests_city ON nests(city);
CREATE INDEX idx_nests_health_score ON nests(health_score);

Table: users

Subscriber profile aggregation

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100),
    tags TEXT[],                                -- Array of tags
    referral_code VARCHAR(20) UNIQUE,           -- User's unique referral code
    total_entries INTEGER DEFAULT 0,
    verified_entries INTEGER DEFAULT 0,
    first_entry_date TIMESTAMP,
    last_entry_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_referral_code ON users(referral_code);

Table: referrals

Referral tracking system

CREATE TABLE referrals (
    referral_id SERIAL PRIMARY KEY,
    inviter_email VARCHAR(255) NOT NULL REFERENCES users(email),
    invitee_email VARCHAR(255) NOT NULL REFERENCES users(email),
    status VARCHAR(20) DEFAULT 'pending',       -- 'pending', 'verified', 'rewarded'
    created_at TIMESTAMP DEFAULT NOW(),
    verified_at TIMESTAMP,
    UNIQUE(inviter_email, invitee_email)
);

CREATE INDEX idx_referrals_inviter ON referrals(inviter_email);
CREATE INDEX idx_referrals_status ON referrals(status);

Table: prizes

Monthly winner fulfillment tracking

CREATE TABLE prizes (
    prize_id SERIAL PRIMARY KEY,
    month VARCHAR(7) NOT NULL,                  -- 'YYYY-MM'
    winner_email VARCHAR(255) NOT NULL REFERENCES users(email),
    winner_entry_id INTEGER REFERENCES entries(entry_id),
    fulfillment_status VARCHAR(20) DEFAULT 'pending',  -- 'pending', 'ordered', 'shipped', 'delivered'
    printify_order_id VARCHAR(100),             -- External order ID
    tracking_number VARCHAR(100),
    shipping_address TEXT,
    awarded_at TIMESTAMP DEFAULT NOW(),
    fulfilled_at TIMESTAMP,
    UNIQUE(month)                               -- One winner per month
);

CREATE INDEX idx_prizes_month ON prizes(month);
CREATE INDEX idx_prizes_fulfillment_status ON prizes(fulfillment_status);

Listmonk Database Schema

Note: Listmonk manages its own schema. Key tables:

• subscribers - Email addresses and metadata
• lists - Email lists (we'll use "Mad Monkey - Benny Campaign")
• campaigns - Email campaign definitions
• templates - Email templates (Welcome, Story, Reward)
• subscriber_lists - Many-to-many relationship

Docker Host: bennybeen (10.0.0.250)

External Ports & Services

Docker Compose - PostgreSQL

File: docker-compose-postgres.yml

version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: mm-postgres
    restart: unless-stopped
    networks:
      mm-network:
        ipv4_address: 10.0.0.151
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: mm_user
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: madmonkey
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d

networks:
  mm-network:
    external: true

volumes:
  postgres_data:

Docker Compose - Shlink

File: docker-compose-shlink.yml

version: '3.8'

services:
  shlink:
    image: shlinkio/shlink:stable
    container_name: mm-shlink
    restart: unless-stopped
    networks:
      mm-network:
        ipv4_address: 10.0.0.152
    ports:
      - "8080:8080"
    environment:
      DEFAULT_DOMAIN: mmlnk.us
      IS_HTTPS_ENABLED: "true"
      DB_DRIVER: postgres
      DB_HOST: 10.0.0.151
      DB_PORT: 5432
      DB_NAME: madmonkey
      DB_USER: mm_user
      DB_PASSWORD: ${POSTGRES_PASSWORD}
      GEOLITE_LICENSE_KEY: ${GEOLITE_KEY}
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.shlink-public.rule=Host(`mmlnk.us`) || Host(`www.mmlnk.us`)"
      - "traefik.http.routers.shlink-public.entrypoints=websecure"
      - "traefik.http.routers.shlink-public.tls.certresolver=letsencrypt"
      - "traefik.http.routers.shlink-admin.rule=Host(`admin.mmlnk.us`) && PathPrefix(`/rest`)"
      - "traefik.http.routers.shlink-admin.entrypoints=websecure"
      - "traefik.http.routers.shlink-admin.middlewares=ip-whitelist"

networks:
  mm-network:
    external: true

Docker Compose - Listmonk

File: docker-compose-listmonk.yml

version: '3.8'

services:
  listmonk:
    image: listmonk/listmonk:latest
    container_name: mm-listmonk
    restart: unless-stopped
    networks:
      mm-network:
        ipv4_address: 10.0.0.153
    ports:
      - "9000:9000"
    environment:
      LISTMONK_app__address: "0.0.0.0:9000"
      LISTMONK_db__host: 10.0.0.151
      LISTMONK_db__port: 5432
      LISTMONK_db__user: mm_user
      LISTMONK_db__password: ${POSTGRES_PASSWORD}
      LISTMONK_db__database: listmonk
      LISTMONK_db__ssl_mode: disable
    volumes:
      - ./config.toml:/listmonk/config.toml
      - listmonk_uploads:/listmonk/uploads

networks:
  mm-network:
    external: true

volumes:
  listmonk_uploads:

Listmonk config.toml

[app]
address = "0.0.0.0:9000"

[db]
host = "10.0.0.151"
port = 5432
user = "mm_user"
password = "YOUR_STRONG_PASS"
database = "listmonk"
ssl_mode = "disable"
max_open = 25
max_idle = 25

[privacy]
individual_tracking = false
unsubscribe_header = true
allow_blocklist = true
allow_export = true

[upload]
provider = "filesystem"
filesystem_upload_path = "/listmonk/uploads"

[security]
enable_captcha = false

n8n Environment Variables

File: .env (for n8n container)

# n8n Configuration
N8N_ENCRYPTION_KEY=your-encryption-key-here
N8N_HOST=hooks.mad-monkey-creations.com
N8N_PORT=5678
N8N_PROTOCOL=https
WEBHOOK_URL=https://hooks.mad-monkey-creations.com
N8N_BASIC_AUTH_ACTIVE=true
N8N_BASIC_AUTH_USER=admin
N8N_BASIC_AUTH_PASSWORD=your-password-here

# Database Connection
DB_TYPE=postgresdb
DB_POSTGRESDB_HOST=10.0.0.151
DB_POSTGRESDB_PORT=5432
DB_POSTGRESDB_DATABASE=n8n
DB_POSTGRESDB_USER=mm_user
DB_POSTGRESDB_PASSWORD=YOUR_STRONG_PASS

# Mad Monkey Project
MM_SENDER_EMAIL=hello@mad-monkey-creations.com
MM_LANDING_BASE=https://www.mad-monkey-creations.com
MM_REWARD_URL=https://www.mad-monkey-creations.com/reward
MM_DISCOUNT_CODE=BANANAS10

# API Credentials
LISTMONK_URL=http://10.0.0.153:9000
LISTMONK_API_USER=admin
LISTMONK_API_PASSWORD=your-listmonk-password
LISTMONK_LIST_ID=1

SHLINK_URL=http://10.0.0.152:8080
SHLINK_API_KEY=your-shlink-api-key

# External APIs
IPAPI_KEY=optional
GEOLITE_KEY=optional
ETSY_API_KEY=optional

Minimum Hardware Requirements

Software Requirements

mmlnk.us DNS Records

mad-monkey-creations.com DNS Records

IP Anonymization

Remove last octet from IPv4 addresses before storage:

// Example JavaScript function
function anonymizeIP(ip) {
    if (ip.includes(':')) {
        // IPv6: remove last 80 bits (keep /48 prefix)
        const parts = ip.split(':');
        return parts.slice(0, 3).join(':') + '::';
    } else {
        // IPv4: remove last octet
        const parts = ip.split('.');
        parts[3] = '0';
        return parts.join('.');
    }
}

// Example: 203.0.113.42 → 203.0.113.0
// Example: 2001:db8:85a3::8a2e:370:7334 → 2001:db8:85a3::

Rate Limiting Rules

CAPTCHA Configuration

Option 1: Cloudflare Turnstile

<script src="https://challenges.cloudflare.com/turnstile/v0/api.js"></script>
<div class="cf-turnstile" data-sitekey="YOUR_SITE_KEY"></div>

Option 2: hCaptcha

<script src="https://js.hcaptcha.com/1/api.js"></script>
<div class="h-captcha" data-sitekey="YOUR_SITE_KEY"></div>

SSL/TLS Configuration

Certificate Provider: Let's Encrypt (via Traefik)

Renewal: Automatic (90-day certs, renew at 60 days)

Cipher Suites: TLS 1.2+ only

Traefik Labels for SSL:

labels:
  - "traefik.http.routers.myservice.tls.certresolver=letsencrypt"
  - "traefik.http.routers.myservice.tls=true"