CSS Platform (V2): Technical README

The Chinese Students' Society's own event, ticket and membership platform: a mini Luma/Eventbrite built on free Google tools. Execs: you want HANDBOOK.md. Something broke: RUNBOOK.md.

Status: API 0.15.0 (the API_VERSION in api/Config.js; the Apps Script deployment label says the same). Events, registration, payments, door and the redesigned Exec Portal are live. Membership sign-up (member.ucalgarycss.ca/create), the Members tab's Pending list, Add member and Mark paid were added 2026-09-30. Roadmap and what is left: the vault note "CSS Platform - Roadmap". The old system (../CSS Ticketing System/) keeps running until this one is proven. Nothing here touches it.

---

1. How it fits together

 People                     Web pages (static files)              Back end                     Storage
 ─────────                  ───────────────────────              ─────────                    ─────────
 Public / attendees  ──►    public/        (event pages)   ──┐
 Execs               ──►    exec-portal/   (Exec Portal)   ──┼──► "CSS Platform API"   ──►   Google Sheets + Drive
                                                             │    (Google Apps Script
                                                             │     web app, api/)

• Pages are plain HTML/CSS/JS with no build step. Host them anywhere static (Vercel, GitHub Pages) or open them from disk.
• Every page talks to one back end: an Apps Script web app. The page sends POST with a JSON body { action, token, ...details } and gets JSON back: { ok: true, ... } or { ok: false, error: "CODE", message }.
• Storage:
  • Membership sheet (the existing one): only read so far, for member search and member-price checks.
  • "CSS Platform Data": a spreadsheet the back end created and owns. One tab per table (section 4).
  • "CSS Platform/Event images" Drive folder: uploaded event images, viewable by link.

2. Where everything lives

Thing	Where
Google account that owns everything	[email protected]
Apps Script project	"CSS Platform API", script ID 1xxpp4EJA-0uUhtwo9sOK9e1itomqN_29C6lwg3jzh35pabKJ-vOKnpMB. Editor: https://script.google.com/d/1xxpp4EJA-0uUhtwo9sOK9e1itomqN_29C6lwg3jzh35pabKJ-vOKnpMB/edit
Web app deployment ID	AKfycbxJ_a_cHyRXadK17ocIebhE2XDFcpEADCzSqOVXwdCgIQe1T-UPi-efzgla2mn7ML2d
Web app URL (API)	https://script.google.com/macros/s/<deployment ID>/exec. Opening it in a browser shows {"ok":true,...}
Platform data sheet	"CSS Platform Data" (may be renamed/moved; the system finds it by ID, stored in Script Property DATA_SHEET_ID)
Event images	Drive folder "CSS Platform/Event images" (ID in Script Property IMAGE_FOLDER_ID)
Local code	Chinese Student Society/CSS Ticketing System V2/ (OneDrive), a git repo
GitHub repo	https://github.com/cssuofcalgary/css-platform (can go private; Vercel is linked to it)
Public site (live)	https://events.ucalgarycss.ca/ (event page: /?e=<slug>, ticket: /ticket.html?t=<secret>)
Exec Portal (live)	https://exec.ucalgarycss.ca/ (door scanner: /scanner/). Layout: theme.css is the look (sidebar, cards, Overview, help-desk Door); the JS files are unchanged logic.
Hosting	Vercel for both sites (events and exec), built from the GitHub repo on every push to main, about a minute or two. The member pass site (member.ucalgarycss.ca) is a separate repo, cssuofcalgary/member-portal, also on Vercel. See "Hosting on Vercel" below. GitHub Pages is not used any more.

3. Files

