EasyLocate — Lost & Found

The constraint was the point of the exercise: a database course, so no ORM. Every query is written by hand against the mysql2 driver, which means the schema carries its own weight and the join logic sits in plain view. What made it more than a CRUD exercise is the matching problem - a lost umbrella and a found umbrella are two rows nobody has connected, and ranking one against the other is a real scoring question.

The naive build is one items table and a search box, and it fails the way every classifieds board fails. The person who lost the thing and the person who found it do not describe it the same way. One posts a black Bombay Dyeing umbrella; the other posts an umbrella, dark, near the library. No shared keyword, no match, and the board is useless exactly when it matters. So the real question is what evidence exists besides the words - what kind of thing it is, where it was, when it was - and what each of those is worth relative to the others. That question is what the schema and the scoring function are both answering.

Eight tables, and what the constraints are doing

• users carries a role enum of admin or user, with indexes on email and role. Registration hashes with bcrypt at cost 10; login issues a seven-day JWT carrying id, email and role.
• locations is split out rather than inlined on the item, holding DECIMAL(10,8) latitude and DECIMAL(11,8) longitude alongside address, city and landmark. Splitting it is what lets an item survive its location being deleted, and gives the haversine calculation a stable place to read from.
• items enums to lost, found or resolved and carries four indexes - status, category, user, posted date - because those are exactly the four columns the listing endpoint filters on. last_updated_at is ON UPDATE CURRENT_TIMESTAMP, so edit time is the database's job.
• Cascade from users to items, set-null from items to locations. Deleting a user takes their posts with them; deleting a location leaves the item standing with a null place. Those are different intentions and the schema says so.
• claims holds two foreign keys into users - claimant and approver - which is why the claims listing joins users twice, once plain and once left-joined as approver. approved_by sets null on delete so a departed moderator does not erase the decision.
• rewards is a ledger, one row per grant, with claim_id set-null on delete so the points survive the claim being removed.
• notifications is deliberately thin: user, message, type string, read flag, timestamp. No template layer, no delivery channel. The type field is what a UI would branch on.
• ai_tags and item_tags are the tagging pair, with model_version on the tag and a DECIMAL(5,4) confidence score on the join, plus a composite unique key so the same tag cannot be attached to an item twice.

The API surface

Six routers under /api, plus a health check and a root index that lists its own endpoints. Items are public to read and authenticated to write. Listing is the dynamic-predicate pattern: WHERE 1=1 as a scaffold, then optional status, category, city and free-text clauses appended with bound placeholders. Column names are hardcoded, values are always parameters, which is the whole discipline of writing SQL by hand safely.

• /api/items for list, detail with tags attached, create, update and delete. Creating an item writes the location row first and threads its insert id back onto the item.
• /api/claims for list, create and the decision endpoint. Non-admins only ever see their own.
• /api/users/profile runs three aggregates beside the user row - item count, claim count and the reward SUM - and /api/users/:id/items gives a public posting history.
• /api/notifications lists with an optional read filter and marks one read, refusing outright if the notification belongs to someone else.
• /api/matches/item/:id scores one item against everything of the opposite status; /api/matches/all scores the full cross product and takes a minScore from the query string.

Ownership checks sit in the handlers rather than in middleware: editing or deleting an item requires being the poster or an admin, and deciding a claim requires being the item's owner or an admin. Image uploads go through multer disk storage with a 5 MB cap, extension and mimetype both tested against the same regex, orphan cleanup on any failed write, and deletion of the previous file when one is replaced.

Claims are the only real state machine

Everything else is rows in and rows out. A claim moves pending to approved or rejected, and approving fans out into four writes: the claim status and approver, the item flipped to resolved, a ten-point row in rewards, and a notification to the claimant. Rejecting writes two of those four. Duplicate claims are refused by a lookup on the item-and-user pair, and a resolved item cannot be claimed at all. Reward balance is never stored - it is a SUM over the ledger every time the profile loads - so the number on screen cannot drift away from the rows that justify it.

The matching score

Candidates are scored out of 100 across five weighted components. Category equality is all-or-nothing at 30. Text similarity contributes up to 25, as cosine over TF-IDF vectors from the natural library built on the concatenated title and description. Distance is worth up to 20, decaying linearly to zero at 50 km from a haversine calculation over the stored coordinates. Recency is up to 15 over 30 days, and city equality adds 10. Results filter at 30 and cap at the top ten per item; the cross-item view filters at 40, and the UI exposes a threshold selector.

Where the model is weak

Worth naming, because reading the numbers back tells you something. Category and recency alone put two same-day items in the same category at 45, above the default threshold, with zero text overlap and no location data, so category equality dominates. And the TF-IDF is fitted per pair over a two-document corpus, which inverts the intent of the IDF term: a word appearing in both items is down-weighted relative to one appearing in only one. Ranking still moves the right way because more overlap means a larger dot product, but the correct version fits IDF once over the whole corpus.

/api/matches/all also scores every lost item against every found item on each request, with no cache and no index to lean on, so it is quadratic in the size of the board.

Nothing is transactional either. Approving a claim is four sequential writes with no BEGIN/COMMIT, and there are no triggers, procedures or views - partly to keep the logic readable, partly because the bootstrap applies the schema by splitting on semicolons, which would shred any DELIMITER block. The tagging tables are the other honest gap: the item detail route reads them, but nothing in the codebase writes them, so the AI tagging is a schema with a reader and no producer. And registration accepts a role from the request body, which means the admin flag is self-service.

How it comes up

Startup does its own provisioning. initDb.js connects to MySQL without naming a database, issues CREATE DATABASE IF NOT EXISTS, reads schema.sql, splits it on semicolons, runs each statement, then re-reads SHOW TABLES to confirm what it built before handing back a pool of ten connections. Every statement is IF NOT EXISTS and the seeded admin account is an INSERT ... ON DUPLICATE KEY UPDATE, because that boot path runs on every start.

Compose puts four containers behind it. MySQL 8 mounts the same schema.sql into docker-entrypoint-initdb.d and gates the API through a mysqladmin ping health check with a thirty-second start period, so the backend does not race the database. The API image carries its own HEALTHCHECK against /api/health. The React build is served by nginx with try_files falling back to index.html for client routing, gzip on, a year of immutable caching on hashed assets and explicit no-cache on the entry document. Adminer rides along on port 8080, pre-pointed at the MySQL service, which for a database assignment is the more useful window - you can open the console and read the rows the API just wrote.

Applying the schema twice, from the container entrypoint and again from the application, looks redundant and is deliberate: it means npm run dev against a bare local MySQL works exactly as well as docker compose up, which is the difference between a project that runs on the grader's machine and one that does not.