# Database Schema Documentation

## Overview

The cronjob system uses two main database tables to track execution history:
- `cron`: Standard cronjob execution records
- `cron_rapid`: Rapid/frequent cronjob execution records

## Table: cron

Standard table for tracking cronjob executions. Typically used for daily or less frequent cronjobs.

### Schema

| Column      | Type         | Null | Default | Description                                    |
|-------------|--------------|------|---------|------------------------------------------------|
| `id`        | INT          | NO   | AUTO    | Primary key                                    |
| `code`      | VARCHAR      | NO   | -       | Cron job code (e.g., 'A111', 'A002')          |
| `started`   | DATETIME     | NO   | -       | Timestamp when cronjob started                 |
| `ended`     | DATETIME     | YES  | NULL    | Timestamp when cronjob completed               |
| `completed` | CHAR(1)      | NO   | 'n'     | Completion status: 'y' or 'n'                 |
| `success`   | CHAR(1)      | NO   | 'n'     | Success status: 'y' or 'n'                    |
| `message`   | TEXT         | YES  | NULL    | Result message (success or error details)      |
| `err_code`  | VARCHAR      | YES  | NULL    | Error code if execution failed                  |
| `year`      | INT          | NO   | -       | Year of execution (for indexing/querying)       |
| `month`     | INT          | NO   | -       | Month of execution (1-12)                       |
| `day`       | INT          | NO   | -       | Day of execution (1-31)                        |
| `odate`     | DATE         | NO   | -       | Date in YYYY-MM-DD format (for querying)      |

### Indexes

**Recommended Indexes** (for performance):
- Primary key on `id`
- Index on `(code, odate)` - for duplicate checking
- Index on `(code, success, odate)` - for success checking
- Index on `(odate)` - for date-based queries
- Index on `(completed)` - for finding running jobs

### Example Record

```sql
INSERT INTO cron (
    code, started, completed, success, year, month, day, odate, ended, message, err_code
) VALUES (
    'A111', 
    '2024-01-15 01:00:00', 
    'y', 
    'y', 
    2024, 
    1, 
    15, 
    '2024-01-15',
    '2024-01-15 01:00:05',
    '',
    NULL
);
```

### Common Queries

#### Check if cron already ran
```sql
SELECT COUNT(*) AS total 
FROM cron 
WHERE code = 'A111' AND odate = '2024-01-15'
```

#### Check if cron ran successfully
```sql
SELECT COUNT(*) AS total 
FROM cron 
WHERE code = 'A111' AND success = 'y' AND odate = '2024-01-15'
```

#### Find all executions for a date
```sql
SELECT * 
FROM cron 
WHERE odate = '2024-01-15'
ORDER BY started DESC
```

#### Find failed executions
```sql
SELECT * 
FROM cron 
WHERE completed = 'y' AND success = 'n'
ORDER BY started DESC
LIMIT 100
```

#### Find running cronjobs (stuck)
```sql
SELECT * 
FROM cron 
WHERE completed = 'n' AND started < DATE_SUB(NOW(), INTERVAL 1 HOUR)
```

## Table: cron_rapid

Rapid cronjob table for tracking frequent executions. Used for cronjobs that may run multiple times per day.

### Schema

Same structure as `cron` table.

| Column      | Type         | Null | Default | Description                                    |
|-------------|--------------|------|---------|------------------------------------------------|
| `id`        | INT          | NO   | AUTO    | Primary key                                    |
| `code`      | VARCHAR      | NO   | -       | Cron job code                                  |
| `started`   | DATETIME     | NO   | -       | Timestamp when cronjob started                 |
| `ended`     | DATETIME     | YES  | NULL    | Timestamp when cronjob completed               |
| `completed` | CHAR(1)      | NO   | 'n'     | Completion status: 'y' or 'n'                 |
| `success`   | CHAR(1)      | NO   | 'n'     | Success status: 'y' or 'n'                    |
| `message`   | TEXT         | YES  | NULL    | Result message                                 |
| `err_code`  | VARCHAR      | YES  | NULL    | Error code if execution failed                  |
| `year`      | INT          | NO   | -       | Year of execution                              |
| `month`     | INT          | NO   | -       | Month of execution (1-12)                      |
| `day`       | INT          | NO   | -       | Day of execution (1-31)                        |
| `odate`     | DATE         | NO   | -       | Date in YYYY-MM-DD format                     |

