# The Memory and the Alarm Clock: Database & Cron Jobs

> Where does S-UI remember your settings when you restart the server? And who cleans up old traffic stats at 3 a.m.? This page explores the SQLite/GORM database layer and the five scheduled jobs (stats, depletion check, WAL checkpoint, core health check, stats deletion), asking "what problem would happen if this job didn't exist?"

- Repository: alireza0/s-ui
- GitHub: https://github.com/alireza0/s-ui
- Human wiki: https://grok-wiki.com/public/wiki/alireza0-s-ui-2ddf594ac444
- Complete Markdown: https://grok-wiki.com/public/wiki/alireza0-s-ui-2ddf594ac444/llms-full.txt

## Source Files

- `database/db.go`
- `database/backup.go`
- `cronjob/cronJob.go`
- `cronjob/statsJob.go`
- `cronjob/depleteJob.go`
- `cronjob/checkCoreJob.go`
- `cronjob/WALCheckpointJob.go`
- `cronjob/delStatsJob.go`

---

<details>
<summary>Relevant source files</summary>
The following files were used as context for generating this wiki page:

- [database/db.go](database/db.go)
- [database/backup.go](database/backup.go)
- [cronjob/cronJob.go](cronjob/cronJob.go)
- [cronjob/statsJob.go](cronjob/statsJob.go)
- [cronjob/depleteJob.go](cronjob/depleteJob.go)
- [cronjob/checkCoreJob.go](cronjob/checkCoreJob.go)
- [cronjob/WALCheckpointJob.go](cronjob/WALCheckpointJob.go)
- [cronjob/delStatsJob.go](cronjob/delStatsJob.go)
- [service/stats.go](service/stats.go)
- [service/client.go](service/client.go)
- [service/config.go](service/config.go)
</details>

# The Memory and the Alarm Clock: Database & Cron Jobs

Imagine S-UI is a little house. The **database** is the house's memory — it remembers who lives there, what the rules are, and how much electricity everyone has used, even after the lights go out and come back on. The **cron jobs** are a set of alarm clocks in the attic: each one wakes up at a fixed time, does one small job quietly, and goes back to sleep. Neither the house's occupants nor the plumbing need to know the alarm clocks exist — they just keep everything running smoothly behind the scenes.

This page answers two questions: *"Where does S-UI remember your settings when you restart the server?"* and *"Who cleans up old traffic statistics at 3 a.m.?"* We'll look at the SQLite/GORM database layer, and then walk through each of the five scheduled jobs, asking the most important Socratic question for each one: **"What problem would happen if this job didn't exist?"**

---

## Part 1 — The Memory (Database Layer)

### What is the database?

S-UI uses **SQLite** — a single-file database that lives on disk, next to the application binary. It does not need a separate database server. When the process restarts, the file is still there, so all your configuration is recovered automatically.

The database is opened via GORM, Go's ORM library. A single package-level variable, `var db *gorm.DB`, holds the one connection that the entire app shares.

Sources: [database/db.go:18]()

### How does S-UI open and configure the database?

`OpenDB` builds the DSN (Data Source Name) — the URL that tells GORM where the file is and how to behave:

```go
// database/db.go:61
dsn := dbPath + sep + "_busy_timeout=10000&_journal_mode=WAL&_cache_size=-200"
db, err = gorm.Open(sqlite.Open(dsn), c)
```

Three flags are baked in:

| Flag | Value | What it does |
|---|---|---|
| `_busy_timeout` | 10 000 ms | If another process has a lock, wait up to 10 seconds before giving up (no immediate crash) |
| `_journal_mode` | WAL | Write-Ahead Logging — readers never block writers, and writes are faster |
| `_cache_size` | -200 | Cap each connection's page cache at ~200 KiB to limit memory use |

The connection pool is also tuned:

```go
// database/db.go:71-74
sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(2)
sqlDB.SetConnMaxLifetime(time.Hour)
sqlDB.SetConnMaxIdleTime(5 * time.Minute)
```

Sources: [database/db.go:36-79]()

### What tables does S-UI create?

`InitDB` calls GORM's `AutoMigrate`, which creates or updates tables to match Go struct definitions. On a fresh install it also seeds a default `"direct"` outbound and an `admin/admin` user account.

```go
// database/db.go:97-110
err = db.AutoMigrate(
    &model.Setting{},
    &model.Tls{},
    &model.Inbound{},
    &model.Outbound{},
    &model.Service{},
    &model.Endpoint{},
    &model.User{},
    &model.Tokens{},
    &model.Stats{},
    &model.Client{},
    &model.Changes{},
)
```

| Table | Stores |
|---|---|
| `Setting` | Key-value config (e.g. panel port, language) |
| `Inbound` / `Outbound` | sing-box proxy entry/exit points |
| `Client` | Users who consume the proxy (with volume/expiry limits) |
| `Stats` | Time-series traffic snapshots (up/down bytes per resource) |
| `Changes` | Audit log of config mutations |
| `User` | S-UI admin panel accounts |
| `Tokens` | Auth tokens for the panel API |
| `Tls`, `Service`, `Endpoint` | TLS certs, service nodes, and endpoint definitions |

