# Homesec DB Logs

> Query the HomeSec Postgres logs table to inspect recent runtime issues, counts by level/logger, and recurring error messages. Use when asked to check database logs in the telemetry DB, scan the last N hours for errors, or summarize log patterns. Enforce read-only access (SELECT only) and redact secrets. Use when this capability is needed.

- Skill: `tomevault-io/homesec-db-logs` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/homesec-db-logs`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/homesec-db-logs/raw
- Safety review: pending (external: skill-scanner FAIL, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Research & Search
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/homesec-db-logs

---


# HomeSec DB Log Queries

## Quick Start

1. Confirm the DSN and time window; use SELECT-only queries.
2. Confirm the logs table schema (from SQLAlchemy models) and time window.
3. Pull counts by level/logger, then top error/warn messages.
4. Inspect a few recent payloads to confirm fields.
5. Summarize findings; avoid dumping large payloads or secrets.

## Canonical Queries

Use psql with `-Atc` for clean output. Replace the DSN as needed.

- List tables:

```bash
psql "$DSN" -Atc "SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY table_name;"
```

- Inspect columns:

```bash
psql "$DSN" -Atc "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema='public' AND table_name='logs' ORDER BY ordinal_position;"
```

- Severity counts (logs table with JSON payload):

```bash
psql "$DSN" -Atc "SELECT payload->>'level' AS level, count(*) FROM logs WHERE ts >= now() - interval '12 hours' GROUP BY level ORDER BY count DESC;"
```

- Top error messages (logs table):

```bash
psql "$DSN" -Atc "SELECT payload->>'message' AS message, count(*) FROM logs WHERE ts >= now() - interval '12 hours' AND payload->>'level'='ERROR' GROUP BY message ORDER BY count DESC LIMIT 10;"
```

- Top warn/error by logger (logs table):

```bash
psql "$DSN" -Atc "SELECT payload->>'logger' AS logger, count(*) FROM logs WHERE ts >= now() - interval '12 hours' AND payload->>'level' IN ('ERROR','WARNING') GROUP BY logger ORDER BY count DESC;"
```

- Recent samples (logs table):

```bash
psql "$DSN" -Atc "SELECT payload FROM logs WHERE ts >= now() - interval '12 hours' ORDER BY ts DESC LIMIT 3;"
```

## Table Structure Notes

- `logs`: generic JSONB log sink (defined in `src/homesec/telemetry/db/log_table.py`).
  - Columns: `id` (bigint), `ts` (timestamptz), `payload` (jsonb).
  - Common payload keys: `ts`, `kind`, `level`, `message`, `fields`, `logger`, `module`, `pathname`, `event_type`, `camera_name`, `recording_id`.
  - Typical filters use JSONB: `payload->>'level'`, `payload->>'logger'`, `payload->>'message'`.

## Guardrails

- Use SELECT statements only. Never run INSERT/UPDATE/DELETE/DDL.
- If output contains secrets (RTSP URLs with credentials, tokens), redact before reporting.
- Keep results compact: aggregate counts first, then sample rows with LIMIT.
- State the time window explicitly (use absolute dates when summarizing).

## Interpretation Tips

- For repeated ffmpeg/ffprobe errors, check for missing flags or unsupported options.
- If warnings/errors cluster around a single logger, focus analysis there before expanding scope.

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/lan17) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-11 -->