File	What it does
api/appsscript.json	Manifest: timezone, permissions (Sheets, Drive, send email), web app = run as owner, anyone can call
api/Api.js	Entry point. doGet = health check. doPost = routes every action. Login + sessions
api/Config.js	Reads Script Properties. checkSetup() = one-click setup test (run from the editor)
api/Membership.js	Joining the club. publicRegisterMember_ (public sign-up: checks, honeypot, limits, duplicate check by UCID or email, new row on the Membership sheet), addMember_ (exec adds by hand, optionally paid at once), markMemberPaid_ (sets Paid Status PAID, emails the pass, sets Mail Status Sent), listPendingMembers_. The only code that writes to the Membership sheet. Member IDs look like CSS + row + 4 digits (same as the old Generate IDs button)
api/Members.js	Member model: turns Membership-sheet rows into Member objects, member search. Pure logic
api/Store.js	Reads the Membership sheet (cached 5 min; if Google fails, an older copy up to 6 h old is used instead of an error). Also the only place that WRITES to it: openMemberSheet_ (a fresh read; adds any missing columns at the end of the header row), appendMemberRow_, setMemberCells_, forgetMembers_ (drops the cached copy after a write)
api/Db.js	The platform data sheet: creates tabs, reads rows, inserts/updates by id, change log, lock. retry_() repeats a Sheets call up to 3 times when Google has a momentary failure (reads and updates only, never inserts)
api/Events.js	Events: validation, save, publish/close, image upload, public event views
api/Orders.js	Registration: one Order (payment code) + one Ticket per person, membership check (flags, never blocks), duplicate check, anti-spam
api/TestKit.js	Stress-test kit, run by hand from the Apps Script editor (not reachable from the web): buildStressTest() makes two closed test events, 46 varied fake attendees (all @example.com) and emails a sheet of 50 QR codes with the expected result of each scan; re-sends it; archives both events. Run once

Hosting on Vercel (events.ucalgarycss.ca + exec.ucalgarycss.ca)

One GitHub repo, two Vercel projects, each serving one folder as a static site (no build step):

Vercel project	Root Directory	Domain
css-events	public	events.ucalgarycss.ca
css-exec	exec-portal	exec.ucalgarycss.ca (and /scanner/)

Framework preset Other, no build command, no output directory. Each folder has a vercel.json (always re-check for fresh files; the exec site is kept out of search engines). DNS (Akhil, who owns the domain): a CNAME for events and one for exec, pointing at the value Vercel shows for each project. Every push to main redeploys both automatically. exec-portal/config.js picks the public site address by host name: on exec.ucalgarycss.ca it uses https://events.ucalgarycss.ca/, elsewhere (GitHub Pages, local) the sibling folder. Ticket links in emails follow it (the PUBLIC_SITE_URL Script Property is refreshed the next time Finance marks an order paid; press Resend tickets to send fresh links for existing orders). Old GitHub Pages links keep working while Pages stays on, and old QR codes keep scanning (the scanner reads only the secret after t=). The API address (Apps Script) is the same from every site; nothing to change there.

Layout modes (exec-portal)

• Wide screens (960 px+): sidebar navigation, a shared event picker in the top bar (Payments and Door follow it), a stat strip and a table-style order list on Payments. Pure CSS under @media (min-width: 960px) at the bottom of styles.css, plus renderGlobalEvent() in events.js.
• Scanner mode: exec-portal/?mode=scanner (also reachable at scanner/, which just redirects there). Shows only the Door tab: camera, "inside" counter, name/ticket search, Undo. Hides walk-ins, the unpaid and flagged lists, entry open/close and every other tab, and never lets an unpaid or flagged person in ("Send to help desk"). Phones are sent here automatically; ?full=1 on a phone opens the full portal. It is a convenience view, not a security boundary: the same exec password signs in and the API still allows exec actions. A real door-only role would need its own password and an action allow-list.
• When the site moves to Vercel with exec.ucalgary.ca, add a rewrite so /scanner opens /exec-portal/?mode=scanner (or keep the scanner/ folder). A separate subdomain isn't needed.

4. Data tables ("CSS Platform Data")