Sources: [database/db.go:82-119]()

### How does backup and restore work?

`GetDb` in `database/backup.go` copies the live database into a temporary on-disk file (with a timestamp in the name), runs `PRAGMA wal_checkpoint` to flush any pending WAL writes, then reads the file into memory and returns it as bytes — ready to be sent to the browser as a download.

`ImportDB` does the reverse with a three-step safe swap:
1. Validate the upload is a real SQLite file (checks the 16-byte magic header `"SQLite format 3\x00"`).
2. Move the current database to a `.backup` file.
3. Move the upload into place and re-run `InitDB`. If that fails, the `.backup` is restored.

After a successful import, `SendSighup` sends `SIGHUP` to itself (or kills itself on Windows) after a 3-second delay so the process gracefully restarts with the new database.

Sources: [database/backup.go:25-309]()

---

## Part 2 — The Alarm Clocks (Cron Jobs)

### How are cron jobs registered?

`CronJob.Start` creates a `robfig/cron` scheduler (with second-level precision) and registers all jobs in a goroutine — so the server does not block startup waiting for jobs to be scheduled.

```go
// cronjob/cronJob.go:17-37
func (c *CronJob) Start(loc *time.Location, trafficAge int) error {
    c.cron = cron.New(cron.WithLocation(loc), cron.WithSeconds())
    c.cron.Start()

    go func() {
        c.cron.AddJob("@every 10s",  NewStatsJob(trafficAge > 0))
        c.cron.AddJob("@every 1m",   NewDepleteJob())
        if trafficAge > 0 {
            c.cron.AddJob("@daily",  NewDelStatsJob(trafficAge))
        }
        c.cron.AddJob("@every 5s",   NewCheckCoreJob())
        c.cron.AddJob("@every 10m",  NewWALCheckpointJob())
    }()
    return nil
}
```

Notice that `DelStatsJob` is **only registered if `trafficAge > 0`** — that's the "keep traffic stats for N days" setting. If you never configured it, the cleanup job simply doesn't run.

Sources: [cronjob/cronJob.go:1-43]()

Here is a visual summary of the five alarm clocks:

```text
Every 5 seconds  ┌─────────────────────────────────────────┐
                 │ CheckCoreJob — is sing-box still alive? │
                 └─────────────────────────────────────────┘

Every 10 seconds ┌─────────────────────────────────────────┐
                 │ StatsJob — snapshot traffic counters     │
                 └─────────────────────────────────────────┘

Every 1 minute   ┌─────────────────────────────────────────┐
                 │ DepleteJob — disable over-quota clients  │
                 └─────────────────────────────────────────┘

Every 10 minutes ┌─────────────────────────────────────────┐
                 │ WALCheckpointJob — merge WAL into DB    │
                 └─────────────────────────────────────────┘

@daily           ┌─────────────────────────────────────────┐
(only if config) │ DelStatsJob — delete old traffic rows   │
                 └─────────────────────────────────────────┘
```

---

### Job 1 — StatsJob (every 10 seconds)

**What does it do?**

Asks the running sing-box core for its traffic counters, then writes them into the database. It also refreshes the in-memory "who is online right now" list (inbound/outbound/user names that have uploaded anything in the last tick).

```go
// cronjob/statsJob.go:19-25
func (s *StatsJob) Run() {
    err := s.StatsService.SaveStats(s.enableTraffic)
    ...
}
```

`SaveStats` opens a transaction, updates each `Client` row's `up`/`down` columns, builds the online list, and — only when `enableTraffic` is true — inserts a new `Stats` row for time-series charting.

Sources: [service/stats.go:24-88]()

**What problem would happen if this job didn't exist?**

> The "online users" panel would always be empty. Client bandwidth counters (`up` / `down`) would never increment. The traffic charts in the UI would show nothing. You would have no idea how much data your users are consuming until you restarted sing-box and inspected its raw logs.

---

### Job 2 — DepleteJob (every 1 minute)

**What does it do?**

Checks every enabled `Client` to see if they have exceeded their data quota (`up + down > volume`) or their expiry timestamp has passed. If so, it disables them and logs a `Changes` audit record.

```go
// service/client.go:396
err = tx.Model(model.Client{}).
    Where("enable = true AND ((volume >0 AND up+down > volume) OR (expiry > 0 AND expiry < ?))", dt).
    Scan(&clients).Error
```

After disabling clients, it collects the affected inbound IDs and calls `InboundService.RestartInbounds` so sing-box immediately rejects new connections from those users.

Sources: [service/client.go:367-420](), [cronjob/depleteJob.go:18-30]()

**What problem would happen if this job didn't exist?**