### Usage

The `cron_rapid` table is used when:
- A cronjob may run multiple times per day
- You want to track each execution separately
- You don't need duplicate prevention per day

**Note**: The current PHP implementation defines this table but doesn't appear to use it in the `Cron` class. It's available via `_record_rapid_cron()` and `_update_complete_rapid_cron()` methods in `CronBase`.

## Data Flow

### Creating a Record

```php
// Step 1: Prepare data
$fields = array(
    'code' => 'A111',
    'started' => '2024-01-15 01:00:00',
    'completed' => 'n',
    'success' => 'n',
    'year' => 2024,
    'month' => 1,
    'day' => 15,
    'odate' => '2024-01-15',
);

// Step 2: Build INSERT query
INSERT INTO cron (code, started, completed, success, year, month, day, odate)
VALUES ('A111', '2024-01-15 01:00:00', 'n', 'n', 2024, 1, 15, '2024-01-15')

// Step 3: Get inserted ID
$cron_id = lastInsertId();
```

### Updating a Record

```php
// Step 1: Prepare update data
$fields = array(
    'completed' => 'y',
    'success' => 'y',
    'ended' => '2024-01-15 01:00:05',
    'message' => '',
    'err_code' => NULL,
);

// Step 2: Build UPDATE query
UPDATE cron 
SET completed = 'y', 
    success = 'y', 
    ended = '2024-01-15 01:00:05',
    message = '',
    err_code = NULL
WHERE id = 12345
```

## Migration Considerations

### For Django Conversion

When converting to Django, consider:

1. **Model Definition**:
   ```python
   class CronExecution(models.Model):
       code = models.CharField(max_length=10)
       started = models.DateTimeField()
       ended = models.DateTimeField(null=True, blank=True)
       completed = models.CharField(max_length=1, choices=[('y', 'Yes'), ('n', 'No')])
       success = models.CharField(max_length=1, choices=[('y', 'Yes'), ('n', 'No')])
       message = models.TextField(blank=True)
       err_code = models.CharField(max_length=20, null=True, blank=True)
       year = models.IntegerField()
       month = models.IntegerField()
       day = models.IntegerField()
       odate = models.DateField()
   ```

2. **Indexes**:
   ```python
   class Meta:
       indexes = [
           models.Index(fields=['code', 'odate']),
           models.Index(fields=['code', 'success', 'odate']),
           models.Index(fields=['odate']),
           models.Index(fields=['completed']),
       ]
   ```

3. **Denormalization**: The `year`, `month`, `day` fields are denormalized from `odate` for performance. Consider if this is still needed with Django's ORM.

4. **Char Fields for Boolean**: `completed` and `success` use 'y'/'n' instead of boolean. Consider using `BooleanField` in Django.

## Data Retention

Consider implementing:

1. **Archive Strategy**: Move old records to archive table
2. **Cleanup Job**: Periodically delete records older than X days
3. **Summary Tables**: Create aggregated statistics tables for reporting

## Monitoring Queries

### Health Check Queries

```sql
-- Recent failures
SELECT code, COUNT(*) as failures
FROM cron
WHERE success = 'n' 
  AND odate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY code;

-- Average execution time
SELECT code, 
       AVG(TIMESTAMPDIFF(SECOND, started, ended)) as avg_seconds
FROM cron
WHERE completed = 'y' 
  AND odate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY code;

-- Stuck cronjobs
SELECT code, started, TIMESTAMPDIFF(MINUTE, started, NOW()) as minutes_running
FROM cron
WHERE completed = 'n'
  AND started < DATE_SUB(NOW(), INTERVAL 1 HOUR);
```