Row 1 = column names. Don't rename tabs or row-1 names. Rows are matched by id, so sorting or filtering by hand is safe. Lists (ticketTypes, questions, answers, summary) are stored as JSON text.

Only current events live here. Thirty days after an event, the nightly job moves that event's Tickets and Orders rows into a yearly archive spreadsheet (CSS Platform Archive 2026-27, September to August, in the Drive folder CSS Platform Archive; same tabs and columns) and writes the event's final numbers into the Events row: archivedAt, archiveYear, summary (totals, money, per ticket type, answer counts, spots taken) and status = archived. The Events tab always keeps every event. An admin can do the same by hand with Archive on the Events list, and Restore brings the rows back (status closed). Why: every request reads the whole Tickets and Orders tabs; with old events gone, those stay small and the shared table cache (limit about 4,800 tickets) keeps working. Nothing is deleted. Code: api/Archive.js.

Tab	Columns
Events	id, slug, name, description, date (YYYY-MM-DD), startTime, endTime (HH:mm), registrationCloses (YYYY-MM-DDTHH:mm, Calgary time; blank = when the event starts), location, capacity (blank = no limit), capacityRule (paid/all), status (draft/published/closed/archived), entryOpen, imageFileId, imageUrl, ticketTypes, questions, codePrefix, createdBy, createdAt, updatedBy, updatedAt
Orders	id, code (payment code, e.g. MGN-4821), eventId, payerName, payerEmail, etransferName, total, status (awaiting/paid/refunded/cancelled; free orders start paid), createdAt, paidAt, paidBy, notes, remindedAt (when the last "please pay" reminder went out; text like "test address, not sent" for @example.com)
Tickets	id (TKT + 8 chars), secret (for the ticket link), orderId, eventId, name, email, ucid, memberId, ticketType (name), price, answers (question label → answer), flag (why the help desk should check, e.g. membership not found, duplicate email), status (same as its order), checkedInAt, checkedInBy, createdAt, emailedAt (when the ticket email went out; blank = waiting; "test address, not sent" for @example.com)
Log	time, who, action, target, details. Every change made through the portal
Feedback	id (= the ticket id), eventId, memberId, name, rating (1 to 5), comment, createdAt, updatedAt
Waitlist	id, eventId, name, email, ucid, memberId, ticketTypeId, ticketType, answers, status (waiting/offered/removed), createdAt, offeredAt, offeredBy, orderId, orderCode, notes
Partners	id, name, offer, address, active, sort, createdAt, createdBy, updatedAt, updatedBy. (active can come back as true or TRUE: read it with isOn_)
Redemptions	id, time, receivedAt, memberId, memberName, partnerId, partnerName, offer

Also added in Oct 2026: Orders cancelRequestedAt, cancelRequestedBy (a ticket page "cancel" request; shown to Finance while the order is awaiting), Events waitlist (TRUE/FALSE), and a ticket's emailedAt can read claim:<time> while an email is being sent (a claim older than 10 minutes counts as unsent). New columns only appear after API_VERSION (api/Config.js) changes, because column names are cached per version.

ticketTypes item: { id, name, price, needsMembership } · questions item: { id, label, type: "text"|"choice", options: [], required }

5. API actions

