Database Architecture

MySQL and Qdrant database architecture for WordPress projects and vector-based AI search.

Overview

WDG uses two database systems:

• MySQL: WordPress data storage
• Qdrant: Vector embeddings for AI search

MySQL Architecture

Single MySQL Instance

All projects share one MySQL container with isolated databases:

MySQL Container (Port 3307)
├── mysql (system database)
├── information_schema
├── performance_schema
├── wp_my_site (project database)
├── wp_client_website (project database)
└── wp_demo (project database)

Database Naming Convention

• Project databases: wp_{project-name}
• Underscores replace hyphens: my-site → wp_my_site
• Lowercase only

Configuration

# docker-compose.yml
mysql:
  image: mysql:8.0
  environment:
    MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    MYSQL_DATABASE: wordpress
    MYSQL_USER: wordpress
    MYSQL_PASSWORD: wordpress
  volumes:
    - mysql-data:/var/lib/mysql
    - ./services/mysql/my.cnf:/etc/mysql/conf.d/custom.cnf
  command: --default-authentication-plugin=mysql_native_password

Custom MySQL Configuration

services/mysql/my.cnf:

[mysqld]
# Performance
max_connections = 200
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M

# Query cache
query_cache_type = 1
query_cache_size = 128M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

WordPress Database Schema

Standard Tables

Each project database includes:

-- Core tables
wp_posts
wp_postmeta
wp_users
wp_usermeta
wp_terms
wp_term_taxonomy
wp_term_relationships
wp_comments
wp_commentmeta
wp_options

-- Multisite (if enabled)
wp_blogs
wp_blog_versions
wp_site
wp_sitemeta

Project-Specific Tables

Custom tables created by themes/plugins:

-- Example custom tables
wp_wikit_blocks
wp_wikit_patterns
wp_custom_post_types
wp_user_preferences

Indexing Strategy

-- Performance indexes
CREATE INDEX idx_post_type ON wp_posts(post_type, post_status);
CREATE INDEX idx_post_author ON wp_posts(post_author);
CREATE INDEX idx_post_date ON wp_posts(post_date);
CREATE INDEX idx_meta_key ON wp_postmeta(meta_key(191));

Qdrant Vector Database

Collection Architecture

Qdrant Container (Port 6333)
├── wdg_framework (Wikit code)
│   ├── Vectors: ~15,000
│   └── Size: ~48 MB
├── project_my_site
│   ├── Vectors: ~1,450
│   └── Size: ~4.7 MB
└── project_client_website
    ├── Vectors: ~3,892
    └── Size: ~12.1 MB

Vector Schema

{
  "id": "unique-vector-id",
  "vector": [0.1, 0.2, 0.3, ...],  // 384 dimensions
  "payload": {
    "project": "my-site",
    "file_path": "wp-content/themes/custom/functions.php",
    "file_type": "php",
    "component_type": "function",
    "component_name": "register_custom_post_types",
    "line_start": 45,
    "line_end": 65,
    "content": "function register_custom_post_types() {...}",
    "language": "php",
    "indexed_at": "2024-10-14T10:30:00Z",
    "commit_hash": "abc123"
  }
}

Collection Configuration

# Create collection with optimal settings
client.create_collection(
    collection_name="project_my_site",
    vectors_config={
        "size": 384,  # all-MiniLM-L6-v2 dimensions
        "distance": "Cosine"
    },
    optimizers_config={
        "indexing_threshold": 20000,
        "memmap_threshold": 50000
    },
    hnsw_config={
        "m": 16,  # Number of edges per node
        "ef_construct": 100  # Construction time/quality tradeoff
    }
)

Database Operations

MySQL Operations

Create Project Database

# Via CLI
wdg db create my-site

# Manual
docker exec wdg-mysql mysql -uroot -p$MYSQL_ROOT_PASSWORD -e \
    "CREATE DATABASE wp_my_site CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Backup Database

# Export
wdg db export my-site backup.sql

# Manual
docker exec wdg-mysql mysqldump \
    -uroot -p$MYSQL_ROOT_PASSWORD \
    wp_my_site > backup.sql

Import Database

# Import
wdg db import my-site backup.sql

# Manual
docker exec -i wdg-mysql mysql \
    -uroot -p$MYSQL_ROOT_PASSWORD \
    wp_my_site < backup.sql

Search & Replace

# Update domain
wdg db search-replace my-site "old-domain.com" "new-domain.com"

# Via WP-CLI
wdg wp my-site search-replace \
    "http://localhost" \
    "https://production.com" \
    --all-tables

Qdrant Operations

Query Vectors

# Semantic search
results = client.search(
    collection_name="project_my_site",
    query_vector=query_embedding,
    limit=10,
    with_payload=True
)

Filter by Metadata

# Search only PHP functions
results = client.search(
    collection_name="project_my_site",
    query_vector=query_embedding,
    query_filter={
        "must": [
            {"key": "file_type", "match": {"value": "php"}},
            {"key": "component_type", "match": {"value": "function"}}
        ]
    },
    limit=10
)

Performance Optimization

MySQL Tuning

# Check status
docker exec wdg-mysql mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Optimize tables
wdg wp my-site db optimize

# Analyze tables
docker exec wdg-mysql mysqlcheck -uroot -p wp_my_site --analyze

Qdrant Optimization

# Optimize collection
curl -X POST http://localhost:6333/collections/project_my_site/optimizers

# Check collection info
curl http://localhost:6333/collections/project_my_site

Backup Strategies

Automated Backups

#!/bin/bash
# backup-databases.sh

DATE=$(date +%Y%m%d-%H%M%S)
BACKUP_DIR="backups/$DATE"

mkdir -p $BACKUP_DIR

# Backup all MySQL databases
for db in $(docker exec wdg-mysql mysql -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW DATABASES" | grep ^wp_); do
    echo "Backing up $db..."
    docker exec wdg-mysql mysqldump -uroot -p$MYSQL_ROOT_PASSWORD $db > "$BACKUP_DIR/$db.sql"
done

# Backup Qdrant collections
curl -X POST http://localhost:6333/collections/snapshot -d '{}' > "$BACKUP_DIR/qdrant.snapshot"

# Compress
tar -czf "backups/full-backup-$DATE.tar.gz" $BACKUP_DIR
rm -rf $BACKUP_DIR

echo "Backup complete: backups/full-backup-$DATE.tar.gz"

Monitoring

MySQL Monitoring

# Connection count
docker exec wdg-mysql mysql -uroot -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Query performance
docker exec wdg-mysql mysql -uroot -p -e "SHOW FULL PROCESSLIST;"

# Database sizes
docker exec wdg-mysql mysql -uroot -p -e "
SELECT
  table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema LIKE 'wp_%'
GROUP BY table_schema;
"

Qdrant Monitoring

# Collection stats
curl http://localhost:6333/collections/project_my_site

# Cluster status
curl http://localhost:6333/cluster

# Metrics
curl http://localhost:6333/metrics

Troubleshooting

MySQL Issues

# Connection refused
docker logs wdg-mysql
docker restart wdg-mysql

# Corrupted table
wdg wp my-site db repair

# Slow queries
docker exec wdg-mysql mysql -uroot -p -e "SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;"

Qdrant Issues

# Collection not found
curl http://localhost:6333/collections

# Recreate collection
wdg collections delete project_my_site
wdg index my-site

# Check logs
docker logs wdg-qdrant

---

See Also:

• Docker Setup
• Collection Management
• Code Indexing