# Go + Echo + SQLite (Embedded, Self-Hosted Tools) — AI Agent Guidelines & Architecture Rules

> Architecture guidelines for single-binary Go services using the Echo framework, embedded SQLite, and minimal-JS server-rendered UI.
> Technologies: Go, Echo, SQLite, Docker

---

## AGENTS.md
```markdown
# Project Architecture & Guidelines (Go + Echo + SQLite)

## 1. System Architecture
- **Language**: Go 1.22+, compiled to a single static binary with no runtime dependencies.
- **Web Framework**: Echo (lightweight HTTP router and middleware, `github.com/labstack/echo/v4`).
- **Database**: Embedded SQLite (`modernc.org/sqlite` or `mattn/go-sqlite3`), file-based, no separate database server to deploy or back up.
- **Frontend**: Server-rendered HTML via Go's `html/template` or a light templating library, with HTMX or minimal vanilla JS for interactivity — no separate SPA build.

## 2. Single-Binary Deployment Model
- Embed static assets (templates, CSS, migrations) into the binary with Go's `embed.FS` so deployment is copying one file, not a directory tree.
- Store the SQLite database file in a configurable data directory (`--data-dir` flag or `DATA_DIR` env var), never hardcoded, so it's easy to volume-mount in Docker.
- Enable SQLite's WAL (write-ahead log) mode at startup (`PRAGMA journal_mode=WAL;`) for concurrent read/write access without locking the whole database on every write.

## 3. Echo Handler & Middleware Conventions
- Group routes by resource with `e.Group("/api/projects")`; apply auth middleware at the group level, not per-handler.
- Handlers return typed errors that a central `HTTPErrorHandler` maps to status codes — never `c.String(500, err.Error())` scattered across handlers, which leaks internal error detail to clients.
- Use Echo's built-in request validation (`c.Bind()` + a `Validate()` method backed by `go-playground/validator`) before touching the database.

## 4. Database Access & Migrations
- Use a lightweight migration tool (`golang-migrate` or a hand-rolled versioned-SQL-files runner) that ships embedded in the binary and runs automatically on startup — self-hosted single-binary tools can't assume an operator will run a separate migration command.
- Wrap multi-statement writes in an explicit `sql.Tx`; SQLite's single-writer model makes uncommitted long-lived transactions a common source of "database is locked" errors.
- Keep the database package (`internal/db`) free of HTTP concerns — handlers call typed repository methods, not raw SQL.

## 5. Common Pitfalls / Coding Standards
- ❌ Leaving SQLite in the default `journal_mode=DELETE`, which serializes all writes and causes "database is locked" under any concurrent load.
- ❌ Hardcoding file paths instead of reading from a configurable data directory, breaking Docker volume mounts.
- ✅ Set `busy_timeout` on the SQLite connection so concurrent writers retry briefly instead of failing immediately.

## 6. Testing Conventions
- Standard `testing` package with table-driven tests; use an in-memory SQLite database (`:memory:`) per test for isolation and speed.
- `httptest` for handler-level tests against the Echo instance without a real network listener.
- Run `go vet` and `staticcheck` in CI; Go's compiler catches most type errors, so linting focuses on correctness patterns instead.

## 7. Git Workflow & PR Conventions
- Conventional Commits scoped to the package, e.g. `fix(db): use WAL mode to avoid write locking under load`.
- New migrations ship in the same PR as the schema change; migration files are numbered and never edited after merge.
- Require `go build ./...`, `go vet ./...`, and `go test ./...` green before merge.
- Squash-merge; tag releases so the single-binary artifact is reproducible from a known commit.
```

---

## CLAUDE.md
```markdown
# CLAUDE.md — Go + Echo + SQLite

Refer to @AGENTS.md for complete database configuration and architectural constraints.

## Common Commands
- `go run ./cmd/server` - Start the server locally
- `go build -o bin/app ./cmd/server` - Build the single static binary
- `go test ./...` - Run the full test suite
- `go vet ./...` - Run Go's static analysis checks

## Claude Specific Directives
- Keep the SQLite database path configurable via flag/env var; never hardcode it.
- Always wrap multi-statement writes in an explicit transaction given SQLite's single-writer model.
```

---

## .cursor/rules/stack.mdc
```markdown
---
description: Go + Echo + SQLite architecture rules
globs: ["**/*.go"]
alwaysApply: true
---

# Go + Echo + SQLite

- Go compiled to a single static binary; Echo for HTTP routing/middleware; embedded SQLite with WAL mode enabled.
- Static assets embedded via `embed.FS`; database path always configurable, never hardcoded, for Docker volume mounts.
- Migrations run automatically on startup from embedded SQL files — no separate migration command assumed.
- Wrap multi-statement writes in an explicit `sql.Tx`; set `busy_timeout` to avoid "database is locked" under concurrent writes.
- Handlers call typed repository methods in `internal/db`, never raw SQL inline; central `HTTPErrorHandler` maps errors to status codes.
- Validate `c.Bind()` input with `go-playground/validator` before it reaches the database.
```

---

## Architecture Overview & Best Practices
## Architecture Overview

Standardized production guidelines for **Go**, the **Echo** web framework, and **embedded SQLite** — the pattern behind small, single-binary, self-hosted tools that a user can run with one command and no external database server.

### Verified Real-World Adoption

**PocketBase** is the canonical example of this exact stack (Go + Echo + embedded SQLite, single binary). **Gitea** and **Gatus** follow the same single-binary, embeddable-SQLite philosophy, offering PostgreSQL as an opt-in upgrade path for larger deployments rather than a requirement.

### Key Architectural Nuances

- **Zero External Dependencies by Default**: The database ships inside the same process as the application, so "installing" the tool is copying one binary and pointing it at a data directory.
- **WAL Mode as a Non-Negotiable Default**: Without it, SQLite's exclusive write lock turns any concurrent access pattern into a source of "database is locked" errors under real usage.