How to Monitor EctoSQLite3 with Vigilmon

EctoSQLite3 is the SQLite adapter for Ecto, enabling zero-configuration database deployments for Elixir applications. It's ideal for embedded systems, local apps, single-tenant SaaS, and development environments — no external database server required. But SQLite has its own failure modes: WAL corruption, lock contention under concurrent writes, database file permission errors, and disk space exhaustion can all silently degrade your app while Ecto reports nothing.

External monitoring catches what EctoSQLite3 can't self-report. This tutorial covers:

• A health endpoint that validates EctoSQLite3 connectivity, WAL state, and disk space
• HTTP uptime monitoring with Vigilmon
• Heartbeat monitoring for background database tasks
• Slack alerts and a status page

Step 1: Add EctoSQLite3 to your project

# mix.exs
defp deps do
  [
    {:ecto_sql, "~> 3.11"},
    {:ecto_sqlite3, "~> 0.17"},
    {:req, "~> 0.4"}   # for heartbeat pings
  ]
end

Configure your repo:

# config/config.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.SQLite3,
  database: "priv/repo/my_app.db",
  pool_size: 5,
  # SQLite WAL mode improves concurrent read performance
  journal_mode: :wal,
  cache_size: -64_000,   # 64 MB page cache
  temp_store: :memory,
  busy_timeout: 5_000

Define your repo module:

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.SQLite3
end

Run migrations:

mix ecto.create
mix ecto.migrate

Step 2: Build a health endpoint for EctoSQLite3

SQLite has failure modes beyond standard SQL connectivity checks. Build a health plug that covers the SQLite-specific risks:

# lib/my_app_web/plugs/health_check.ex
defmodule MyAppWeb.Plugs.HealthCheck do
  import Plug.Conn
  require Logger

  def init(opts), do: opts

  def call(%Plug.Conn{request_path: "/health"} = conn, _opts) do
    checks = run_checks()
    status = if checks.status == "ok", do: 200, else: 503

    conn
    |> put_resp_content_type("application/json")
    |> send_resp(status, Jason.encode!(checks))
    |> halt()
  end

  def call(conn, _opts), do: conn

  defp run_checks do
    db = database_check()
    disk = disk_check()

    overall = db.status == "ok" and disk.status == "ok"

    %{
      status: if(overall, do: "ok", else: "degraded"),
      database: db,
      disk: disk
    }
  end

  defp database_check do
    start = System.monotonic_time(:millisecond)

    result =
      case Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1", [], timeout: 3_000) do
        {:ok, _} ->
          elapsed = System.monotonic_time(:millisecond) - start
          wal = wal_check()
          integrity = integrity_check()

          %{
            status: if(wal.status == "ok" and integrity.status == "ok", do: "ok", else: "warning"),
            latency_ms: elapsed,
            wal: wal,
            integrity: integrity
          }

        {:error, reason} ->
          Logger.error("[EctoSQLite3] health check failed: #{inspect(reason)}")
          %{status: "error", reason: inspect(reason)}
      end

    result
  end

  defp wal_check do
    # PRAGMA wal_checkpoint returns (busy, log, checkpointed) pages
    case Ecto.Adapters.SQL.query(MyApp.Repo, "PRAGMA wal_checkpoint(PASSIVE)", [], timeout: 5_000) do
      {:ok, %{rows: [[busy, log, checkpointed]]}} ->
        %{
          status: if(busy == 0, do: "ok", else: "busy"),
          busy_pages: busy,
          log_pages: log,
          checkpointed_pages: checkpointed
        }

      {:error, reason} ->
        %{status: "error", reason: inspect(reason)}
    end
  end

  defp integrity_check do
    # Quick integrity check — fast even on large databases
    case Ecto.Adapters.SQL.query(MyApp.Repo, "PRAGMA quick_check", [], timeout: 10_000) do
      {:ok, %{rows: [["ok"]]}} ->
        %{status: "ok"}

      {:ok, %{rows: rows}} ->
        issues = Enum.map(rows, fn [msg] -> msg end)
        Logger.error("[EctoSQLite3] integrity issues: #{inspect(issues)}")
        %{status: "error", issues: issues}

      {:error, reason} ->
        %{status: "error", reason: inspect(reason)}
    end
  end

  defp disk_check do
    db_path = Application.get_env(:my_app, MyApp.Repo)[:database] || "priv/repo/my_app.db"

    case File.stat(db_path) do
      {:ok, %{size: size}} ->
        # Warn when database exceeds 4GB (SQLite default page limit)
        status = if size < 4_000_000_000, do: "ok", else: "warning"
        %{status: status, size_bytes: size, path: db_path}

      {:error, reason} ->
        %{status: "error", reason: inspect(reason), path: db_path}
    end
  end