> A client whose 10 GB plan ran out at 2:47 a.m. would keep downloading indefinitely. Expiry dates would be decorative. Traffic quotas would be meaningless. The proxy would keep forwarding traffic for accounts you thought you had cut off.

---

### Job 3 — CheckCoreJob (every 5 seconds)

**What does it do?**

Calls `ConfigService.StartCore()`. If sing-box is already running, the call returns immediately (one early-exit check: `if corePtr.IsRunning() { return nil }`). If sing-box has crashed or was never started, this job re-reads the configuration from the database and starts it — with a mutex and a cooldown timer to prevent rapid-fire restart loops.

```go
// service/config.go:89-126
func (s *ConfigService) StartCore() error {
    if corePtr.IsRunning() {
        return nil
    }
    // mutex + cooldown guard
    ...
    err = corePtr.Start(*rawConfig)
    ...
}
```

Sources: [service/config.go:89-126](), [cronjob/checkCoreJob.go:15-17]()

**What problem would happen if this job didn't exist?**

> If sing-box crashed — due to a bad config, an OOM kill, or a transient OS error — it would stay dead until a human noticed and manually restarted it. With this job, the proxy self-heals within 5 seconds.

---

### Job 4 — WALCheckpointJob (every 10 minutes)

**What does it do?**

SQLite in WAL mode writes new data to a separate `.wal` file first. Over time that file can grow large. A *checkpoint* merges the WAL back into the main database file and resets the WAL to empty, keeping disk usage bounded.

```go
// cronjob/WALCheckpointJob.go:14-19
func (s *WALCheckpointJob) Run() {
    db := database.GetDB()
    if err := db.Exec("PRAGMA wal_checkpoint(FULL)").Error; err != nil {
        logger.Error("Error checkpointing WAL: ", err.Error())
    }
}
```

Sources: [cronjob/WALCheckpointJob.go:1-19]()

**What problem would happen if this job didn't exist?**

> SQLite *does* checkpoint automatically under certain conditions, but with 25 open connections each writing stats every 10 seconds, the WAL can grow to hundreds of megabytes without periodic flushing. Read performance also degrades as SQLite must scan a longer WAL to reconstruct the current state of each page. Disk space would silently balloon.

---

### Job 5 — DelStatsJob (daily, only if `trafficAge > 0`)

**What does it do?**

Deletes all rows from the `Stats` table whose `date_time` column is older than `trafficAge` days.

```go
// service/stats.go:159-163
func (s *StatsService) DelOldStats(days int) error {
    oldTime := time.Now().AddDate(0, 0, -(days)).Unix()
    db := database.GetDB()
    return db.Where("date_time < ?", oldTime).Delete(model.Stats{}).Error
}
```

Because `StatsJob` runs every 10 seconds and (when enabled) inserts a new row each time, a busy server can accumulate hundreds of thousands of rows per day. This daily sweep keeps the table — and therefore the database file — from growing without bound.

Sources: [service/stats.go:159-163](), [cronjob/delStatsJob.go:19-26]()

**What problem would happen if this job didn't exist?**

> The `Stats` table would grow forever. After a few weeks on a busy server you could easily have millions of rows, making query times for the traffic chart slow, the database file huge, and backups impractical.

---

## How Everything Connects

```text
                     ┌─────────────────────────────────────┐
                     │            SQLite file               │
                     │  (WAL mode, _busy_timeout=10000)    │
                     │                                      │
  InitDB ──────────► │  Setting, Client, Stats, Changes,   │
  (at startup)       │  Inbound, Outbound, User, Tokens…   │
                     └───────────────┬─────────────────────┘
                                     │  GetDB()
              ┌──────────────────────┼──────────────────────┐
              │                      │                       │
      StatsJob (10s)       DepleteJob (1m)        WALCheckpointJob (10m)
      ↓ SaveStats()        ↓ DepleteClients()     ↓ PRAGMA wal_checkpoint
      update up/down       disable expired         merge WAL → main file
      insert Stats rows    restart inbounds

      CheckCoreJob (5s)    DelStatsJob (daily)
      ↓ StartCore()        ↓ DelOldStats(N days)
      re-launch sing-box   delete old Stats rows
      if it crashed
```

Sources: [cronjob/cronJob.go:17-37]()

---

## Summary

S-UI stores all its configuration — proxies, clients, traffic history, admin accounts — in a single SQLite file opened with WAL mode and a tuned connection pool ([database/db.go:36-79]()). Five background jobs act as its maintenance crew: `StatsJob` keeps bandwidth counters and online-user lists current every 10 seconds; `DepleteJob` enforces quota and expiry rules every minute; `CheckCoreJob` keeps sing-box self-healing within 5 seconds of a crash; `WALCheckpointJob` prevents the WAL file from ballooning every 10 minutes; and `DelStatsJob` purges old traffic rows daily when retention is configured. Remove any one of them and a different, quiet problem accumulates — zombied quotas, dead proxies, disk bloat, or blank charts — until someone notices at the worst possible moment.