# note.md — Architecture & Operations (read this first) > **Purpose:** Onboard a new AI/dev to this project fast. Keep it current — see > **[§10 Maintaining this file](#10-maintaining-this-file)**. Last updated: **2026-05-24**. --- ## 1. What this app is **Auto Form Generator** (Thai: *ระบบสร้างหนังสือราชการอัตโนมัติ*) — a Streamlit web app that generates 5 standard Thai procurement letters (Word `.docx`) from templates. The user types a **PO number**, the app looks it up in an Excel sheet and auto-fills vendor name, job title, etc., then renders a downloadable document. - **Live deployment:** Hugging Face Space **`Menixis/auto-form`** (private). URL: https://huggingface.co/spaces/Menixis/auto-form - **SDK:** Docker (see `Dockerfile`), Streamlit on **port 7860** (HF convention). - **Theme:** "Neon Indigo" dark dashboard theme (deep indigo bg `#262150`→`#191730`, panel- purple cards) with a vibrant **violet→magenta gradient** (`#7C5CFF → #FF4D8D`) as the primary accent on the hero card, primary/download/PO-search buttons and the active step — all with white text/icons and a soft glow. **Cyan** (`#22D3EE`) marks success/done; light-violet (`#C9B7FF`) is the secondary text accent. The hero card is styled as a **ticket/receipt**: a left-aligned "PTT DOCUMENTS GENERATOR" pill in the stub, a bold dashed perforation (light), and two semicircular edge notches punched as TRUE transparent holes via CSS `mask` (so the page background shows through them exactly — no colour seam). AUTO FORM title + subtitle are left-aligned; all content is vertically balanced. Buttons are gradient pills. No sidebar. Data loads silently — no data-source bar and no manual upload UI (the app does not tell users where the data comes from). > ⚠️ The user sometimes edits `streamlit_app.py` **directly in the HF web editor**, so > the Space can diverge from this local clone / GitHub. **Always `git fetch origin` and > compare before pushing. Never force-push.** --- ## 2. The data-flow architecture (the important part) This is the part that took the most effort to get right. Read it carefully. ``` ┌─────────────┐ Power Automate flow ┌──────────────────────────┐ │ Power BI │ ── Office Script ─▶ │ Excel file on the user's │ │ "Data For │ (cloud, on PC-off)│ PERSONAL OneDrive │ │ Usage" │ │ (pttplc-my.sharepoint…) │ │ model │ │ shared "Anyone w/ link" │ └─────────────┘ └────────────┬─────────────┘ │ HTTP GET (anonymous link │ + &download=1 + browser UA) ▼ ┌──────────────────────────┐ │ HF Space Menixis/auto-form│ │ load_live_data(DATA_URL) │ │ → auto-clean → lookup │ │ cached ~1h, sample fallbck│ └──────────────────────────┘ ``` **Division of labor:** - **User side is automated:** a **Power Automate flow** refreshes the Power BI dataset and runs an **Office Script (Excel script)** that pulls the latest data into the Excel file on OneDrive. This runs in the Microsoft cloud on a schedule, so it happens **even with the user's PC off** — no manual save step. - **App** fetches that file live on each load. No upload step, no database, no dataset, no Microsoft auth token, no GitHub Action, no IT involvement — and it works **with the user's PC turned off** (the file lives in the cloud). **Why this design (the hard constraints that drove it):** the data had to be PC-off-accessible, free, with no admin/IT involvement and no fragile periodic re-login. Those constraints ruled out service principals, Power Automate premium, scheduled uploads, etc. The breakthrough: **PTT blocks anonymous share links on team SharePoint sites but allows them on personal OneDrive** (`pttplc-my.sharepoint.com`). So the app reads a personal-OneDrive anonymous link directly. Tradeoff the user accepted: anyone with that obscure link can read the file. **Data source upstream:** Power BI semantic model **"Data For Usage"** (workspace `461e9fbe-d85e-4070-bf3b-526fbb127b4c`, dataset `6fffb9e8-1be2-4091-874b-4aa8f04ea1c4`), table **"Data for Auto Form"**, lookup key column **`poNo`**. --- ## 3. Configuration — the one secret that matters | Where | Name | Value | Purpose | |-------|------|-------|---------| | HF Space → Settings → Variables and secrets | **`DATA_URL`** | the personal-OneDrive "Anyone with the link" URL to the Excel | Live data source. If unset/unreachable, app falls back to `sample_po_data.xlsx`. | The code (`streamlit_app.py`) reads it via `os.environ.get("DATA_URL")`, auto-appends `&download=1`, and sends a browser `User-Agent` (a plain GET 403s without it). > The actual `DATA_URL` value is **not stored in this repo** — it lives only in the HF > Space secret. Don't paste it into committed files. **Legacy secrets that are now UNUSED** (safe to delete from the Space): `HF_TOKEN`. Also unused: the `procurement-tools/ptt-auto-form-data` HF dataset and the `menixis-data-read` HF token. These are leftovers from an earlier "upload to a private HF dataset" approach that was replaced by the OneDrive-GET approach. (The `llm-app` HF token is **NOT** unused — see §4.) --- ## 4. Keep-alive cron job (cron-job.org) A free HF Space sleeps after ~48h of inactivity. To keep it responsive, an external scheduler on **[cron-job.org](https://console.cron-job.org)** restarts the Space twice a day. Account: the user's, logged in as `640097@pttplc.com` (password not stored here). **Job "Keep PTT Auto Form Alive"** (job id `7595656`): - **Request:** `POST https://huggingface.co/api/spaces/Menixis/auto-form/restart` - **Header:** `Authorization: Bearer ` — the token named **`llm-app`** in HF → Settings → Access Tokens (value intentionally not stored in this repo). **This token is IN USE — do not delete it.** If it is ever rotated, update the `Authorization` header in the cron job too, or the restarts will start failing (401). - **Schedule:** crontab `0 */12 * * *` → **00:00 and 12:00 Asia/Bangkok**, 30s timeout, POST with empty body. - Notifies the account owner if the job is auto-disabled after repeated failures. A sibling job **"Keep PTT LLM App Alive"** does the identical thing for a *different* Space (`Menixis/llm-app`) — not part of this app, but it shares the same `llm-app` token. > The restart also clears the app's in-memory state, so the next page load re-fetches > fresh OneDrive data. (Day-to-day freshness is already handled by the 1-hour > `load_live_data` cache — the cron job's real job is preventing the idle-sleep, not data > freshness.) > > The `llm-app` token has **write** scope (the restart API requires auth), and it lives > in plaintext inside the cron-job.org job config — so treat that account as sensitive. --- ## 5. Key files | File | Role | |------|------| | `streamlit_app.py` | **Main entry point.** Web UI + data loading + PO lookup. ~1127 lines. | | `form_logic.py` | Shared doc-generation logic. Exports `generate_form`, `FORM_TYPES`, `DOC_TYPE_OPTIONS`, `DISPLAY_FIELDS`. | | `excel_lookup.py` | Legacy standalone Excel lookup helper (used only by the now-deleted desktop GUI). The web app uses `lookup_po_in_bytes()` inside `streamlit_app.py`, not this — **orphaned, safe to delete.** | | `templates/form1-5.docx` | The 5 Word templates that get filled in. | | `sample_po_data.xlsx` | Demo data + fallback when `DATA_URL` is unreachable. Demo POs: `4500987654 / 4500987655 / 4500987656`. | | `Dockerfile` | HF Spaces container (python:3.11-slim, runs Streamlit on 7860). | | `requirements.txt` | Deps. NOTE: lists `huggingface_hub` (now unused/legacy). `requests` is used by `load_live_data` and comes in transitively via Streamlit. | | `README.md` | Has the HF YAML frontmatter (title/sdk/app_port) — **do not delete the frontmatter**, HF needs it. | | `.streamlit/config.toml` | Theme + toolbar config. | --- ## 6. Key code (in `streamlit_app.py`) Two functions carry the data layer. Both are robust against the **raw Power BI Excel export format** (a banner row like "Data returned for …", a blank row, then headers wrapped as `Data for Auto Form[poNo]`). - **`_strip_col_wrapper(name)`** — turns `Data for Auto Form[poNo]` → `poNo`. - **`lookup_po_in_bytes(po_number, file_bytes, sheet_name=None, po_column="poNo")`** — scans the first 15 rows to auto-detect the header row (so banner/blank rows don't break it), strips the wrappers, matches the PO (handles floats like `4.14e9` → int string), and returns `(result_dict, available_cols)`. Returns `(None, cols)` if PO not found, or `{"__error__": ...}` if the `poNo` column is missing. - **`load_live_data(url)`** — `@st.cache_data(ttl=3600)`; GETs the OneDrive link, validates the response starts with `PK` (a real `.xlsx` zip — catches login-redirect HTML), returns `(bytes, fetched_at)`. **Data-source selection** (~line 841): `load_live_data(DATA_URL)`, else fall back to `sample_po_data.xlsx`. Loads silently on each page load. The manual-upload override and the data-source bar were removed (2026-05-23) — the UI no longer shows the data origin. The `fetched_at` returned by `load_live_data` is now discarded. --- ## 7. Deploying changes The Space deploys on push to `main`. ```powershell cd "C:\Users\640097\Documents\Applications\auto-form-generator" git fetch origin # ALWAYS — the Space may have been edited in the web UI git log --oneline origin/main -3 # compare; reconcile before pushing # ... make edits, commit ... git push origin main ``` **HF git auth:** there are no ambient credentials. The working pattern is: mint a **temporary** *write* token in the HF web UI, push using `https://Menixis:@huggingface.co/spaces/Menixis/auto-form`, then **revoke the token** immediately after. > ⚠️ **Do not leave a token embedded in the git remote.** The remote was scrubbed clean on > 2026-05-23 (`git remote set-url origin https://huggingface.co/spaces/Menixis/auto-form`). > Keep it that way — pass the token inline in the push command instead. Pushing to `main` is gated by Claude Code's permission classifier and needs a `PowerShell(git -C "" push*)` allow rule in `~/.claude/settings.json` (already added for this repo path). --- ## 8. Gotchas (things that already bit us) 1. **Don't keep this repo inside OneDrive-synced `Documents`.** Mid-session, OneDrive dehydrated/removed the working folder and broke git. The folder is currently pinned with `attrib +P -U`, but the real fix is to **move it out of OneDrive** (e.g. `C:\dev\`). 2. **Team-site SharePoint links don't work headless** — they redirect to login. Only the **personal OneDrive** anonymous link works, and only with a browser `User-Agent`. 3. **The HF Space code can diverge from this clone** (web-UI edits). Always fetch first. 4. **`requirements.txt` lists `huggingface_hub`** but nothing imports it anymore — it's a legacy dep. Harmless; remove only if doing a deliberate cleanup. 5. **The keep-alive depends on the `llm-app` HF token** (§4). Deleting/rotating that token without updating the cron jobs silently stops the Space restarts → it can sleep again. --- ## 9. Known cleanup items (optional, none urgent) - [x] Scrub the embedded HF token from `.git/config` remote URL (done 2026-05-23). - [ ] Delete unused HF assets: `HF_TOKEN` Space secret, `menixis-data-read` token, `procurement-tools/ptt-auto-form-data` dataset. **(Do NOT delete the `llm-app` token — it's used by the keep-alive cron, §4.)** - [ ] Move this repo out of OneDrive-synced `Documents`. - [ ] (Optional) drop `huggingface_hub` from `requirements.txt`. --- ## 10. Maintaining this file Update `note.md` whenever any of these change, and bump the "Last updated" date at the top: - The data-flow architecture or the data source (§2) — e.g. if the OneDrive approach is ever replaced. - The `DATA_URL` secret or any other config/secret (§3). - The keep-alive cron job(s) on cron-job.org (§4) — schedule, restart URL, or the `llm-app` token. - Key files added/removed/renamed, or the data-layer functions (§5–§6). - The deploy target Space or the push procedure (§7). - Any new gotcha worth warning the next person about (§8), or when a cleanup item in §9 is done (check it off / remove it).