ETL Cohort Hub Portfolio Pipeline, Part 2

Role

Sole designer and builder. Architecture, security review, documentation, and iterative testing, working across dozens of build-and-fix cycles with an AI coding assistant for implementation and an AI collaborator for planning, code review, and content drafting.

This picks up from Part 1: https://localkine.ai/portfolio/kjc-edtech-portal-part-1, which covers building the ETL Cohort Hub itself. Part 1 is the shared infrastructure; this is what sits on top of it — the tool that gets each of the Hub's nineteen K1 members to their own individual, QE-compliant portfolio site.

The Situation

K1's Digital Portfolio requirement is exacting and non-negotiable by rubric: an introduction page, three domain sections in a fixed order with courses listed as named placeholders, and a separate Professional Growth Plan page — identical structure for all nineteen cohort members, whatever their technical background. The available shortcuts — a generic Google Sites template, or nothing — either undersold real work behind a dead-feeling page, or asked people with no development experience to somehow produce a live, working website from scratch. I'd already built myself a working prototype using an AI app builder connected to a no-code database. The open question was whether that pattern could be handed to eighteen other people without turning into eighteen separate support tickets.

What I Did

Chose Google Sheets over Airtable as the shared backend, specifically to lower the barrier to entry — most of the cohort already lives in the Google ecosystem, and evidence from prior cohorts' abandoned Google Sites suggested tool-unfamiliarity, not effort, was the real reason so many portfolios stalled at "details coming soon."

Designed a single-sheet CMS schema that mirrors the rubric directly — a Domain field mapped to the program's three required domain groupings, a Course field matched against a pre-populated course list (in real program sequence, not alphabetical), and a QE Rationale field distinct from a plain description, specifically so the artifact a reviewer needs to read is never buried in summary text.

Built an interactive wizard inside the existing cohort portal — prefilling identity and bio from each member's own directory profile, gating only the final "generate" step on completeness so people could jump straight to whatever section they hadn't finished instead of clicking through steps that were already done, and generating a single, fully-specified build prompt designed to be sent once rather than iterated on live and burn through free credits.

Wrote a companion teaching course (final decision was to condense it as a set of key instructions) and spreadsheet template, shared directly with cohort members rather than posted publicly — dropdown-validated fields, ten real worked examples pulled from my own coursework so people could see what a finished entry, a full write-up, and a QE Rationale actually look like before writing their own, and a Read Me tab rewritten for scannability after early feedback that dense instructional text wasn't landing for everyone.

Ran a security review before opening it to the cohort, and it caught three real issues, not hypothetical ones: a server function that returned every member's phone number and personal email to any authenticated caller regardless of role, a storage policy that made unapproved submission photos publicly readable, and an endpoint that let anyone probe whether a given email had applied and what its status was. All three got fixed before launch.

Verified my own claims instead of asserting them. Before telling anyone "this won't burn through your starting credits," I signed up for a brand-new account and ran the whole flow myself, first try, the way an actual new user would — and did it again on a second account after a template revision, to confirm the number held.

Iterated on accessibility after a classmate's confusion, not before. When a soft-launch tester didn't seem to be absorbing key setup warnings, I converted the two facts most likely to silently break someone's first attempt — a required URL-format rule, and a status field that hides content on purpose — from sentences embedded in paragraphs into standalone, icon-led callouts that don't depend on reading the surrounding text to find.

Rebuilt the underlying template schema as real portfolio entries accumulated, not just once at launch. What started as a handful of fields grew to include Entry Format, dual Domain/Additional Category placement, and separate Slide Deck/Recording/Document link columns as real submissions revealed what a one-size schema couldn't represent — currently on the third full build of the template, with the credit estimate revised upward each time rather than treated as fixed, since a longer generated prompt costs more than the shorter one it replaced.

Found a related gap in the same Contact step that had already blocked one classmate over a missing Spreadsheet ID: there's a required-field guardrail for the Spreadsheet ID, but nothing equivalent for the email field, so a missing email silently defaults to the institutional directory address instead of the person's own.

Hit and fixed a data-integrity bug in the template-generation script itself: inserting a new column with openpyxl doesn't move the sheet's existing dropdown validations along with the data — they stay anchored to their original column letters while the data shifts, so every validation past the insertion point silently pointed at the wrong column. The fix was to strip and rebuild every validation by looking up each column's letter from the header row by name after any insertion, then read them back to confirm before calling a template revision done.

Built a Follow-up Prompts page as its own destination, once it became clear the wizard's job ends the moment the rubric's minimum structure exists, not the moment someone's done caring about their site: a bank of reusable, fill-in-the-blank prompt cards for the things people actually come back for after the first build — connecting a custom domain, reporting something that looks visually wrong, running an accessibility pass, chasing down a slow page — plus one card specifically for classmates who'd already started building their own site independently before the wizard existed, which audits their current pages against the rubric's structure (Present, Partially present, or Missing, item by item) without touching their existing design. That last card replaced my original plan of just asking those classmates to restart from scratch — a "keep what you already built, tell me what's actually missing" prompt costs them nothing and costs me one card, instead of a conversation I was dreading having with each of them individually.

Reflection

The harder part of this project was never the build — it was resisting the instinct to consider it finished once it worked for me. Almost every real fix in this project came from a specific moment of someone else actually using it and hitting something I hadn't: a column position I hadn't checked against the code that read it, a dropdown value that got silently overwritten by an unrelated edit, a warning that technically existed on the page but that a real reader skimmed straight past. None of those would have surfaced from re-reading my own prompt more carefully. They only came from handing it to someone else and watching, closely, where it actually broke.

I also went back and forth for a while on whether to state, anywhere, how much time this actually took. I decided it's not self-promotion to say plainly that unpaid infrastructure work is still work — it's just accurate, and it's the kind of thing that gets erased by default if the person who did it doesn't say so.

What's still open going into the next build: the Contact-step email guardrail above, and reconciling a field-naming mismatch between what the build prompt calls the hero image field and what the sheet itself calls it. A bigger question — whether new cohorts get open access to the Cohort Sites directory or a gated one — isn't mine to decide alone, so it's sitting with Dr. Zieger rather than something I picked for everyone on my own.