tinyfiles.tv
Upload a location + review

Organization name, category, location, stars, review. It pins to the map + feed instantly (mock).

Your rating:☆☆☆☆☆Tap stars

Map — pinned locations6 pins
📍 TinyFiles Map — mock • pins are demo positions
Sort by:
Category:
Top Rated
Most Complained
How complaints work

Complaint = “I had a problem.” Not a 1-star. A place can have 4 stars and 40 complaints — that tells you something different. File one with the button on each card.

Backend  ·  Template · Proposed · Not connected

⬇ Backend — Reviews Backend — Template

Right now Reviews is local-only — feed is a hardcoded let reviews = [...] in JS, addReview() just unshifts a card, pins are placed by hashPos(name) fake math, fileComplaint() just does reviews[i].complaints++ in the browser, and pin clicks are alert(). This backend is the map to make it real: places + reviews live on the server, stars are one-per-user, complaints are stored separately from stars, map pins come from real lat/lng. Proposed, not connected.

01 / Files to create
api/reviews/feed.php            GET  /api/reviews/feed?cat=all&sort=starsDesc
api/reviews/add.php             POST /api/reviews/add (auth required)
api/reviews/rate.php            POST /api/reviews/rate
api/reviews/complaint.php       POST /api/reviews/complaint
api/reviews/top.php             GET  /api/reviews/top
sql/reviews_tables.sql

Keep the tinyfiles PHP pattern: each endpoint loads /api/bootstrap.php, uses json_in() / json_out(), prepared PDO statements.

02 / Database tables
-- sql/reviews_tables.sql
review_places
  id INT AUTO_INCREMENT PRIMARY KEY
  name VARCHAR(150) NOT NULL          -- "Joe's Pizza"
  cat ENUM('Restaurant','Tech','Retail','Healthcare','Education','Other') NOT NULL
  location VARCHAR(150) NOT NULL      -- "Westtown, PA"
  lat DECIMAL(10,7) NULL               -- real pin, replaces hashPos()
  lng DECIMAL(10,7) NULL
  created_by INT NOT NULL               -- FK -> users.id
  created_at DATETIME NOT NULL
  UNIQUE(name, location)               -- one place row, many reviews

review_reviews
  id INT AUTO_INCREMENT PRIMARY KEY
  place_id INT NOT NULL                 -- FK -> review_places.id
  user_id INT NOT NULL
  stars TINYINT NOT NULL                -- 1-5
  body TEXT NOT NULL                   -- one-line review
  created_at DATETIME NOT NULL
  UNIQUE(place_id, user_id)            -- one review per user per place

review_complaints
  id INT AUTO_INCREMENT PRIMARY KEY
  place_id INT NOT NULL
  user_id INT NOT NULL
  body TEXT NULL                       -- optional "what happened"
  created_at DATETIME NOT NULL
  UNIQUE(place_id, user_id)            -- one complaint per user per place

Stars and complaints are separate tables on purpose — that’s the page’s rule: “Complaint = ‘I had a problem.’ Not a 1-star. A place can have 4 stars and 40 complaints.” Never fold complaints into the star average.

03 / API endpoints & request flow
GET /api/reviews/feed?cat=all&sort=starsDesc
- returns { "ok": true, "reviews": [ { id, name, cat, location,
  lat, lng, stars, complaints, author, body, created_at }, ... ] }
- cat in [all, Restaurant, Tech, Retail, Healthcare, Education, Other]
- sort in [starsDesc, starsAsc, complaintsDesc, complaintsAsc, newest]
- stars = AVG(review_reviews.stars) for that place, 1 decimal
- complaints = COUNT(review_complaints) for that place

POST /api/reviews/add (auth required)
{ "name": "Joe's Pizza", "cat": "Restaurant",
  "location": "Westtown, PA", "stars": 4, "body": "..." }
- validates cat allow-list, name/location 1-150 chars, stars 1-5, body 1-500
- finds or creates review_places row, then INSERT review_reviews
- returns { "ok": true, "place_id": N }

POST /api/reviews/rate
{ "place_id": 12, "stars": 4, "body": "..." }
- auth required, INSERT ... ON DUPLICATE KEY UPDATE (re-review = update)

POST /api/reviews/complaint
{ "place_id": 12, "body": "Waited 2 hours" }
- auth required, one complaint per user per place
- returns { "ok": true, "complaints": 43 } — new count

GET /api/reviews/top
- returns { "ok": true, "topRated": [...3], "mostComplained": [...3] }
- same math as feed, used for the two sidebar boxes

Feed / map / sidebar all read from the same three tables — no second source of truth. Map pins use lat/lng from review_places, not hash math.

04 / Geocoding, moderation & validation (server owns this)
// Geocoding — server only, never the browser:
//   on add, server geocodes "Westtown, PA" -> lat/lng via a server-side
//   geocoder, stores it in review_places. If geocode fails, lat/lng = NULL
//   and the place still lists in the feed, just without a pin.

// Moderation:
//   reviews start live, but a place with 3+ complaints gets flagged
//   for admin review — it does NOT auto-hide. Admin decides.
//   body is plain text — escape on render, never store HTML.

// Validation:
//   name / location: 1-150 chars, trimmed, no URLs in body
//   stars: integer 1-5 only, body: 1-500 chars
//   rate-limit: max 10 reviews / 10 complaints per user per hour

Keep the page’s rule: “Rate any organization — restaurant, tech, shop, anything. Sort by category, stars, complaints.” The backend enforces the one-review / one-complaint part of that.

05 / Frontend wiring
// Replace the hardcoded let reviews = [...] in this page:
let reviews = []; // filled from server, same shape as now

async function loadReviews(){
  const url = '/api/reviews/feed?cat=' + curCat + '&sort=' + curSort;
  const res = await fetch(url);
  const data = await res.json();
  // data.reviews -> renderAll(), same .rev-card + .pin HTML as now
  // pin position: x = (lng - minLng) / span, y = (lat - maxLat) / span
  //   — or keep the CSS map as a fake map until a real map is wired
}

// Replace addReview():
async function addReview(){
  const res = await fetch('/api/reviews/add', { method:'POST',
    headers:{'Content-Type':'application/json'},
    body: JSON.stringify({ name, cat, location, stars: upStars, body }) });
  // on ok: loadReviews(), show "Pinned!" — same toast as now
}

// Replace fileComplaint(i):
async function fileComplaint(placeId){
  const res = await fetch('/api/reviews/complaint', { method:'POST',
    headers:{'Content-Type':'application/json'},
    body: JSON.stringify({ place_id: placeId }) });
  // update complaint count from response, same UI as now
}

// Replace setCat / setSort:
//   setCat / setSort -> loadReviews(), keep .pill active logic

// On load: loadReviews() + fetch('/api/reviews/top') for sidebar
// If API fails, fall back to current hardcoded reviews + sidebar.

Keep the current hardcoded reviews + fake map playable with no backend — if /api/reviews/feed fails, use the in-page list as the fallback.

Copy the snippets above into api/reviews/ + sql/reviews_tables.sql — no GitHub needed. The <pre> blocks are the backend spec: server-owned places + one-review-per-user + complaints kept separate from stars + server geocoding, same layout, placeholders stay until wired.