plan.md
Rama main · réplica de lectura del repositorio original.
# applytrack → open-source, multi-tenant, self-hostable (.NET 10 API + Python poller)
## Status / what changed (revised 2026-06-07)
This plan was originally a **commercial** multi-tenant SaaS, all-Python (FastAPI +
SQLAlchemy). Revised per CryptoJones into an **open-source, self-hostable** app
with a **polyglot** backend:
- **Open-source, not commercial.** License → **Apache-2.0** (same as the original
applytrack — no relicense). **No Stripe / billing.**
- **Core API → .NET 10** — ASP.NET Core Minimal APIs on Kestrel. (".NET Core" is
just **.NET** since v5; latest LTS is .NET 10, Nov 2025.)
- **Discovery poller stays Python** (polyglot). The existing `poll.py` — 8 source
fetchers + HTML scraping + `classify()` scoring/dedup — runs as a cron worker
against the **same Postgres**. We do **not** rewrite the fetchers in C#.
- **Data layer → Dapper + Npgsql** in .NET: hand-written SQL mapped to records,
migrations via **DbUp** (embedded, idempotent `.sql` scripts), manual
`version`-column optimistic locking. (No EF Core.)
- **Accessible build-free UI.** The vanilla-JS SPA is served directly from
`wwwroot/`, targets WCAG 2.2 AA, and has automated Playwright/axe coverage.
The .NET API preserves the exact endpoint URLs and JSON request/response shapes
the SPA calls, including `?expected_version=` and the 409 conflict flow.
- **Multi-tenant stays** (self-hostable, multi-user). Only monetization is dropped.
- AI cover-letter/materials engine — was deferred past v1; **landed post-v1** as an
optional module (provider-agnostic OpenAI-compatible drafting, per-tenant encrypted
keys, structured résumé, Markdown output). See Step 6. LaTeX/PDF résumé is the
remaining future work.
## Architecture
```
┌─────────────────────────────────┐
Browser ──► │ ASP.NET Core (.NET 10, Kestrel) │
(SPA, │ • serves wwwroot/ = the SPA │
UNCHANGED)│ verbatim │
│ • JSON API (Dapper + Npgsql) │──┐
│ • magic-link auth, server-side │ │
│ sessions, tenancy choke-point │ ├──► Postgres
└─────────────────────────────────┘ │ (shared schema =
┌─────────────────────────────────┐ │ the cross-runtime
Cron ───► │ Python poller (worker.py) │──┘ contract)
│ • run_poll() per tenant │
│ • psycopg3 direct to DB │
│ • reuses all 8 fetchers as-is │
└─────────────────────────────────┘
```
**Two runtimes, one database.** The Postgres **schema is the contract** between
the .NET API and the Python poller — neither process calls the other. The .NET
side owns auth/sessions and CRUD; the Python side reads profiles/seen/users and
writes new leads. Both **unconditionally filter `WHERE tenant_id`**.
Ownership split:
- **.NET writes/owns:** `users`, `magic_tokens`, `sessions`; CRUD on
`applications`; edits to `search_profiles` and `blacklist`.
- **Python writes:** new rows in `applications` (leads), `seen` entries.
- **Python reads:** `users` (active list), `search_profiles`, `seen`, `blacklist`.
- **Shared tables:** `applications`, `search_profiles`, `seen`, `blacklist`.
## Repo layout after the split
```
api/ # new .NET solution (ApplyTrack.Api)
ApplyTrack.Api.csproj
Program.cs # Minimal API wireup, DI, static files
Endpoints/*.cs # apps, criteria, blacklist, auth, account
Data/Repo.cs # ApplicationRepo (Dapper), tenant-scoped
Data/Slug.cs # safe_name → slug validation choke-point
Auth/*.cs # magic-link, sessions, tenant provider
Migrations/*.sql # DbUp scripts 0001_…, 0002_…
wwwroot/ # the existing SPA, moved verbatim
ApplyTrack.Api.Tests/ # xUnit + Testcontainers-Postgres
src/applytrack/ # Python, trimmed to the poller + CLI
poll.py worker.py db.py criteria.py linkcheck.py cli.py
(store.py/web/ removed once .NET owns the API)
```
## Cross-cutting: license = Apache-2.0 (set up alongside Step 0)
- The project **stays Apache-2.0** (same as the original applytrack) — no
relicense. Ship a full `LICENSE` (Apache-2.0 text) at the repo root; set
`[project] license = {text="Apache-2.0"}` in `pyproject.toml`;
`<PackageLicenseExpression>Apache-2.0</PackageLicenseExpression>` in the `.csproj`.
- Every source file (Python + the new C#) carries
`SPDX-License-Identifier: Apache-2.0` and `Copyright 2026 Aaron K. Clark`.
## Build order (each step independently shippable)
### Step 0 — Test scaffolding + repo split + license setup (do first)
- **Python side already has 27 tests** (criteria/poll/fetchers) — keep them; they
guard the poller through the split. Preserve the `run_poll(listings=)` offline
seam.
- **New .NET test project:** xUnit + **Testcontainers-Postgres** (disposable PG
per test run), DbUp `upgrade` in a fixture, truncate between tests.
- Stand up the empty .NET solution + DbUp migration runner so Step 1 has a home.
- Add the Apache-2.0 `LICENSE` + license metadata here while the file set is small.
### Step 1 — .NET API skeleton + Postgres storage ⭐ (start here; single-user, no auth yet)
Goal: the existing SPA loads and works **identically**, now served by .NET off
Postgres, with a hardcoded bootstrap `tenant_id = 1` (auth lands in Step 2).
- **`applications` table** (DbUp `0001_applications.sql`):
```
id bigserial PK, tenant_id bigint NOT NULL,
name text NOT NULL, -- the slug, public key within tenant
company/role/lane/status/link/location/salary/source/contact/contact_email text,
applied/followup/created/score text, -- keep the SPA's free-text shape
notes text, version bigint NOT NULL DEFAULT 1,
created_at/updated_at timestamptz DEFAULT now(),
UNIQUE (tenant_id, name), INDEX (tenant_id, status)
```
`applied/followup/created/score` stay **text** at the column edge to match the
SPA's free-text fields — real `date`/`int` is cleaner but ripples into the SPA;
defer.
- **`ApplicationRepo(IDbConnection conn, long tenantId)`** (Dapper) replaces the
Python `AppStore`. Same logical methods: `Create/Update/Delete/Read/ReadFields/
List/Stats/Version`. Every query **unconditionally** includes
`WHERE tenant_id = @tenantId`.
- **Slug choke-point** (`Data/Slug.cs`): the C# heir to `safe_name` — validates/
normalizes the slug (reject empty/`.`/`..`/separators). Single naming control.
- **Optimistic lock — same contract, new mechanism.** `Update(..., expectedVersion)`
→ `UPDATE applications SET …, version = version + 1 WHERE id = @id AND
tenant_id = @t AND version = @expected`; **0 rows affected → 409 Conflict**.
The SPA's `?expected_version=` param + `saveWithConflict` 409-confirm flow keep
working unchanged (version stays opaque to the SPA).
- `List`/`Stats` are plain SQL (`GROUP BY status` for stats).
- **Minimal API endpoints** (`Endpoints/Apps.cs`) — **byte-for-byte the URLs/shapes
the SPA calls:** `GET /api/apps`, `GET/PUT /api/apps/{name}`,
`GET/PUT /api/apps/{name}/raw`, `GET /api/stats`, plus `GET/PUT /api/criteria`
and `GET/POST/DELETE /api/blacklist`. Verify each against `app.js` before moving
on.
- `.../raw` round-trips markdown: render a row → the same frontmatter+body text
the SPA edits. (Port the tiny render/parse codec to C#, or keep markdown
round-trip Python-side via the import/export CLI and have `.raw` compose from
columns — choose the C# port; it's ~40 lines and keeps the API self-contained.)
- **Serve the SPA:** drop `web/static/*` into `wwwroot/`; `app.UseDefaultFiles()` +
`app.UseStaticFiles()`. No SPA edits.
- **`criteria`/`blacklist` tables** now (read by Python in Steps 3–4):
`search_profiles(tenant_id, keywords jsonb, sources jsonb, min_score int,
remote_only bool, exclude_locations jsonb, default_lane text, active bool)` and
`blacklist(tenant_id, company text, PK(tenant_id, company))`. `/api/criteria`
edits the one profile row; `/api/blacklist` edits the table. These replace
`.criteria.json` / `.blacklist.json`.
- **Migration of existing data — stays Python.** Keep an `applytrack import-md
--dir applications --tenant 1` CLI that reuses the existing `parse_app` to upsert
the live Markdown into Postgres (psycopg3). The Markdown codec already exists in
Python; no reason to reimplement it in C# for a one-shot.
- **Materials (post-v1):** `/api/apps/{name}/draft` now drafts a cover letter via the
configured LLM and persists it (Step 6); the SPA's Generate button is live. The
check-link endpoint remains the only `501`. On-demand `/api/poll` — see Step 4.
- **Tests (.NET):** repo CRUD, optimistic-lock 409, list/stats, endpoint-shape
contract tests; **(Python):** existing 27 stay green.
- **Milestone:** identical app, on Postgres, served by .NET. Ship this first.
### Step 2 — Auth + tenancy spine (.NET)
- Migrations: `users(id, email citext UNIQUE, status, created_at)`,
`magic_tokens(user_id, token_sha256, expires_at, used_at)`,
`sessions(id opaque, user_id, expires_at)`. `tenant_id == user.id` for v1 (the
name future-proofs orgs).
- **Single tenancy choke-point** (`Auth/TenantProvider.cs`): resolves the current
tenant from the session cookie — the *only* place a `tenant_id` enters the
system. DI hands endpoints a **pre-scoped `ApplicationRepo`**; endpoints never
see `tenant_id`, never hold a raw connection. (Optional belt-and-suspenders:
Postgres RLS via `SET LOCAL app.tenant_id`; repo discipline is the primary
control and ships on its own.)
- **Magic-link** (`Endpoints/Auth.cs` + an `IEmailSender`):
`POST /api/auth/request {email}` (upsert user, store **sha256** of token,
email link, **always 200** to prevent enumeration, rate-limit),
`GET /api/auth/verify?token=` (validate unexpired+unused, mint **opaque
server-side session**, set cookie, redirect to `/` stripping the token),
`POST /api/auth/logout`, `GET /api/auth/me`. Server-side sessions (not JWT) →
instant revocation.
- **Cookie:** `HttpOnly; Secure; SameSite=Lax; Path=/`. CSRF defense =
`SameSite=Lax` + JSON-only `fetch` mutations; no separate CSRF token in v1
(document it).
- **Email:** lean — SMTP via `MailKit` or a transactional HTTP provider; in dev,
print the link to stderr/console.
- **SPA:** the *only* additive change to the kept UI — `api()` handles `401` →
small login view (~40 LOC, no framework); `boot()` calls `/api/auth/me`; the 5s
poll loop bounces to login on 401. The 8 data endpoints keep their shapes, so
this is genuinely the only SPA delta in the whole plan.
- **Tests:** request→verify→session happy path; expired/used token rejection;
**mandatory cross-tenant isolation test** (user A cannot touch user B's `name`).
### Step 3 — Per-user search profiles (Python poller reads them)
- `search_profiles` already exists (Step 1). Now the **Python poller consumes it**
instead of hardcoded constants.
- **`poll.py`:** `DEFAULT_KEYWORDS`/`min_fit_score` become **defaults seeded on
signup** (a row written by .NET at user creation), not the live source.
`classify(title, desc, keywords)` already takes keywords ✓. `run_poll(...)` →
`run_poll(repo, profile, *, fetchers=None, listings=None, verify_links=None)`.
**`listings=`/`fetchers=` seams preserved verbatim** so the 27 offline tests pass
a constructed `profile` + fixed `listings`, no network/DB.
- **`db.py` (Python, psycopg3):** thin tenant-scoped reader/writer mirroring the
.NET schema — load a profile, insert leads, all `WHERE tenant_id`. This is the
Python half of the shared-schema contract.
- Materials wiring stays out of `run_poll` (new leads → `status="lead"`).
- Profile editing is the existing `/api/criteria` panel (no new UI).
- **Tests:** `classify` with custom keywords (have it); `run_poll` honoring
`min_score`/`sources` offline (have it) — re-point fixtures at a `profile`.
### Step 4 — Per-tenant cron + `seen` table (Python)
- Migration `seen(tenant_id, kind ('url'|'slug'), key, PK(tenant_id,kind,key))`
replacing `.seen.json`. The `Seen` adapter keeps the **same `has`/`add`
interface** so `run_poll`'s loop body is untouched; `_norm_url`/`_norm_slug`
unchanged. `_seed_from_existing(repo, seen)` via
`SELECT link, company, role WHERE tenant_id`.
- **`worker.py`:** `run_all_tenants()` selects active users, builds a per-tenant
repo+profile **inside** the loop (preserving `WHERE tenant_id` — cron does **not**
bypass the choke-point), runs `run_poll`, commits, isolates per-tenant failures.
The one privileged query (`SELECT id FROM users WHERE status='active'`) lives
here.
- **Fetch-once optimization (key risk):** public boards (esp. RemoteOK) rate-limit
a single IP. Gather each source **once per run**, then run classify/dedup/insert
**per tenant** over the shared listing set. Split `run_poll` into `_gather` +
`score_and_stage(repo, profile, listings)` — the `listings=` seam makes this
trivial.
- **`cli.py`:** `poll` drops `--dir`, gains `--tenant <id>` or default (all
active); run hourly by **system cron / systemd timer** (replaces the user-level
units).
- **On-demand poll button:** the SPA's poll button posts `/api/poll`; in the split,
.NET can't run Python inline. v1 options, pick one: (a) **enqueue** a
`poll_requests(tenant_id, requested_at)` row the worker drains each minute
[recommended — keeps runtimes decoupled]; (b) .NET shells out to the worker; or
(c) hide the button and rely on cron. Default to (a).
- **Tests:** dedup across runs (offline), seed-from-existing skip, multi-tenant
isolation.
### Step 5 — Self-host packaging + data export/delete (replaces billing)
- **No Stripe.** Instead, make it *easy to self-host and trust*:
- **`docker-compose.yml`:** `api` (.NET), `poller` (Python cron), `db`
(Postgres) — one `docker compose up`. `.env.example` for connection string,
email creds, base URL.
- **README rewrite:** self-host quickstart, the two-runtime model, env vars,
`import-md` first-run migration, Apache-2.0 badge.
- **Data export + import** (landed) `GET /api/account/export` → one JSON snapshot
(every application + criteria + blacklist); `POST /api/account/import` loads it
back, upserting applications by slug in a single transaction. The round-trip lets
a user migrate between instances, not just back up — no lock-in. SPA gets Export
/Import buttons in the masthead.
- **Shared opportunity list** (landed; issue #1) `GET /api/account/export/shared` →
the peer-facing sibling format (`format: applytrack-shared`): slug + company,
role, link, location, source only — personal state (status, notes, contacts,
dates, score, salary) stripped at the source. Importing one lands every entry as
a fresh `lead` and skips slugs the importer already tracks (never overwrites).
Lives in Settings · Account as **Share an opportunity list**.
- **Data delete** `DELETE /api/account` → FK `ON DELETE CASCADE` from `users`
drops applications/seen/profiles/sessions/tokens/blacklist in one statement; SPA
confirms.
### Step 6 — Cover-letter materials engine (post-v1, optional module)
Was the heaviest-cost / scariest-data piece, deferred past v1; landed once the core
was stable. Provider-agnostic and local-model-first so résumé data can stay on-prem.
> **⚠ REQUIREMENT — any LLM, and strictly optional.** The engine talks the
> OpenAI-compatible `/chat/completions` shape and **must keep working against any
> endpoint that speaks it** — local (Ollama / vLLM / LM Studio) or hosted (OpenAI,
> OpenRouter, Together, Groq, Anthropic's compat shim, …). Never hard-code a
> vendor, model, or paid key into the backend. And the whole engine is
> **opt-out per tenant**: `cover_letters_enabled` (DbUp 0013, surfaced in
> Settings · AI) turns off every drafting affordance and refuses
> `POST /api/apps/{name}/draft`, so users who don't want to run a model never
> see, configure, or pay for one.
- **Schema (DbUp 0010–0012):** `resume_profiles`, `llm_settings`
(`api_key_ciphertext`), `cover_letters((tenant_id, application_name))` — all
per-tenant, FK→`users`/`applications` `ON DELETE CASCADE`.
- **Config layering:** instance default (`Llm__BaseUrl`/`Model`/`ApiKey` env) merged
field-by-field with a per-tenant override (key decrypted) → `EffectiveLlmConfig`.
Drafting is simply off until either layer is configured.
- **Secrets at rest:** per-tenant API keys encrypted with AES-256-GCM
(`SecretProtector`), master key via `APPLYTRACK_SECRETS_KEY`; the key field is
write-only (never echoed back) and removable.
- **Endpoints:** `GET/PUT /api/resume`, `GET/PUT /api/llm-settings`,
`POST /api/apps/{name}/draft` (rate-limited), `DELETE /api/apps/{name}/cover-letter`.
The drafter calls an OpenAI-compatible `/chat/completions`, length-gates the result
(40..6000 chars), and persists Markdown.
- **SPA (extend-only):** Résumé editor, AI-settings panel, and an inline
draft→render→copy/download/discard flow on the application sheet.
- **Tests:** repo CRUD + crypto round-trip, config resolution, a stub LLM client for
the drafter; full headless-browser E2E of the SPA flows. LaTeX/PDF output is the
remaining future work.
## Critical files
- **New .NET:** `api/Program.cs`, `Endpoints/{Apps,Auth,Account}.cs`,
`Data/{Repo,Slug}.cs`, `Auth/*`, `Migrations/*.sql`, `wwwroot/` (the SPA),
`ApplyTrack.Api.Tests/`.
- **Python, trimmed:** `poll.py` (`run_poll(repo, profile)`, split `_gather` +
`score_and_stage`), `worker.py` (new, per-tenant), `db.py` (new, psycopg3),
`criteria.py` (defaults seed), `cli.py` (`import-md`, per-tenant `poll`),
`linkcheck.py` (unchanged).
- **Removed from Python once .NET owns the API:** `store.py`, `web/` (the SPA
files move to `wwwroot/`, the FastAPI app + Basic-Auth middleware retire).
- **License (Apache-2.0):** every source header + `LICENSE` + `pyproject.toml` + `.csproj`.
## Main risks
- **Two runtimes sharing one schema.** Mitigation: the schema *is* the contract;
changes go through DbUp; the Python `db.py` and .NET `Repo.cs` are the only
writers and both filter `tenant_id`. A schema-shape test on each side guards
drift.
- **`version` semantics swap** (mtime-size → int): trivial on a fresh DB; the real
risk is omitting `tenant_id` from the optimistic `UPDATE` — the repo choke-point
guards isolation *and* the lock.
- **Auth before storage is solid** entangles two hard changes — Step 1 as a
shippable single-user-on-PG milestone keeps the rollout safe.
- **Cron HTTP volume / board rate-limits** — mitigated by fetch-once-per-run.
- **SPA contract drift** — the kept UI is unforgiving about endpoint shapes; Step 1
contract tests against `app.js`'s actual calls are mandatory.
## Verification
- **Step 0/1:** .NET + Python tests green; `applytrack import-md --dir applications
--tenant 1` then `dotnet run` → the **existing SPA** loads all apps;
create/edit/delete and the 409-conflict flow work identically on Postgres.
- **Step 2:** request a magic link (dev: link to console), verify → session cookie
set, app loads; logout → 401 → login view. Cross-tenant isolation test green.
- **Step 3:** edit a profile's keywords via the Criteria panel; Python
`run_poll(repo, profile, listings=[...])` offline yields leads filtered by
`min_score`/`sources`.
- **Step 4:** `applytrack poll` (all tenants) twice offline → second run adds zero
(dedup + seed-from-existing). Per-tenant failure doesn't abort others. Poll
button enqueues; worker drains it.
- **Step 5:** `docker compose up` brings up all three; `GET /api/account/export`
returns a JSON snapshot that `POST /api/account/import` round-trips on another
instance; `DELETE /api/account` cascades to zero rows.
- **Step 6:** with an OpenAI-compatible endpoint configured (instance env or the
SPA's AI panel), fill the résumé → open an application → Generate drafts a cover
letter inline; copy/download/discard work; the saved per-tenant API key is never
echoed back and survives a reopen. Backend + headless-browser E2E green.
---
# Appendix — Secure remote access (already shipped on the Python app)
> The self-signed-TLS + HTTP-Basic-Auth LAN access task is **already implemented
> and committed** on the current Python/FastAPI app (cert gen, `0.0.0.0` guard,
> Basic-Auth middleware, systemd `EnvironmentFile`). In the .NET rewrite it is
> **subsumed**, not re-ported:
> - **TLS:** Kestrel terminates HTTPS natively (`UseHttps`, dev certs via
> `dotnet dev-certs`); no `openssl` shell-out.
> - **Auth:** the **Step 2 magic-link + sessions** replace Basic Auth entirely —
> there's no longer an "exposed without auth" state to guard against, because
> the app requires login.
> - **Gold-standard alternative still stands:** front it with `tailscale serve`
> for a real trusted cert + identity-based access, app bound to the tailnet, no
> `0.0.0.0`. (See the `tailscale-serve-todo` memory.)