File size: 13,127 Bytes
d2d751b
 
 
b032378
d2d751b
 
 
 
 
 
 
 
 
 
 
 
 
3036e5d
 
 
 
 
 
 
 
 
d3137b1
f7feab1
 
d2d751b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2c8ee7b
d2d751b
 
 
2c8ee7b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d2d751b
 
 
 
 
2c8ee7b
d2d751b
 
 
 
 
 
 
 
 
2c8ee7b
d2d751b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2c8ee7b
 
 
 
d2d751b
 
 
2c8ee7b
d2d751b
 
 
 
 
 
 
 
 
 
 
 
2c8ee7b
d2d751b
 
 
2c8ee7b
 
 
d2d751b
 
 
 
 
 
 
2c8ee7b
d2d751b
 
 
 
 
 
 
 
 
2c8ee7b
 
d2d751b
 
 
2c8ee7b
d2d751b
2c8ee7b
d2d751b
2c8ee7b
 
d2d751b
 
 
 
 
2c8ee7b
d2d751b
 
 
 
 
 
2c8ee7b
 
 
 
 
d2d751b
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
# 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 <HF write token>` β€” 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:<TOKEN>@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 "<repo>" 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).