sasa-maillist/database/README.md

# Database-Driven Mailing List Management

This mailing list system uses MySQL with **Postfix's native MySQL support** for real-time dynamic list management. Postfix queries the database directly for each email - no scripts or reloads needed.

## Management Options

**1. Web Interface (Recommended for Non-Technical Users)**
- Access: http://localhost:3000
- Visual interface with tables and forms
- Toggle-based subscription management
- No SQL knowledge required

**2. REST API (Recommended for Automation)**
- Access: http://localhost:8000/docs
- Full CRUD operations via HTTP
- Token authentication
- Perfect for scripts and integrations

**3. Direct MySQL (Recommended for Advanced Users)**
- Full SQL access for complex queries
- Bulk operations and reporting
- Database administration tasks
- Described in detail below

## Database Schema

Three-table design with many-to-many relationships:

### Tables

**`lists`** - Mailing list definitions
- `list_id` (primary key)
- `list_name` - Display name
- `list_email` - Full email address (e.g., `community@lists.sasalliance.org`)
- `description` - Optional description
- `active` - Boolean flag to enable/disable list

**`members`** - Member information
- `member_id` (primary key)
- `name` - Display name
- `email` - Email address
- `active` - Boolean flag to enable/disable member

**`list_members`** - Subscription junction table
- `list_id` + `member_id` (composite unique key)
- `active` - Boolean flag to enable/disable subscription
- Foreign keys to `lists` and `members`

## How It Works

1. **Incoming email** arrives for `community@lists.sasalliance.org`
2. **Postfix queries MySQL** using the config in `mysql_virtual_alias_maps.cf`
3. **Database returns** comma-separated list of active member emails
4. **Postfix expands** the alias and delivers to all members
5. **Changes take effect immediately** - no restart or reload needed!

## Managing Lists and Members

### Via Web Interface (Easiest)

1. Open http://localhost:3000 in your browser
2. Enter your API_TOKEN (from .env file)
3. Use the tabs to:
   - **Lists Tab**: View, create, edit, delete mailing lists
   - **Members Tab**: View, add, edit, remove members
   - **Subscriptions**: Click "Subscriptions" button on any member to toggle their list memberships

### Via REST API (For Automation)

See `api/README.md` for complete API documentation, or visit http://localhost:8000/docs for interactive docs.

Quick examples:
```bash
# Get all lists
curl -H "Authorization: Bearer $API_TOKEN" http://localhost:8000/lists

# Create member
curl -X POST http://localhost:8000/members \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name":"John Doe","email":"john@example.com","active":true}'

# Subscribe to list
curl -X POST http://localhost:8000/subscriptions \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"list_email":"community@lists.sasalliance.org","member_email":"john@example.com"}'
```

### Via MySQL Client (Advanced)

Connect to the database:

```bash
docker-compose exec mysql mysql -u maillist -p maillist
```

### Common Operations

**View all lists:**
```sql
SELECT list_id, list_name, list_email, active FROM lists;
```

**View all members:**
```sql
SELECT member_id, name, email, active FROM members;
```

**View subscriptions for a list:**
```sql
SELECT m.name, m.email 
FROM members m
JOIN list_members lm ON m.member_id = lm.member_id
JOIN lists l ON lm.list_id = l.list_id
WHERE l.list_email = 'community@lists.sasalliance.org'
  AND lm.active = TRUE AND m.active = TRUE;
```

**Add a new member:**
```sql
INSERT INTO members (name, email) 
VALUES ('John Doe', 'john.doe@example.com');
```

**Subscribe member to list:**
```sql
-- Method 1: Using subqueries (one step)
INSERT INTO list_members (list_id, member_id) 
VALUES (
    (SELECT list_id FROM lists WHERE list_email = 'community@lists.sasalliance.org'),
    (SELECT member_id FROM members WHERE email = 'john.doe@example.com')
);
```

**Unsubscribe member from list:**
```sql
DELETE FROM list_members 
WHERE list_id = (SELECT list_id FROM lists WHERE list_email = 'community@lists.sasalliance.org')
  AND member_id = (SELECT member_id FROM members WHERE email = 'john.doe@example.com');
```

**Create a new mailing list:**
```sql
INSERT INTO lists (list_name, list_email, description)
VALUES ('Developers', 'dev@lists.sasalliance.org', 'Developer discussions');
```

**Disable a list (keeps data, stops delivery):**
```sql
UPDATE lists SET active = FALSE WHERE list_email = 'community@lists.sasalliance.org';
```

**Re-enable a list:**
```sql
UPDATE lists SET active = TRUE WHERE list_email = 'community@lists.sasalliance.org';
```

## Verification

Test that Postfix can query the database:

```bash
docker-compose exec postfix postmap -q "community@lists.sasalliance.org" mysql:/etc/postfix/mysql_virtual_alias_maps.cf
```

This should return a comma-separated list of member email addresses.

## Database Initialization

The database is automatically initialized from `database/schema.sql` when the MySQL container first starts. Sample data includes:

- 4 mailing lists (community, board, members, announcements)
- 2 sample members
- Sample subscriptions

### Reset Database

To completely reset the database (deletes all data!):

```bash
docker-compose down -v  # Remove volumes
docker-compose up -d    # Reinitialize from schema.sql
```

## Performance

Postfix caches MySQL query results, so the database isn't queried for every single email. The cache TTL is configurable in `mysql_virtual_alias_maps.cf` if needed.