Tổng kết năm học 2025-2026 — kế hoạch xử lý kho dữ liệu

A coordinator shared the full end-of-year ("Lễ tổng kết năm học 2025-2026") package for all six partner schools. This plan inventories it, lays out every action it supports across all four Foundation areas, recommends a sequencing, and surfaces the decisions. Internal review surface — nothing executed yet.

draft — for review Area 2 · Media Area 1 · Care Areas 3·4 · Donor/Finance Phase A — no auto-publish

Bối cảnh · Context

A shared Drive folder "Tổng kết năm học 25-26" (from the Fund Director's personal Drive — the same one-way read-in lane used before; workspace-ethics.md §1) holds the end-of-year ceremony package for all six partner schools. It is a single bundle that touches all four Foundation areas at once: media & stories, beneficiary care, and donor/finance + stewardship.

This plan: inventory the corpus, list every action it supports (grouped by area), recommend a wave-by-wave sequencing with a low-risk pilot, name the real tool entry points to reuse, flag the constraints, and surface the four decisions only the administrator can make.

1. Kho dữ liệu · Corpus inventory

Root → 6 school sub-folders. Per school: a photographer photo set, raw video clips, a year-end summary report, speeches, an SE-assessment sheet (Đánh giá cảm xúc), and an awards sheet (Thưởng / Giải thưởng). Approx. 1,500 photos, ~320 videos, ~25 documents. All six are existing partner schools.

Trường	Ảnh sự kiện	Ảnh thợ ảnh	Video	Tài liệu chính
THCS Tam Thanh	21	167	13 + 191 clip thô	BC tổng kết; BC HMT; Đánh giá cảm xúc; Thưởng
THCS Tân Khánh	67	168	14	BC tổng kết; BC GVCN; phát biểu Hiệu trưởng; Giải thưởng
THCS Vĩnh Hào	179 + 22	244	33 + 5	BC tổng kết; phát biểu lớp; Danh sách thưởng
THCS Đại An	76	196	16	BC tổng kết; phát biểu GVCV; phát biểu HT (PDF); Đánh giá cảm xúc; Giải thưởng
THPT Lương Thế Vinh	51	197	30	BC tổng kết; 2 phát biểu; Danh sách thưởng
THPT Nguyễn Bính	29	132	13 + 2	BC cuối năm (×2 trùng); BC tổng kết; Các giải thưởng

What the documents actually contain (verified by reading Tam Thanh's set)

The year-end report .docx is effectively a pre-written coordinator brief. It narrates the ceremony beat-by-beat — opening song/dance, screening of the Foundation's year clip, homeroom-teacher summary, principal & Fund-Director speeches, a student's speech, the prize-giving (sport / writing / academics), the class gift, the summer-English pledge — and includes the event date (Tam Thanh = 19/5/2026), named winners with cash amounts, and a verbatim parent thank-you letter.
SE sheet = per-student social-emotional evaluation (Teacher / Student / Family columns) + class rank ("Xếp thứ 16/42"). Maps to the care se doc type (petal 3).
Awards sheet = per-student prizes with cash amounts (e.g. Nhất nhảy dây = 200,000đ). Simultaneously a giay-khen note, a cash benefit, and a school register milestone.

2. Toàn bộ hành động khả thi · The full menu of actions

The complete menu the data supports. REC marks items recommended for the first pass; §3 sets the order.

Area 2 — Media & stories (Stream A / B / C)

RECM1 — 6 event folders + auto-derived brief.yaml (one per school ceremony). The year-end report is the brief: pre-fill activity type, school, date, program, participants, event summary, quote_1..7 (parent letter + student/principal lines, verbatim), the outcome-for-child beat, petal anchor, and claim sources.
RECM2 — a drafted post per school (Stream A milestone story): cull ≈5 photos → sanitise → brand chrome → Opus caption → QC → awaiting-approval. Six dignified posts, each with a real quote and a concrete outcome.
M3 — network "one number that grows" impact post (Stream B): total students / prizes / cash awarded across all six schools, numbers traced to the awards sheets.
M4 — Stream C "Hành trình của em" (anonymised): e.g. the student whose handwriting + thinking transformed over 3 years — a journey that needn't name anyone.
M5 — quote-card (Stream B): the parent thank-you letter or a student line as a cream/navy typographic card.
M6 — video: catalog/curate the ~320 raw clips; realise the "Clip Tổng kết 2025-2026" + short reels (later stage; burned-in captions + bumper).
M7 — website gallery + news per school (post-approval).
M8 — monthly impact recap once the year-end posts are approved.

Area 1 — Beneficiary care (documents → registers)

RECC1 — file every document in the two spaces: human Drive 2_Hoc-sinh/Tai-lieu-tong-hop/2025-2026/<trường>/ (intact) + machine-bucket .md/.txt extraction; catalog each in source_doc (kind=consolidated). Filed by ownership, not by method.
RECC2 — extract the SE sheets → se school_report + a Block-3 profile note (petal 3) per matched student; capture class rank.
RECC3 — extract the awards sheets → per student: a giay-khen profile note + a school register_entry (kind=prize) + a cash benefit row (funding source = HMT year-end awards).
RECC4 — year-end report → school + cohort registers: a milestone ("Lễ tổng kết 2025-2026"), headcount, funding total, plus note rows; notable-student facts → profile notes.
C5 — parent thank-you letters → school register note (reused for M5 / stewardship).
C6 — roster reconciliation: the SE + awards sheets are a fresh roster snapshot — detect new students, leavers, name/class corrections.
C7 — graduation / cohort completion: lớp 9 (THCS) and lớp 12 (THPT) finished exams → status change + cohort-completion notes.
C8 — per-school + per-cohort care reports once data is loaded (quân số, funding trọn đời, trường hợp tiêu biểu).
C9 — triage queue (_needs-review.md) for every unmatched / ambiguous student — never silently filed.

Areas 3 & 4 — Donor, finance & stewardship

F1 — record the cash awards as benefit outflows tied to a funding source → feeds the monthly transparency report.
F2 — donor stewardship material: the parent letters + principal praise are testimonials for donor updates and the international-fundraising track (human-approved; no auto-send beyond receipts).

Cross-cutting / governance

RECG1 — provenance + preservation: copy the corpus into the Foundation Drive so the only copy isn't a personal account (one-way read-in; firewall intact).
G2 — coordinator review list: surface every data-quality issue found (duplicated report, stray shortcut file, "thành tích thấp hơn năm ngoái" notes) honestly.

3. Trình tự đề xuất · Recommended sequencing

The corpus is large and spans live beneficiary data — do it in waves, not one shot.

Wave	Nội dung	Rủi ro
0	Publish this plan to the review hub (you are reading it) → pick scope	—
1	Provenance + filing (G1, C1): copy corpus into Foundation Drive, file docs in the two spaces + catalog `source_doc`. `--dry-run` first	low
2	Pilot one school end-to-end (recommend Tam Thanh): M1→M2 (folder + brief + one drafted post) + C2/C3/C4 (SE + awards + report → registers, validated in place)	medium
3	Batch the other five through the proven Wave-2 path	medium
4	Aggregate + stewardship: M3 impact post, M4 journey story, C8 reports, F1/F2, M6 video — each its own gated step	varies

All Area-2 output stops at awaiting-approval (Phase A — nothing posts without /approve).

4. Công cụ tái sử dụng · Tool entry points (reuse, don't rebuild)

Event folder + brief: the event-folder Cloud Run service / tools/event_folder_core.py; brief schema _system/forms/coordinator-brief-form-v1.yaml. For a bulk run, write brief.yaml straight into each event folder rather than submitting the form 6×.
Ingest + draft: tools/ingest_watch.py → tools/pipeline_run.py --once (sanitise/EXIF-strip) → tools/pipeline_run.py --bundle <dir> [--select …] [--milestone] (cull → chrome → Opus caption → QC).
Care extraction: tools/school_doc_extract.py (se, giay-khen), tools/care_intake_convert.py, tools/beneficiary_promote.py (the /record path), tools/roster_match.py (dual name matcher), tools/beneficiary_registry_migrate.py (schema: source_doc, register_entry). Reports: report_school.py / report_cohort.py / report_child.py.

5. Khoảng trống cần lưu ý · Gaps to confirm before executing

xlsx input gap. school_doc_extract's engine takes PDF document input; the SE/awards data here is .xlsx/.docx. The proven path for sheet data is the existing backfill_kq_drive.py pattern (openpyxl parse + roster_match → school_report / register_entry / benefit). Wave 2 adapts that, not the PDF path.
Schema partly uncommitted. source_doc / register_entry are code-complete but uncommitted, and the catalog/register writers are only partly built. Wave 1/2 commits the schema and adds thin writers — not a from-scratch build.
Matching won't be 100%. New students, transfers, name variants → everything ambiguous goes to _needs-review.md, never force-matched.
Cash amounts. Confirm the prize amounts are Foundation-paid (benefit outflow) vs. school-paid before F1.

6. Ràng buộc cứng · Hard constraints

Phase A absolute: Area-2 output stops at awaiting-approval; no /approve, no post.
Care live-data discipline: validate in place, --dry-run first, backup + gcsfuse quiesce of the hub before any so-dang-ky.sqlite bucket upload, verify after. The care bucket is the sole copy.
Firewall + provenance: read-in from the personal Drive only; never write HMT data out.
Sensitive detail: SE notes may carry family/behaviour detail — record the care-relevant fact, never copy identifying medical / precise hardship into the profile body.
Claims: every published number traces to a Foundation record; no donation CTA.

7. Quyết định cần từ quản trị viên · Decisions for you

D1 — Pilot vs batch: pilot Tam Thanh end-to-end first (recommended), or set up all six folders/briefs + file all docs in one batch?
D2 — Media granularity: six per-school posts, one network impact post, or both (six folders for the record + 1–2 drafted now)?
D3 — Care depth this pass: full extraction (SE + awards + reports → registers + benefits) now, or file + catalog the documents only (C1) and defer per-student extraction to a later supervised run?
D4 — Cash awards: are the prize amounts Foundation-funded (record as benefits / F1)?

HMT Media · operations & automation workspace · internal review surface · noindex,nofollow. Drive IDs and service-account details are redacted from this page; the working plan retains them. Generated 2026-06-11.