end

Register the plug before the router:

# lib/my_app_web/endpoint.ex
plug MyAppWeb.Plugs.HealthCheck
plug MyAppWeb.Router

Test it:

mix phx.server
curl http://localhost:4000/health | jq .
# {
#   "status": "ok",
#   "database": {
#     "status": "ok",
#     "latency_ms": 2,
#     "wal": {"status": "ok", "busy_pages": 0, "log_pages": 12, "checkpointed_pages": 12},
#     "integrity": {"status": "ok"}
#   },
#   "disk": {"status": "ok", "size_bytes": 1048576, "path": "priv/repo/my_app.db"}
# }

Step 3: Capture slow queries with Ecto telemetry

# lib/my_app/telemetry.ex
defmodule MyApp.Telemetry do
  require Logger

  @slow_query_threshold_ms 100   # SQLite should be fast; 100ms is already slow

  def setup do
    :telemetry.attach(
      "ecto-sqlite3-slow-query-logger",
      [:my_app, :repo, :query],
      &handle_query/4,
      nil
    )
  end

  def handle_query(_event, %{total_time: total_time}, %{query: query, source: source}, _config) do
    duration_ms = System.convert_time_unit(total_time, :native, :millisecond)

    if duration_ms >= @slow_query_threshold_ms do
      Logger.warning(
        "[EctoSQLite3 slow query] #{duration_ms}ms — source=#{source} query=#{String.slice(query, 0, 200)}"
      )
    end
  end
end

Call MyApp.Telemetry.setup() in your application.ex:

# lib/my_app/application.ex
def start(_type, _args) do
  MyApp.Telemetry.setup()
  # ... rest of supervision tree
end

SQLite is typically sub-millisecond for indexed reads. A query taking 100ms suggests a missing index or lock contention — catch it early.

Step 4: Monitor the health endpoint with Vigilmon

1. Sign up at vigilmon.online
2. Click New Monitor → HTTP
3. Enter https://yourdomain.com/health
4. Set check interval: 1 minute
5. Enable keyword check: body must contain "status":"ok"
6. Save

The keyword check matters here: SQLite lock contention or WAL checkpoint issues return HTTP 200 with "status":"degraded". The keyword match catches database degradation before users experience query failures.

Step 5: Heartbeat monitoring for background database tasks

If you run periodic jobs that use your SQLite repo — cleanups, archival, VACUUM operations — use a heartbeat to confirm they ran:

# lib/my_app/workers/db_maintenance_worker.ex
defmodule MyApp.Workers.DbMaintenanceWorker do
  use GenServer
  require Logger

  # Run VACUUM weekly; SQLite WAL grows without periodic maintenance
  @interval :timer.hours(24)

  def start_link(_), do: GenServer.start_link(__MODULE__, %{}, name: __MODULE__)

  def init(state) do
    schedule()
    {:ok, state}
  end

  def handle_info(:maintain, state) do
    run_maintenance()
    schedule()
    {:noreply, state}
  end

  defp run_maintenance do
    # ANALYZE updates query planner statistics
    case Ecto.Adapters.SQL.query(MyApp.Repo, "PRAGMA wal_checkpoint(TRUNCATE)", [], timeout: 30_000) do
      {:ok, _} ->
        Logger.info("[DbMaintenanceWorker] WAL checkpoint complete")
        ping_vigilmon()

      {:error, reason} ->
        Logger.error("[DbMaintenanceWorker] WAL checkpoint failed: #{inspect(reason)}")
    end
  end

  defp ping_vigilmon do
    url = Application.get_env(:my_app, :vigilmon)[:heartbeat_url]

    if url do
      case Req.get(url, receive_timeout: 5_000) do
        {:ok, %{status: 200}} -> :ok
        err -> Logger.warning("[DbMaintenanceWorker] heartbeat ping failed: #{inspect(err)}")
      end
    end
  end

  defp schedule, do: Process.send_after(self(), :maintain, @interval)
end

In Vigilmon, create a Heartbeat Monitor:

1. Click New Monitor → Heartbeat
2. Set expected interval: 26 hours (giving a 2-hour buffer for the 24-hour job)
3. Copy the ping URL
4. Set it as VIGILMON_HEARTBEAT_URL in your environment

If maintenance fails, the WAL file grows unbounded and eventually locks out writers. The heartbeat catches this before it becomes a production incident.

Step 6: Expose migration state in the health check

defp run_checks do
  db = database_check()
  disk = disk_check()
  migrations = migration_check()

  overall = db.status == "ok" and disk.status == "ok" and migrations.status in ["ok", "unknown"]

  %{
    status: if(overall, do: "ok", else: "degraded"),
    database: db,
    disk: disk,
    migrations: migrations
  }
end

defp migration_check do
  case Ecto.Migrator.migrations(MyApp.Repo) do
    migrations when is_list(migrations) ->
      pending = Enum.count(migrations, fn {status, _, _} -> status == :down end)
      %{status: if(pending == 0, do: "ok", else: "warning"), pending: pending}

    _ ->
      %{status: "unknown"}
  end
end

Step 7: Slack alerts and status page

Slack alerts:

1. In Vigilmon, go to Notifications → New Channel → Slack
2. Paste your Slack incoming webhook URL
3. Enable the channel on your EctoSQLite3 health monitor and heartbeat monitor

When the database goes down or the WAL locks:

🔴 DOWN: yourdomain.com/health
Keyword "status":"ok" not found
Detected: US-West
1 minute ago

When the maintenance heartbeat misses:

🔴 HEARTBEAT MISSED: SQLite DB Maintenance
Expected ping within 26 hours — none received
Last seen: 28 hours ago

Status page:

1. Go to Status Pages → New Status Page
2. Add your HTTP monitor and heartbeat monitor
3. Share the URL with your team

What you've built

| What | How | |------|-----| | Health endpoint | Plug with EctoSQLite3 ping + WAL + integrity checks | | Keyword check | Vigilmon keyword match on "status":"ok" | | Uptime monitoring | Vigilmon HTTP monitor → /health | | WAL monitoring | PRAGMA wal_checkpoint busy page count | | Integrity check | PRAGMA quick_check on every health poll | | Disk space check | File.stat on the database file | | Slow query detection | Ecto telemetry handler with 100ms threshold | | Maintenance monitoring | Heartbeat GenServer + Vigilmon heartbeat monitor | | Slack alerts | Vigilmon Slack notification channel | | Status page | Vigilmon public status page |

EctoSQLite3 gives you a zero-config database. Vigilmon tells you when that database needs attention before your users do.

Next steps

• Set a Vigilmon alert when disk.size_bytes approaches a threshold — SQLite has a 281TB theoretical limit but disk space is finite
• Monitor WAL checkpoint busy pages — consistently non-zero busy pages indicate write contention worth investigating
• Use Vigilmon's response time history to catch gradual latency increases that signal missing indexes as data grows

Get started free at vigilmon.online.