# Database & User Profile Backup System

A comprehensive backup solution for Nazexa DB admin panel, allowing administrators to create, manage, and restore full database and user profile backups in both SQL and JSON formats.

## Features

### 🗄️ Database Backup
- **SQL Format**: Complete SQL dump of entire database including table structures and data
- **JSON Format**: Machine-readable JSON export suitable for data integration and analysis
- Complete data preservation with all tables and relationships

### 👥 User Profile Backup
- **JSON Format**: Backup all user profiles with subscription info
- Anonymized sensitive data for privacy compliance
- Includes user projects, roles, and recent activity logs
- Safe for sharing and archiving

### 📦 Backup Management
- **Create Backups**: One-click backup creation with multiple format options
- **Download**: Download any backup locally for external storage or analysis
- **List History**: View all created backups with metadata (size, creation date, type)
- **Delete**: Remove old backups to manage storage
- **Audit Logging**: All backup operations are logged in audit trail

## Access & Security

### Admin-Only Access
- Requires authenticated admin user with `platformRole: 'admin'`
- All backup operations require valid session
- Access control enforced at API and page levels

### Authentication
- Uses NextAuth.js session authentication
- Protected routes require valid admin session
- IP logging for audit trail

## Usage

### Access the Backup Panel
1. Navigate to Admin Dashboard (`/admin`)
2. Click "Backups" in the sidebar navigation
3. Or visit directly: `/admin/backups`

### Create Database Backup

#### SQL Format
1. Click "SQL Format" under Database Backup
2. Confirm the operation in the dialog
3. Click "Create Backup"
4. Backup file: `backup_database_TIMESTAMP.sql`

#### JSON Format
1. Click "JSON Format" under Database Backup
2. Confirm the operation in the dialog
3. Click "Create Backup"
4. Backup file: `backup_database_TIMESTAMP.json`

### Create User Profile Backup

1. Click "JSON Format" under User Profiles Backup
2. Confirm the operation in the dialog
3. Click "Create Backup"
4. Backup file: `backup_users_TIMESTAMP.json`

### Download a Backup
1. Locate the backup in the history table
2. Click the download button (↓ icon)
3. Backup is downloaded to your device

### Delete a Backup
1. Locate the backup in the history table
2. Click the delete button (🗑️ icon)
3. Confirm deletion in the dialog
4. Backup is permanently removed

## API Endpoints

### GET `/api/admin/backup`

List all backups or perform operations.

**Query Parameters:**
- `action`: Operation to perform
  - `list` - List all backups
  - `download` - Download backup content
  - `delete` - Delete a backup
- `filename` - Backup filename (required for download/delete)

**Examples:**

```bash
# List all backups
GET /api/admin/backup?action=list

# Download backup
GET /api/admin/backup?action=download&filename=backup_database_2026-08-07T20-01-47.sql

# Delete backup
GET /api/admin/backup?action=delete&filename=backup_database_2026-08-07T20-01-47.sql
```

### POST `/api/admin/backup`

Create a new backup.

**Request Body:**
```json
{
  "type": "database|users",
  "format": "sql|json"
}
```

**Constraints:**
- `type`: "database" or "users"
- `format`: "sql" or "json"
- User backups only support JSON format

**Examples:**

```bash
# Create database SQL backup
POST /api/admin/backup
Content-Type: application/json

{
  "type": "database",
  "format": "sql"
}

# Create user profile JSON backup
POST /api/admin/backup
Content-Type: application/json

{
  "type": "users",
  "format": "json"
}
```

**Response:**
```json
{
  "success": true,
  "message": "Backup created successfully",
  "backup": {
    "filename": "backup_database_2026-08-07T20-01-47.sql",
    "size": "25.5 MB",
    "type": "database",
    "format": "sql",
    "createdAt": "2026-08-07T20:01:47.358Z",
    "metadata": {
      "timestamp": "2026-08-07T20:01:47.358Z",
      "version": "1.0",
      "database": "nazexa",
      "format": "sql",
      "recordCount": { /* table counts */ }
    }
  }
}
```

## Database Backup Contents (SQL)

SQL backups include:

```sql
-- Table definitions with all constraints
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  -- ... all columns and constraints
);

-- Data for each table
INSERT INTO users VALUES (...);
INSERT INTO users VALUES (...);

-- Relationships and foreign keys
CREATE TABLE projects (
  id VARCHAR(36) PRIMARY KEY,
  userId VARCHAR(36),
  FOREIGN KEY (userId) REFERENCES users(id),
  -- ...
);
```

**Tables included:**
- `User`, `Account`, `Session` (Auth)
- `Subscription`, `Plan`, `UsageRecord` (Subscriptions)
- `Project`, `ProjectMember`, `ProjectShare`, `ProjectInvitation` (Projects)
- `Table`, `Column`, `TableIndex`, `Relationship` (Schema Designer)
- `Activity`, `Version`, `Comment`, `Export` (Project Data)
- `PaymentGateway`, `PaymentRequest` (Payments)
- `AuditLog`, `LoginThrottle`, `VerificationToken`, `PasswordResetToken` (Security)
- And more...

## User Profile Backup Contents (JSON)