Action	Login?	Does
publicEvents	no	Upcoming published events (public fields only)
publicEvent {slug}	no	One published/closed event + registrationOpen, soldOut, spotsLeft, e-transfer email
login {password, name}	—	Exec password → role exec, admin password → role admin. Returns a token (valid 6 h). 30 wrong tries in 10 min → 10-min pause for new sign-ins
register {slug, people[], etransferName, website, requestId?, member?}	no	people[0] = payer, others = friends. Each: name, email, ucid?, memberId?, ticketTypeId, answers {questionId: value}. Max 10. website must be empty (bot trap). Same email max 5 registrations / 10 min. Returns payment code, total, e-transfer email, and each ticket with its flag and page (the ticket page: payment status until paid, then the QR); emails the payer. The event site and the member portal both use it. member {memberId, k} (member portal only): a valid pass key proves who is registering, so the payer's details are filled in from the Membership sheet and the member price is not questioned (unless membership is unpaid). Without a key a typed member ID is only a claim: the name must share a word with the member on file, and ID and UCID must point at the same member, otherwise the ticket is flagged and a free member ticket waits for review. requestId makes retries safe: it is saved on the order (Orders.requestId), so a retry after the 10-minute cache has gone still returns the same order, and a registration that was only half written (order without all its tickets) is finished instead of duplicated. If the tickets can't be written, the order is cancelled with a note starting "registration failed" and the page is told to try again. The event is read again inside the lock (closed, archived or edited meanwhile). A free registration also takes the main lock, so it can't hand out the last spot at the same moment as Mark paid
memberProfile {memberId, k}	pass key	The member portal's registration form: name, email, UCID and paid status of the member, to pre-fill. Only for the member's own pass key
restoreOrder {orderId, force?}	exec

6. Settings (Apps Script → ⚙ Project Settings → Script Properties)

