watd

Professional-grade SaaS for WhatsApp traders and dealers — send messages, watch groups, build product/price catalogs. Market verticals include IT & PC hardware, electronics, and real estate.

Data ownership (invariant)

The platform owns four SQLite files. Everything the application creates, reads and writes lives in them:

File	Schema	Contents
watd.db	main	users, sessions, invites, ingest watermarks
prod.db	prod	durable replica of wacli's tables, plus __ingest_runs
app.db	app	catalogue, inventory, broadcasts, todo, links, AI usage
rag.db	rag	the FTS5 search index (derived, rebuildable)

The writer connection sees all four as schemas (they are ATTACHed at open); the read-only pool sees the same. archive.sqlite holds rows past the retention window. docs/DATA-MAP.md maps every table.

wacli's SQLite is read-only, always. It is touched in exactly three ways, none of which writes to it: sqlite3 <wacli.db> ".backup" to take a consistent snapshot, a plain ATTACH DATABASE <snapshot> to import it, and sqlite3 -json against a snapshot for primary-key discovery. wacli's own CLI writes its own store when we drive it (groups refresh, leave, join); that is wacli's storage, not ours.

Any future feature that wants to persist something adds a SQLite table through internal/store.EnsureTables. Nothing outside wacli ever opens wacli.db for writing.

Numbers shown in the UI

Displayed counts come from the replica (prod.db/app.db). Because ingestion replicates on an interval, the replica is deliberately behind wacli's live store, so the UI also shows the drift between the two: how far behind the replica is, rather than implying the numbers are instantaneous.

Stack

Layer	Choice
Ingestion	wacli 0.25.0 — paired WhatsApp Web device with a local SQLite mirror
Data engine	SQLite 3.53 via CGO (github.com/mattn/go-sqlite3), built with -tags sqlite_fts5 (FTS5)
Backend	Go standard library — one static binary serving the JSON API and the SPA
Frontend	Svelte 5 + SvelteKit 3 + Tailwind CSS 4 + custom shadcn-svelte components, compiled to static files
Gateway	Caddy 2.11.7 — installed and configured, currently parked (see Access)

No Python anywhere, and no Node or Python in production: Node is build-time only.

Ingestion model

wacli may be writing to its store at any moment, so a plain file copy can capture torn WAL pages. Each cycle therefore:

wacli/*.db ──(sqlite3 .backup, every 15 min)──► consistent snapshot
     snapshot ──► an in-memory staging table, every table verbatim
     staging  ──► content-hash upsert ──► durable SQLite "prod" tables

• sqlite3 .backup rather than cp, which is not crash-consistent.
• Tables are discovered from each snapshot's sqlite_master at run time, so a wacli upgrade that adds tables keeps working with no code change.
• SQLite FTS shadow tables (*_fts*) are skipped — index internals, not data.
• Identity is the snapshot's primary key; tables without one merge on the content hash. Unchanged rows are never rewritten, so a 5-minute cycle only touches what actually changed.
• Every cycle is recorded in prod.__ingest_runs (files, tables, rows, duration, error) as an audit trail.

Production tables are named <store>__<table>, e.g. prod.wacli__messages.

Topology

	Host	Role
Build	/root/watd2 (148.113.9.103)	edit, compile, test, ship
Runtime	root@148.113.8.216:20135 (aavishna2, 10.10.10.136)	Caddy + wacd + wacli + SQLite

Credentials live in vps.env. scripts/vps.sh feeds the password through SSH_ASKPASS, so it never appears on a command line or in a log.

Commands

make help          # list targets
make web           # build the SPA into frontend/build
make build         # compile bin/wacd (CGO, linux/amd64)
make dbcheck       # compile the SQLite diagnostic
make deploy        # build, publish the artifact branch, host pulls it (SSH fallback)
make deploy-check  # verify that path end to end, install nothing
make status        # service state + /api/health on the VPS
make logs          # tail the wacd journal
make ws            # interactive shell on the VPS

scripts/tunnel.sh  # expose the deployed app at http://127.0.0.1:8080

Access

The container is on a Proxmox host that owns the public IPs and answers :80/:443 itself, so only SSH (public 20135) reaches it. Until the public web ports are forwarded or fronted, use the tunnel:

scripts/tunnel.sh          # then browse http://127.0.0.1:8080

Caddy is installed, validated and parked (systemctl disable --now caddy) so it does not hold a port we cannot publish. wacd serves the API and the SPA directly on 0.0.0.0:8080. When a domain is ready:

echo 'WATD_SITE=watd.example.com' >> /etc/default/caddy
# Required: wacd ignores X-Real-IP unless it is told to trust the proxy.
# Without this every visitor shares one 10/min login budget.
echo 'WATD_TRUST_PROXY=1' >> /etc/watd/wacd.env
systemctl enable --now caddy
systemctl restart wacd

The Caddyfile proxies /api/* to 127.0.0.1:8080, which is where wacd listens.

API

Route	Auth	Purpose
POST /api/auth/login	public	credentials -> session cookie (rate limited, 429 when spent)
POST /api/auth/logout	public	delete the session server-side and clear the cookie
GET /api/auth/me	public	current user, else 401 unauthenticated
GET /api/whatsapp/status	session	linked state plus link/sync job states
POST /api/whatsapp/link/start	session	begin QR pairing (409 already_running)
POST /api/whatsapp/link/cancel	session	abandon an in-flight pairing attempt
POST /api/whatsapp/unlink	session	log the device out and clear local state
POST /api/whatsapp/sync/start	session	continuous sync (409 not_linked when unpaired)
POST /api/whatsapp/sync/stop	session	stop continuous sync
GET /api/whatsapp/events	session	SSE: qr, state, paired, error, heartbeat
GET /api/health	public	liveness + SQLite version
GET /api/stats	public	replica row counts, table count, last successful ingest
GET /api/admin/maintenance	session	nightly backup/integrity status
/*	-	static SPA with client-route fallback

Unknown /api/* paths return JSON 404 not_found; a known path with the wrong method returns JSON 405 method_not_allowed. Unknown non-/api paths fall back to the SPA so client routes deep-link correctly. Full shapes: docs/API.md.

Authentication

Single workspace; staff logins share one set of WhatsApp accounts and all data. Accounts are admin-provisioned and there is no public signup.

• Passwords use stdlib crypto/pbkdf2 (PBKDF2-HMAC-SHA256, 600k iterations, 16-byte per-user salt) and are verified in constant time. No third-party crypto dependency.
• Sessions are opaque 256-bit tokens; only their SHA-256 hash is persisted in SQLite. The cookie is watd_session, HttpOnly, SameSite=Lax, Path=/, 30 day expiry. Secure stays off unless WATD_HTTPS=1, because TLS terminates at the gateway (currently parked).
• The unknown-email path performs a PBKDF2 verification against a dummy record so response latency cannot reveal whether an account exists.
• POST /api/auth/login is rate limited per client IP. Proxy headers are ignored unless WATD_TRUST_PROXY=1, so a direct listener cannot be spoofed into handing out a fresh budget.

First admin

Set WATD_ADMIN_EMAIL and WATD_ADMIN_PASSWORD (in /etc/watd/wacd.env on the VPS) and the admin is seeded idempotently at startup. The password is never logged.

WhatsApp linking

wacli auth --events writes NDJSON lifecycle events to stderr, including the raw linking payload:

{"event":"qr_code","data":{"code":"https://wa.me/settings/linked_devices#..."}}

internal/whatsapp supervises that process, forwards each QR payload to every SSE subscriber verbatim (WhatsApp rotates the code, so each event repaints the QR), and then supervises wacli sync --follow --events with capped backoff. At most one wacli child exists at any instant and no shell is ever invoked.

Sync lifecycle (operator notes)

Capture is deliberately self-healing, because a silently stopped sync is the worst failure mode for a product that watches groups:

• On boot, if the store already holds a linked device, sync starts by itself before the HTTP listener is ready. A deploy or reboot therefore never silently stops capture.
• After pairing, sync starts automatically once the link process exits, confirmed by an authoritative wacli auth status re-probe rather than by event names alone.
• A cancelled or failed pairing starts nothing and leaves the device unlinked.
• Stop sync is honoured for the life of the process; a later restart is a fresh manager that re-reads the store and deliberately resumes capture.

Groups and contacts are refreshed nightly by watd-refresh.timer (02:30 Asia/Kolkata); see refresh-store.sh.

Local development

cd frontend && npm run dev     # http://127.0.0.1:5173, proxies /api to :8787
./bin/wacd -db data/watd.db -web frontend/build       # app/prod/rag beside it
./bin/wacd -ingest-once -wacli-store /path/to/store   # one replication cycle
./bin/dbcheck -db data/watd.db -q 'SELECT 1'          # ad-hoc SQL

Layout

main.go                  entry point: HTTP server, route table, ingest worker
internal/api/             handlers, login rate limiter, JSON 404/405 fallbacks
internal/auth/            PBKDF2 hashing, SQLite sessions, RequireUser middleware
internal/whatsapp/        wacli supervision, NDJSON events, SSE fan-out
internal/mail/            AgentMail transport for onboarding email
internal/store/           SQLite files, pools, schema/constraint repair, backups
internal/maintenance/     nightly VACUUM INTO backups + integrity check
internal/dmarchive/       daily DM PDFs, WeasyPrint, Google Drive device flow
internal/ingest/          snapshot, staging load, content-hash upsert
cmd/dbcheck/              SQLite diagnostic and SQL runner
frontend/                 SvelteKit project; build/ is the static bundle
frontend/src/routes/login/     sign-in page
frontend/src/routes/whatsapp/  QR linking + sync controls
scripts/                  vps.sh, vps-cp.sh, tunnel.sh, provision-vps.sh, deploy.sh, go.sh,
                          deploy-artifacts.sh, publish-deploy.sh, remote-deploy.sh,
                          install-wacli.sh, wacli-rehearse.sh, upgrade-wacli.sh
systemd/wacd.service      hardened unit for the API + ingest worker
docs/DM-ARCHIVE.md        daily DM PDFs, Drive layout, Google setup
docs/API.md               frozen API contract
docs/DATA-MAP.md          every database, schema, data flow and where the indexes live
Caddyfile                 gateway, {$WATD_SITE} drives auto-SSL

Sandboxed development

The DSH workspace-write sandbox makes everything outside the workspace (plus /tmp) read-only, which breaks tools that cache into $HOME. Both are handled:

Tool	Default (blocked)	Project setting
Go build cache	/root/.cache/go-build	GOCACHE=$(CURDIR)/.cache/go-build exported by the Makefile; use scripts/go.sh for ad-hoc commands
npm cache	/root/.npm	frontend/.npmrc -> /root/watd2/.npm-cache

Use make build / make dbcheck, or scripts/go.sh vet ./... for anything else. A bare go build or go vet will fail with read-only file system.

Still outside the workspace: the Go module cache (/root/go/pkg/mod). Adding a new Go dependency therefore needs one-off wider access; everything already in go.mod builds and tests normally.

Market intelligence (planned)

The offer-extraction layer (app.offers) is the foundation for a market-intelligence product beyond the chat box. Four additions are specced in .scratch/market-intelligence/spec.md:

1. Hybrid semantic retrieval - wire the existing ai.Embedder into a rag.offer_vectors index and rerank FTS5 hits by cosine, so fuzzy queries ("cheap gaming PC under 20k") find offers keyword search misses.
2. Buy-side / sell-side match - cross want-offers against sell-offers for market depth, arbitrage, and "who is asking for X" leads.
3. Offer/price alerts - reuse the alert scan and broadcast delivery for "new 3060 under 9k" and "nvme down 8% this week".
4. Catalogue + counterparty view - a browsable screen over app.offers with price bands by condition and repost/staleness signals.

Status: needs-triage - see the spec for the contract, non-goals and open questions.

Status

Working and verified end to end:

• wacd + SQLite + wacli running under systemd on the VPS; health reports the SQLite version and the CGO binary is built here and verified running there.
• Login, session cookies, logout, and the idempotent first-admin seed; the login route is rate limited and proxy headers are not trusted by default.
• WhatsApp section: link device -> live QR over SSE -> paired -> sync start/stop.
• SPA served with security headers, compression and client-route fallback.
• Ingestion: changed rows rewritten, unchanged rows untouched, inserts merged, repeat runs idempotent, and run history recorded.
• session.db (linked-device identity and private keys) is never replicated into the replica; skips are logged.

Two independent review gates (auth core, HTTP integration) both returned pass, and every claim above was re-checked by the captain with the built binary.

Not yet done: AgentMail onboarding wiring (the transport is built and tested but the credential-delivery flow is undecided), group watching, catalog, and sending. The catalogue and RAG extensions are specced in .scratch/market-intelligence/spec.md.