Organization Database Configuration

Overview

This document describes the configuration and setup for the organization database system in the MyDesk Core API.

Environment Variables

Add these variables to your .env file:

Main Database Configuration


DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=mydesk_core

DB_USERNAME=root

DB_PASSWORD=your_password



Organization Database Configuration


DB_HOST_ORG=127.0.0.1

DB_PORT_ORG=3306

DB_DATABASE_ORG=mydesk

DB_USERNAME_ORG=root

DB_PASSWORD_ORG=your_password



Database Security Settings


DB_SSL_ENABLED=false

MYSQL_ATTR_SSL_CA=



Organization Settings


ORG_LOGO_BASE_URL=/storage/organizations

ORG_DATABASE_PREFIX=mydesk_



Performance Settings


DB_CONNECTION_POOL_SIZE=10

DB_CONNECTION_TIMEOUT=30

DB_QUERY_TIMEOUT=60



Monitoring Settings


HEALTH_CHECK_CACHE_TTL=300

HEALTH_CHECK_ENABLED=true



Backup Settings


BACKUP_ENABLED=true

BACKUP_RETENTION_DAYS=30

BACKUP_STORAGE_PATH=storage/backups



Rate Limiting


DB_RATE_LIMIT_ENABLED=true

DB_RATE_LIMIT_MAX_ATTEMPTS=100

DB_RATE_LIMIT_WINDOW=60



Logging


DB_QUERY_LOG_ENABLED=false

DB_SLOW_QUERY_THRESHOLD=1000

Database Setup

1. Main Database

The main database stores organization metadata and user information.

2. Organization Databases

Each organization gets its own database with the naming convention: mydesk_{orgcode}

3. Required Tables

Each organization database must have these tables:

• company - Organization information


• user - Organization users


• role - User roles


• settings - Organization settings


• migrations - Migration tracking


• {orgcode}_api_tokens - API tokens for the organization

Security Considerations

1. Database Credentials: Never hardcode database credentials
2. Connection Limits: Implement rate limiting for database connections
3. SSL/TLS: Enable SSL for production environments
4. Access Control: Use dedicated database users with minimal privileges
5. Audit Logging: Log all database operations

Performance Optimization

1. Connection Pooling: Use connection pooling for better performance
2. Query Optimization: Monitor and optimize slow queries
3. Indexing: Ensure proper indexing on frequently queried columns
4. Caching: Implement caching for frequently accessed data

Monitoring

Use the health check command to monitor organization databases:

Check specific organization


php artisan organization:health-check {orgcode}



Check all organizations


php artisan organization:health-check --all



Check only organizations with issues


php artisan organization:health-check --all --issues-only



Export results as JSON


php artisan organization:health-check --all --format=json

Backup Strategy

1. Automated Backups: Set up automated daily backups
2. Retention Policy: Keep backups for 30 days
3. Testing: Regularly test backup restoration
4. Encryption: Encrypt backup files

Troubleshooting

Common Issues

1. Connection Timeout: Check network connectivity and database server status
2. Migration Failures: Verify migration files and database permissions
3. Performance Issues: Check query performance and database size
4. Permission Errors: Verify database user permissions

Debug Commands

Test database connection


php artisan test:database-connection {orgcode}



Test organization creation


php artisan test:organization-creation --name="Test Org" --email="test@example.com"



Check migration status


php artisan migrate:status --database=mydesk_{orgcode}

Best Practices

1. Naming Convention: Use consistent naming for databases and tables
2. Error Handling: Implement comprehensive error handling
3. Logging: Log all important operations
4. Testing: Write comprehensive tests for all database operations
5. Documentation: Keep documentation up to date
6. Version Control: Track all database schema changes
7. Rollback Strategy: Always have a rollback plan for migrations