```json
{
  "metadata": {
    "timestamp": "2026-08-07T20:01:47.358Z",
    "version": "1.0",
    "database": "nazexa",
    "format": "json",
    "recordCount": {
      "users": 42,
      "profiles": 42
    },
    "size": "512 KB"
  },
  "data": [
    {
      "id": "user_id",
      "email": "user@example.com",
      "name": "User Name",
      "platformRole": "user",
      "status": "active",
      "lastLoginAt": "2026-08-07T15:00:00.000Z",
      "createdAt": "2026-08-01T10:00:00.000Z",
      "subscription": {
        "status": "active",
        "currentPeriodStart": "2026-08-01T00:00:00.000Z",
        "currentPeriodEnd": "2026-09-01T00:00:00.000Z"
      },
      "projectCount": 3,
      "projects": [
        {
          "projectId": "proj_123",
          "projectName": "My Project",
          "role": "owner",
          "joinedAt": "2026-08-01T10:00:00.000Z"
        }
      ],
      "recentActivity": [
        {
          "action": "project_created",
          "targetType": "Project",
          "targetId": "proj_123",
          "createdAt": "2026-08-07T15:00:00.000Z"
        }
      ]
    }
  ]
}
```

## File Storage

Backups are stored in the `/backups` directory in the project root:

```
project-root/
├── backups/
│   ├── backup_database_2026-08-07T20-01-47.sql
│   ├── backup_database_2026-08-06T15-30-22.json
│   ├── backup_users_2026-08-07T14-15-00.json
│   └── ...
├── src/
├── prisma/
└── ...
```

## Audit Logging

All backup operations are logged in the `AuditLog` table:

- **BACKUP_CREATED**: When a backup is created
  - Includes type, format, and file size
- **BACKUP_DELETED**: When a backup is deleted
  - Includes filename

Example:
```json
{
  "id": "audit_123",
  "action": "BACKUP_CREATED",
  "targetType": "Backup",
  "targetId": "backup_database_2026-08-07T20-01-47.sql",
  "details": "{\"type\":\"database\",\"format\":\"sql\",\"size\":26624000}",
  "actorId": "admin_user_id",
  "ip": "192.168.1.1",
  "createdAt": "2026-08-07T20:01:47.358Z"
}
```

## File Information

### Core Files

1. **`src/lib/admin/backup.ts`** (10.4 KB)
   - Core backup service module
   - Functions for SQL/JSON generation
   - File management operations
   - Utility functions

2. **`src/app/api/admin/backup/route.ts`** (5.9 KB)
   - API routes for backup operations
   - GET: List, download, delete operations
   - POST: Create new backups
   - Authentication & authorization
   - Audit logging

3. **`src/components/admin/BackupManager.tsx`** (16.8 KB)
   - React client component
   - UI for backup creation
   - Backup history display
   - Download/delete functionality
   - Tab-based filtering

4. **`src/app/admin/(panel)/backups/page.tsx`** (1.5 KB)
   - Admin backup page
   - Server-side authentication
   - Admin role verification

### Updated Files

1. **`src/components/admin/AdminSidebar.tsx`**
   - Added Database icon import
   - Added Backups navigation link

2. **`.gitignore`**
   - Added `/backups/` to exclude backup files

## Technical Details

### SQL Backup Generation

```typescript
// Process:
1. Get all tables from information_schema
2. For each table:
   - Fetch CREATE TABLE statement
   - Fetch all table data
   - Generate INSERT statements
   - Format as proper SQL
3. Return complete SQL dump
```

### JSON Backup Generation

```typescript
// Process:
1. Query all Prisma models
2. For each model:
   - Fetch all records with relations
   - Serialize to JSON
   - Include metadata (timestamps, counts)
3. Return formatted JSON with metadata
```

### Database Connection

Uses Prisma ORM with MySQL:
- Configured via `DATABASE_URL` environment variable
- Direct SQL queries for backup/restore operations
- Transactions for consistency

## Performance Considerations

### Backup Creation Time
- Database backups: Depends on database size (typically 10-60 seconds)
- User backups: Usually 2-5 seconds
- Large databases may take several minutes

### Storage Space
- SQL format is smaller for large databases
- JSON format is more readable and portable
- Each backup is stored separately

### Recommendations
- Schedule regular backups (daily/weekly)
- Download critical backups to external storage
- Delete old backups periodically
- Monitor available disk space

## Error Handling

### Common Errors

**Unauthorized (401)**
- User is not logged in
- Session expired
- Solution: Login to admin panel

**Forbidden (403)**
- User is not an admin
- Solution: Request admin access

**Invalid Request (400)**
- Missing required parameters
- Invalid backup type/format
- Solution: Check request format

**Not Found (404)**
- Backup file not found
- May have been deleted
- Solution: Create new backup

**Server Error (500)**
- Database connection issue
- File system error
- Solution: Check server logs

## Security Considerations

### Data Protection
- Admin-only access enforced
- Session-based authentication
- IP logging for audit trail
- File path validation (prevents traversal attacks)

### Sensitive Data
- SQL backups contain all data including passwords (hashed)
- User backups anonymize certain fields
- Consider encryption for external storage

### Backup Integrity
- Backups stored in project directory
- File permissions inherited from server process
- Consider backup encryption at rest

## Future Enhancements

Potential features for future versions:

- [ ] Automated backup scheduling
- [ ] Cloud storage integration (S3, GCS, Azure)
- [ ] Backup encryption
- [ ] Differential/incremental backups
- [ ] Restore from backup functionality
- [ ] Backup compression
- [ ] Email notifications
- [ ] Backup versioning with retention policies
- [ ] Database comparison tools
- [ ] Backup integrity verification

## Support

For issues or questions:
1. Check audit logs for operation details
2. Review error messages in backup creation response
3. Check server logs for detailed errors
4. Ensure admin privileges are active

## License

This backup system is part of Nazexa DB and follows the same license as the main project.