# Query Logs SQLite App

A DNS App for Technitium DNS Server that logs DNS queries to a SQLite database.

## Overview

- **Async logging** – writes log entries through a bounded queue
- **Cleanup support** – prunes old records by age/count
- **Optional in-memory mode** – can use an in-memory database
- **Vacuum support** – can vacuum after cleanup when enabled

## Integration / extension points

- Implements: `IDnsApplication`, `IDnsQueryLogger`, `IDnsQueryLogs`
- Runs as a DNS query logger with asynchronous persistence.

## Database

The database table stores values for some fields in numeric format. The fields and the values are described as below.

### Protocol Field

| Protocol | Value | Description |
| 0 | UDP | The standard DNS over UDP protocol |
| 1 | TCP | The standard DNS over TCP protocol |
| 2 | TLS | DNS-over-TLS RFC 7858 |
| 3 | HTTPS | DNS-over-HTTPS RFC 8484 |
| 5 | QUIC | DNS-over-QUIC RFC 9250 |
| 253 | UdpProxy | PROXY Protocol over UDP |
| 254 | TcpProxy | PROXY Protocol over TCP |

### Response Type Field

| Response Type | Value | Description |
| 1 | Authoritative | Response generated by the DNS server itself | 
| 2 | Recursive | Response received from a recursive query to upstream | 
| 3 | Cached | Response generated by DNS server's cache | 
| 4 | Blocked | Response generated by DNS server to block a request |
| 5 | UpstreamBlocked | Response received from an upstream blocking a request |
| 6 | UpstreamBlockedCached | Response generated by DNS server's cache containing blocked response from upstream |
| 7 | Dropped | A `null` response generated by the DNS server indicating that the request was dropped |

## Configuration

`dnsApp.config` contains these keys:

| Property | Type | Default | Description |
| --- | --- | --- | --- |
| `enableLogging` | boolean | `true` | Enables or disables logging. |
| `maxQueueSize` | number | `200000` | Size of the bounded log queue. |
| `maxLogDays` | number | `0` | Maximum age of retained logs (days). `0` disables age-based cleanup. |
| `maxLogRecords` | number | `0` | Maximum number of retained log records. `0` disables count-based cleanup. |
| `enableVacuum` | boolean | `false` | Runs `VACUUM` after cleanup when records were deleted. |
| `useInMemoryDb` | boolean | `false` | Uses an in-memory SQLite database. |
| `sqliteDbPath` | string | `querylogs.db` | Path to the SQLite database file. |
| `connectionString` | string | `Data Source='{sqliteDbPath}'; Cache=Shared;` | SQLite connection string template. |

The example below matches the shipped `dnsApp.config` values, which enable 7-day and 10,000-record cleanup by default in the distributed package.

### Example

```json
{
  "enableLogging": true,
  "maxQueueSize": 200000,
  "maxLogDays": 7,
  "maxLogRecords": 10000,
  "enableVacuum": false,
  "useInMemoryDb": false,
  "sqliteDbPath": "querylogs.db",
  "connectionString": "Data Source='{sqliteDbPath}'; Cache=Shared;"
}
```

## Runtime behavior

1. Queries are buffered in a bounded channel.
2. A background consumer thread bulk inserts records into SQLite.
3. A periodic cleanup timer removes old records.
4. If `enableVacuum` is enabled and cleanup deleted records, the database is vacuumed.

## Risks / operational notes

- Queue overflow drops writes (`DropWrite` behavior).
- SQLite write contention can affect high traffic deployments.
- In-memory mode does not persist across restarts.

## Troubleshooting

- Confirm the database path is writable.
- Confirm `connectionString` still contains the `{sqliteDbPath}` token.
- Check server logs for SQLite errors if logging stops.