Property	Meaning
MEMBERSHIP_SHEET_ID	Membership sheet (link or ID). Required
EXEC_PASSWORD	Exec sign-in. Required. Change any time; takes effect immediately
ADMIN_PASSWORD	Admin sign-in (admin-only tools, coming later). Optional
MEMBERSHIP_TAB	Default Form Responses 1
ETRANSFER_EMAIL	Default [email protected]
CONTACT_EMAIL, INSTAGRAM_URL	Shown at the bottom of the public pages (the API sends them; public/config.js is only the fallback)
LINK_KEY	Set by the system the first time a private link is made. Signs the links in "Find my pass / tickets" emails. Delete it to cancel every link ever sent (a new one is made automatically; members just ask for a new link)
SCANNER_PASSWORD	Optional door password. Set/removed in Settings → Passwords. Blank = door volunteers sign in with just a name (only while entry is open)
EMAIL_SIGNER_ROLE, EMAIL_BUTTON_COLOR	Optional. Role line under the name and the button colour (like #824a24) in every email. Set from Settings → Email wording; blank = standard
PRESIDENT_NAME	Optional. Name in the signature of every email. Default "Gordon Chen". Change it when the President changes
ARCHIVE_SHEETS, ARCHIVE_FOLDER_ID	Set by the system the first time an event is archived: the archive spreadsheet of each school year (JSON, {"2026-27": "sheetId"}) and the Drive folder holding them. Don't edit; losing ARCHIVE_SHEETS just means the system can't find old rows until it's put back (the files are still in the folder CSS Platform Archive)
BACKUP_FOLDER_ID, LAST_BACKUP, LAST_GOOD_BACKUP, ALERT_SENT_DAY	Set by the system by the nightly job (backup folder, when the last backup ran, the once-a-day alert limit)
SESSION_EPOCH,

Everything above except the automatic ones can be changed in the portal's Settings tab (admin). Script Properties is only needed if the admin password is lost. | PUBLIC_SITE_URL | Set by the system from the Exec Portal (its PUBLIC_SITE_URL config) whenever Finance marks paid. Used for ticket links in emails. After moving the public site to a new address, use "Resend tickets" to send fresh links | | DATA_SHEET_ID, IMAGE_FOLDER_ID | Set by the system. Only change if you deliberately point it at a restored copy (see RUNBOOK) |

7. Changing the code

Option A: by hand, no tools needed

1. Open the Apps Script editor (link in section 2), signed in as the CSS account.
2. Edit the file, or paste the new contents of api/<File>.js into the file of the same name. Ctrl+S.
3. Deploy → Manage deployments → ✏ (edit) → Version: New version → Deploy. The URL stays the same. (Using "New deployment" instead creates a new URL, and then every config.js has to be updated.)
4. If appsscript.json permissions changed: select checkSetup → Run → Allow.

Option B: with clasp (needs Node.js; run inside api/)

npm install -g @google/clasp      # once
clasp login                        # sign in as [email protected]
clasp push --force                 # upload the files
clasp update-deployment AKfycbxJ_a_cHyRXadK17ocIebhE2XDFcpEADCzSqOVXwdCgIQe1T-UPi-efzgla2mn7ML2d --description "what changed"

api/.clasp.json already points at the project.

Web pages: edit the files, then publish (PowerShell, inside the V2 folder):

git add -A
git commit -m "what changed"
git push

GitHub Pages updates the live site in about a minute. When you change a .js/.css file, bump the ?v= number on its <script>/<link> line in the page's .html (e.g. ?v=0.2.1 → ?v=0.2.2). Otherwise phones may keep the old copy for a while. No git? Edit the file directly on github.com (✏ icon) and commit. Text changes only need strings.js. Moving to Vercel later: import the GitHub repo in Vercel (no build step, output = root), add events. / exec. domains, then use "Resend tickets" if old links must change.

Local testing: serve the folder (e.g. python -m http.server 8765 inside CSS Ticketing System V2/) and open http://localhost:8765/exec-portal/ or /public/. Empty API_URL in exec-portal/config.js = demo mode with made-up members (password demo / demo-admin).

8. Design rules (keep these)

• Never find rows by row number. Always by id. (Row numbers were how the old pay page marked the wrong person paid after a sort.)
• Anything that changes over time is data or a setting, never code (prices, questions, passwords, emails).
• All page text lives in strings.js, so translating = adding a block.
• Writes happen inside withLock_, so two execs or two scanners can't clash.
• Every change goes in the Log tab with who did it.
• Ticket QR codes hold the ticket page's address (ticket.html?t=<secret>). The secret is long and random, so tickets can't be guessed. The scanner reads the t= part, so tickets keep working even if the site moves.
• Test with @example.com emails: the system never sends to them, so tests don't use the daily email limit or spam anyone.
• Membership checks flag, never block: a wrong or unpaid member ID still gets a ticket, flagged for the help desk (the scanner sends flagged people there).
• Public pages match the member portal's look. Change colours only via the variables at the top of public/public.css. Codes people must type (order codes) use the plain font, not the pixel font.
• Keep it free: Google Apps Script + Sheets + Drive + Gmail (about 100 emails/day on a regular Gmail account).

Speed: table cache and paging (API 0.4.0)

• api/Db.js keeps Events, Tickets and Orders in Apps Script's shared cache under a version number. Every write bumps the version after the write, so the next read is always fresh; a cold or failed cache just reads the sheet. The Log is never cached. Edits made by hand in the data sheet can take up to 30 min to show (use the portal).
• A scan never trusts the cache for the decision: it re-reads that one ticket row from the sheet (readRowFresh_) under the lock.
• Signing in warms the cache (warmCaches_), so the first tab click afterwards is quick.
• Payments, Attendees and Door load one small page / summary first and fetch more on demand (see the API table).

Tests

tests/ has offline tests that run every api/*.js file in Node with a fake Google (in-memory tables, no email sent): node tests/core.test.js, waitlist.test.js, partners.test.js, emailclaim.test.js, deskalert.test.js, payfix.test.js, registration.test.js, doorundo.test.js, hardening.test.js, emailsettings.test.js, money.test.js, mytickets.test.js. Run all twelve before every deploy. See tests/README.md.

Deploying

In api/: clasp push, then clasp deploy -i <deployment ID> -d "label" (the same ID keeps the same URL), then git push origin main in the repo root. A deploy only publishes what was pushed, so always push first. Google can serve the old version for up to a minute afterwards. The web pages (public/, exec-portal/) go live from GitHub through Vercel in a minute or two; bump the ?v= numbers in index.html when a file changes so browsers fetch it.