Project A — Quarterly Research Activity Tracker

Part 1 · The case

Dr Ilyas coordinates research reporting at a university unit with about 30 active researchers. Every quarter he chases people on WhatsApp for their publications, grants, industry engagements, and events, then hand-merges everything into a master spreadsheet. Two people always miss the deadline. Three send screenshots of a Word doc.

He doesn't want to make researchers sign up with passwords. Most of them would never finish onboarding, and the rest would forget the password by next quarter. He just wants a link they open, type their name and email, and start filling a familiar spreadsheet-style grid — while he sees everyone's data on one dashboard.

That's what this project builds.

Part 2 · What you're building

A Quarterly Research Activity Tracker. Each researcher self-identifies with name + email, picks a year and quarter (Q1 to Q4), then logs their activities across eight tabs: Publications, Grants, Industry Engagement, Events Organized, Intellectual Property, Competitions, Invited Talks, and Community Service. Every cell auto-saves. The coordinator signs in as admin and sees everything in one place.

• Researcher side: one page, type name + email (or search a returning email), pick year + quarter, click a category tab, add rows in a spreadsheet-style grid. Every edit saves by itself.
• Admin side: sign in with password or Google. Three pages — Submissions (filter, edit, export to Excel), Analytics (charts + leaderboard), and the researcher-facing page for demos.

Part 3 · Who uses it & how

Part 4 · The Power Prompt to start

A "table" is just a list of rows inside the app's storage (the database). "JSON" is a flexible bag of fields inside a single column — useful when each category has different columns and you don't want eight separate tables. You don't need to memorise these words — they're only here because Lovable will use them when it builds the app.

Goal — Build a quarterly research activity tracker. Researchers self-identify with name + email and log activities across eight categories per year and quarter. Admin sees everything on one dashboard.

Input — On the home page a researcher can either search by email (returning user) or type name + email and click Confirm (new user). Then they pick a year and quarter, click a category tab, and add rows.

Layout — Home page has a search box, a name/email pair, and — once confirmed — a period picker (year + quarter) plus eight tabs: Publications, Grants, Industry Engagement, Events Organized, Intellectual Property, Competitions, Invited Talks, Community Service. Each tab is a spreadsheet-style grid with an "Add Row" button. Admin has three pages: /submissions, /analytics, and the home page for demos.

Features — Two tables: researchers (name, email) and research_activities (researcher_id, category, details JSON, evidence link, year, quarter). Auto-save on every edit. Admin sign-in with email/password + Google. Admin can edit and delete any row. Excel export from both Submissions and Analytics (one sheet per category + a leaderboard sheet). Charts on Analytics: entries per category, distribution, top researchers, and per-category breakdowns.

Output — Researcher sees a small "auto-saved" badge next to the period picker. Admin sees a filterable table (10 rows per page per category), KPI cards, a bar chart per category, a distribution pie, and a leaderboard with 🥇🥈🥉 for the top three.

Part 5 · Data & flow

Two tables

• researchers — one row per person. Columns: an id, name, email (used to look them up on return), and the date the row was created.
• research_activities — one row per activity a researcher logs. Columns: an id, the researcher_id (link back to the person), the category (one of the eight tabs), a details field that holds the category-specific columns as JSON, an evidence_link, the year and quarter, and the last time it was edited.

Why JSON for the category columns

Each category has different columns — Publications wants "journal name" and "index quartile", Grants wants "amount" and "status", Community Service wants "program name" and "date". You could make eight separate tables. Or you can keep one table and put the per-category fields inside a single flexible details column — that flexible bag is called JSON. One table, eight shapes, one query to read everything on the admin dashboard.

The security rules — honest edition

These are the rules the database itself enforces before letting anyone read or write a row. This kind of rule is called a policy, and the whole system is called Row-Level Security — the database checks every request row by row.

• Reading rows — anyone can read the researcher list and every activity row. That's a feature here: the coordinator wants transparency, and researchers can see what peers submitted.
• Writing rows — anyone can add a researcher (self-register) and anyone can add an activity row. No allowlist. No gatekeeper.
• Editing & deleting — only the admin. That's the one place the database says "no" to a stranger.
• Admin powers — admin isn't a checkbox on a profile; it's a separate record that says "this account has the admin role" (the same pattern from Lovable Lesson 16).

Part 6 · What matters for this project

From the five foundations in Lesson 1, this project leans on:

1. 1Security (soft identity, honest limits)

   Email is soft proof of identity — anyone can type anything. This project accepts that trade-off in return for zero friction. The database rule that matters is the one that stops non-admins from editing or deleting.

2. 2Auth (only for the admin)

   Only the coordinator signs in. Researchers are recognised by email, not authenticated. Good enough for reporting, wrong for anything the person shouldn't be able to impersonate.

3. 3Database (JSON for shape, Y+Q for reporting)

   One activities table with a JSON details field keeps eight tab-shapes in one place. Year and quarter are first-class columns so the admin can slice the dashboard by period in one query. Always lowercase + trim emails on the way in.

4. 4Cloud (Excel export in the browser)

   The Excel export runs client-side in the admin's browser using SheetJS. No server function to write, no file to store — the admin clicks a button and their browser builds the .xlsx.

5. 5Roles

   One separate table lists which accounts have the admin role, plus a small database function to check it. That's the fence around edit and delete.

Part 7 · Strengths ✅

• Zero friction for researchers — one URL, type email, done. No password reset flow, no confirmation inbox, no lost accounts.
• One table, eight shapes. JSON details keep the schema calm even as categories evolve — add a column to one category without touching the database.
• Year + quarter is baked into every row. Reporting is grouping numbers, not rebuilding the app or exporting eight files.
• The dashboard matches how universities already think — Q1 to Q4, categories on tabs, leaderboard for a nudge.
• Cheap to run — no auth emails to researchers, no password resets, small footprint. Admin sees everything in seconds.

Part 8 · Weaknesses & trade-offs ⚠️

• Email is never verified. A researcher who knows a colleague's email could log entries under their name. Wrong pattern for anything with approvals, money, or grades.
• Everyone can read everyone's entries. Feature here (transparency), wrong for anything private — flip that rule and it becomes a totally different app.
• JSON details are flexible but not validated. A typo in a select value (e.g. 'Scoups Q1') silently splits the analytics. Constrain critical fields with dropdowns in the UI.
• Category schema is code, not data. Adding a ninth category (say, 'Consultancies') is a code change, not a config change. Fine if categories change once a year, painful if quarterly.
• Trailing spaces and case in emails will split someone's totals in half. Always lowercase and trim on the way in, both in the form and in the database.

Part 9 · Extend it

• Add a magic link before the first save each session — the researcher clicks a short-lived link in their inbox to prove the email is really theirs. Turns soft identity into verified identity, still no password.
• Quarter-lock: once a quarter closes, mark those rows read-only for researchers and editable only by admin. One boolean on a settings table + one extra condition in the database rule.
• Light AI moderation: flag improbable entries ('12 Q1 papers this quarter') for the admin to review, without blocking the researcher.
• A public totals counter — 'this quarter, our unit produced X publications and RM Y in grants.' Just totals, no names. Turns the internal tool into a public signal for the unit's website.
• Email digest to the admin on the last day of each quarter — a one-page summary of what came in, what didn't, and who to nudge.