OpenLitterMap v5

June 8, 2026 · View on GitHub

Overview

This document covers the v5 locations architecture: what's changing, what's being deprecated, the new database schema, Redis design, and the re-engineered upload flow.

The core principle: locations tables store identity only; all aggregates live in the metrics table and Redis.

v5 Schema Summary

Location tables (countries/states/cities) store identity only. Legacy aggregate columns (total_litter, total_photos, all total_* category counters, manual_verify, littercoin_paid, countrynameb/statenameb, user_id_last_uploaded, etc.) are dropped — all aggregate stats now live in the metrics table + Redis. Redundant string location columns on photos (country, country_code, county, city, display_name, location, road) are also dropped in favour of FKs (country_id/state_id/city_id) plus the raw address_array JSON. The deprecated UpdateLeaderboardsForLocationAction and IncrementPhotoMonth event are replaced by MetricsService + RedisMetricsCollector; the UploadHelper (with its error_country/error_state/error_city sentinels) is replaced by ResolveLocationAction.

v5 Target Schema

countries table

-- Keep
id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
country         VARCHAR(255) NOT NULL        -- Display name
shortcode       VARCHAR(2) NOT NULL UNIQUE   -- ISO 3166-1 alpha-2
created_by      BIGINT UNSIGNED NULLABLE     -- User who first triggered creation
created_at      TIMESTAMP
updated_at      TIMESTAMP

-- Deprecate (migration to drop)
manual_verify           -- unused
littercoin_paid         -- tracked elsewhere  
countrynameb            -- unused alternate name
user_id_last_uploaded   -- derivable from photos

states table

-- Keep  
id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
state           VARCHAR(255) NOT NULL
country_id      BIGINT UNSIGNED NOT NULL     -- FK → countries
created_by      BIGINT UNSIGNED NULLABLE
created_at      TIMESTAMP
updated_at      TIMESTAMP

UNIQUE KEY (country_id, state)

-- Deprecate (migration to drop)
statenameb              -- unused alternate name
manual_verify           -- unused
littercoin_paid         -- tracked elsewhere
user_id_last_uploaded   -- derivable from photos

cities table

-- Keep
id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY  
city            VARCHAR(255) NOT NULL
country_id      BIGINT UNSIGNED NOT NULL     -- FK → countries
state_id        BIGINT UNSIGNED NOT NULL     -- FK → states
created_by      BIGINT UNSIGNED NULLABLE
created_at      TIMESTAMP
updated_at      TIMESTAMP

UNIQUE KEY (country_id, state_id, city)

-- Deprecate (migration to drop) — ALL total_* columns
total_smoking, total_cigaretteButts, total_food, total_softdrinks,
total_plasticBottles, total_alcohol, total_coffee, total_drugs,
total_dumping, total_industrial, total_needles, total_sanitary,
total_other, total_coastal, total_pathways, total_art, total_dogshit,
manual_verify, littercoin_paid, user_id_last_uploaded

photos table — columns to deprecate

-- Deprecate (migration to drop)
country         -- redundant, use country_id → countries.country
country_code    -- redundant, use country_id → countries.shortcode
county          -- confusingly named (it's state), use state_id → states.state
city            -- redundant, use city_id → cities.city
display_name    -- full OSM address string, move to address_array JSON
location        -- first element of address array, derivable
road            -- second element of address array, derivable

-- Keep
country_id, state_id, city_id  -- foreign keys
address_array                   -- raw OSM response (JSON), source of truth for display_name/location/road
lat, lon, geohash              -- coordinates

metrics table (already exists in v5)

This is the single source of truth for all aggregates. See MetricsService for full schema.

-- Composite unique key
(timescale, location_type, location_id, user_id, year, month, week, bucket_date)

-- Additive counters
uploads, tags, brands, materials, custom_tags, litter, xp

LocationType enum:

  • 0 = Global
  • 1 = Country
  • 2 = State
  • 3 = City

Timescales:

  • 0 = All-time
  • 1 = Daily
  • 2 = Weekly (ISO)
  • 3 = Monthly
  • 4 = Yearly

v5 Location Models

All three models extend the base Location model. They hold identity columns + relationships only; every aggregate is a Redis-backed accessor (see base model section below).

Country model (cleaned)

app/Models/Location/Country.php — $fillable: country, shortcode, created_by. getRouteKeyName() returns 'country'. Relationships: states() / cities() (hasMany). Carries the shared $appends aggregate accessors (total_litter_redis, total_photos_redis, total_contributors_redis, litter_data, brands_data, objects_data, materials_data, recent_activity, total_xp, ppm, updatedAtDiffForHumans, total_ppm).

State model (cleaned)

app/Models/Location/State.php — $fillable: state, country_id, created_by. Same $appends as Country. Relationships: country() (belongsTo), cities() (hasMany).

City model (cleaned)

app/Models/Location/City.php — $fillable: city, country_id, state_id, created_by. Same $appends as Country. Relationships: country() / state() (belongsTo).

Location base model $appends

All aggregate data (total_litter_redis, total_photos_redis, etc.) should be computed from Redis, which is populated by RedisMetricsCollector and rebuildable from the metrics table. The base Location model provides these accessors.

Redis Key Design (v5)

Redis is a derived cache — rebuildable from the metrics table at any time.

Scope prefixes (from LocationType enum)

global              → LocationType::Global
country:{id}        → LocationType::Country  
state:{id}          → LocationType::State
city:{id}           → LocationType::City

Key patterns managed by RedisMetricsCollector

All keys use RedisKeys::* builders (single source of truth). See app/Services/Redis/RedisKeys.php.

# Aggregate counters (HINCRBY on hash keys)
{scope}:stats                   → HASH { uploads, tags, litter, xp, brands, materials, custom_tags }

# Contributors (PFADD on HyperLogLog)
{scope}:hll                     → HyperLogLog of user IDs (~0.81% error, O(1) reads)

# Per-tag counters
{scope}:obj                     → HASH { object_id: count }
{scope}:mat                     → HASH { material_id: count }
{scope}:brand                   → HASH { brand_id: count }
{scope}:cat                     → HASH { category_id: count }

# Tag rankings (ZINCRBY on sorted sets)
{scope}:rank:objects             → ZSET { object_id: count }
{scope}:rank:materials           → ZSET { material_id: count }
{scope}:rank:brands              → ZSET { brand_id: count }

# Leaderboards (ZINCRBY on sorted sets)
{scope}:lb:xp                   → ZSET { user_id: xp }

# Per-user
{u:$userId}:stats               → HASH { uploads, xp, litter }
{u:$userId}:bitmap               → BITMAP (activity streak tracking)

Deprecated Redis keys (to remove)

# Old leaderboard format — replaced by {scope}:lb:xp ZSETs
xp.country.{id}
xp.country.{id}.state.{id}
xp.country.{id}.state.{id}.city.{id}
leaderboard:country:{id}:total
leaderboard:state:{id}:total
leaderboard:city:{id}:total
leaderboard:country:{id}:{year}:{month}:{day}
leaderboard:state:{id}:{year}:{month}:{day}
leaderboard:city:{id}:{year}:{month}:{day}
leaderboard:country:{id}:{year}:{month}
leaderboard:state:{id}:{year}:{month}
leaderboard:city:{id}:{year}:{month}
leaderboard:country:{id}:{year}
leaderboard:state:{id}:{year}
leaderboard:city:{id}:{year}

# Old location format — replaced by {scope}:stats, {scope}:hll
country:*:user_ids
state:*:user_ids
city:*:user_ids

Migrations

The deprecated-column drops have shipped. See the migration files for the exact column lists:

  • Drop deprecated location columns (manual_verify, littercoin_paid, countrynameb/statenameb, user_id_last_uploaded, and all cities.total_* category counters) — database/migrations/2026_02_21_212414_drop_deprecated_locations_columns.php
  • Drop deprecated photo string columns (country, country_code, county, city, display_name, location, road) — database/migrations/2026_03_14_191950_drop_phase_a_columns_from_photos_table.php

Photo-column drops were run only after confirming no code reads the string columns (display values are derived from address_array instead).

Re-engineered Upload Flow

Current flow (v4)

$ \text{UploadPhotoController}::\text{\_\_invoke}() ├── \text{MakeImageAction}::\text{run}() → \text{image} + \text{EXIF} ├── \text{UploadPhotoAction}::\text{run}() \times 2 → \text{S3} + \text{bbox} ├── \text{getCoordinatesFromPhoto}() → \text{lat}/\text{lon} ├── \text{ReverseGeocodeLocationAction}::\text{run}() → \text{OSM} \text{address} ├── \text{UploadHelper}::\text{getCountry}/\text{State}/\text{City} → \text{firstOrCreate} \text{locations} ├── \text{Photo}::\text{create}() → 20+ \text{columns} \text{including} \text{string} \text{locations} ├── \text{event}(\text{ImageUploaded}) → \text{broadcast} + \text{contributor} \text{counts} ├── \text{UpdateLeaderboardsForLocationAction} → \text{deprecated} \text{Redis} \text{writes} ├── \text{event}(\text{NewCountryAdded}) → \text{notification} ├── \text{event}(\text{NewStateAdded}) → \text{notification} ├── \text{event}(\text{NewCityAdded}) → \text{notification} └── \text{event}(\text{IncrementPhotoMonth}) → \text{deprecated} \text{month} \text{counters} $

New flow (v5)

$ \text{UploadPhotoController}::\text{\_\_invoke}() ├── \text{MakeImageAction}::\text{run}() → \text{image} + \text{EXIF} ├── \text{UploadPhotoAction}::\text{run}() \times 2 → \text{S3} + \text{bbox} ├── \text{getCoordinatesFromPhoto}() → \text{lat}/\text{lon} ├── \text{ResolveLocationAction}::\text{run}() → \text{country}, \text{state}, \text{city} (\text{replaces} \text{UploadHelper}) ├── \text{Photo}::\text{create}() → \text{slim} \text{columns} (\text{FKs} \text{only}, \text{no} \text{string} \text{duplication}) ├── \text{MetricsService}::\text{processPhoto}() → \text{MySQL} \text{metrics} + \text{Redis} (\text{replaces} \text{leaderboards} \text{action}) ├── \text{event}(\text{ImageUploaded}) → \text{broadcast} \text{to} \text{real}-\text{time} \text{map} ├── \text{event}(\text{NewCountryAdded}) → \text{notification} (\text{if} \text{wasRecentlyCreated}) ├── \text{event}(\text{NewStateAdded}) → \text{notification} (\text{if} \text{wasRecentlyCreated}) └── \text{event}(\text{NewCityAdded}) → \text{notification} (\text{if} \text{wasRecentlyCreated}) $

Removed:

  • UpdateLeaderboardsForLocationAction — replaced by MetricsService
  • IncrementPhotoMonth event — replaced by metrics table time-series
  • String location columns from Photo::create()
  • UploadHelper class — replaced by ResolveLocationAction

New ResolveLocationAction

Replaces UploadHelper with cleaner error handling:

namespace App\Actions\Locations;

use App\Models\Location\{Country, State, City};

class ResolveLocationAction
{
    /**
     * Resolve lat/lon to Country, State, City.
     * 
     * @throws \App\Exceptions\GeocodingException
     */
    public function run(float $lat, float $lon): LocationResult
    {
        $revGeoCode = app(ReverseGeocodeLocationAction::class)->run($lat, $lon);
        $address = $revGeoCode['address'];

        $country = $this->resolveCountry($address);
        $state = $this->resolveState($country, $address);
        $city = $this->resolveCity($country, $state, $address);

        return new LocationResult(
            country: $country,
            state: $state,
            city: $city,
            addressArray: $address,
            displayName: $revGeoCode['display_name'],
        );
    }

    private function resolveCountry(array $address): Country
    {
        $code = $address['country_code'] ?? null;

        if (!$code) {
            throw new \App\Exceptions\GeocodingException('No country_code in geocode response');
        }

        return Country::firstOrCreate(
            ['shortcode' => strtoupper($code)],
            ['country' => $address['country'] ?? '', 'created_by' => auth()->id()]
        );
    }

    private function resolveState(Country $country, array $address): State
    {
        $name = $this->lookup($address, ['state', 'county', 'region', 'state_district']);

        if (!$name) {
            throw new \App\Exceptions\GeocodingException('No state found in geocode response');
        }

        return State::firstOrCreate(
            ['state' => $name, 'country_id' => $country->id],
            ['created_by' => auth()->id()]
        );
    }

    private function resolveCity(Country $country, State $state, array $address): City
    {
        $name = $this->lookup($address, ['city', 'town', 'city_district', 'village', 'hamlet', 'locality', 'county']);

        if (!$name) {
            throw new \App\Exceptions\GeocodingException('No city found in geocode response');
        }

        return City::firstOrCreate(
            ['country_id' => $country->id, 'state_id' => $state->id, 'city' => $name],
            ['created_by' => auth()->id()]
        );
    }

    private function lookup(array $address, array $keys): ?string
    {
        foreach ($keys as $key) {
            if (!empty($address[$key])) {
                return $address[$key];
            }
        }
        return null;
    }
}

New LocationResult DTO

namespace App\Actions\Locations;

use App\Models\Location\{Country, State, City};

class LocationResult
{
    public function __construct(
        public readonly Country $country,
        public readonly State $state,
        public readonly City $city,
        public readonly array $addressArray,
        public readonly string $displayName,
    ) {}
}

New UploadPhotoController (v5)

namespace App\Http\Controllers\Uploads;

use Geohash\GeoHash;
use App\Models\Photo;
use App\Events\{ImageUploaded, NewCityAdded, NewCountryAdded, NewStateAdded};
use App\Actions\Photos\{MakeImageAction, UploadPhotoAction};
use App\Actions\Locations\ResolveLocationAction;
use App\Services\Metrics\MetricsService;
use App\Http\Controllers\Controller;
use App\Http\Requests\UploadPhotoRequest;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\Auth;

class UploadPhotoController extends Controller
{
    public function __construct(
        private MakeImageAction $makeImageAction,
        private UploadPhotoAction $uploadPhotoAction,
        private ResolveLocationAction $resolveLocationAction,
        private MetricsService $metricsService,
    ) {}

    public function __invoke(UploadPhotoRequest $request): JsonResponse
    {
        $user = Auth::user();
        $file = $request->file('photo');

        // 1. Process image & extract EXIF
        $imageAndExif = $this->makeImageAction->run($file);
        $image = $imageAndExif['image'];
        $exif = $imageAndExif['exif'];
        $dateTime = getDateTimeForPhoto($exif);

        // 2. Upload full image + bbox thumbnail
        $imageName = $this->uploadPhotoAction->run($image, $dateTime, $file->hashName());
        $bboxImageName = $this->uploadPhotoAction->run(
            $this->makeImageAction->run($file, true)['image'],
            $dateTime,
            $file->hashName(),
            'bbox'
        );

        // 3. Resolve location from GPS coordinates
        $coordinates = getCoordinatesFromPhoto($exif);
        $lat = $coordinates[0];
        $lon = $coordinates[1];

        $location = $this->resolveLocationAction->run($lat, $lon);

        // 4. Create photo (slim — no string location duplication)
        $photo = Photo::create([
            'user_id' => $user->id,
            'filename' => $imageName,
            'datetime' => $dateTime,
            'remaining' => !$user->picked_up,
            'lat' => $lat,
            'lon' => $lon,
            'model' => $exif['Model'] ?? 'Unknown',
            'country_id' => $location->country->id,
            'state_id' => $location->state->id,
            'city_id' => $location->city->id,
            'platform' => 'web',
            'geohash' => (new GeoHash())->encode($lat, $lon),
            'team_id' => $user->active_team,
            'five_hundred_square_filepath' => $bboxImageName,
            'address_array' => json_encode($location->addressArray),
        ]);

        // 5. Broadcast to real-time map
        event(new ImageUploaded($user, $photo, $location->country, $location->state, $location->city));

        // 6. Notify on new locations
        if ($location->country->wasRecentlyCreated) {
            event(new NewCountryAdded($location->country->country, $location->country->shortcode, now()));
        }
        if ($location->state->wasRecentlyCreated) {
            event(new NewStateAdded($location->state->state, $location->country->country, now()));
        }
        if ($location->city->wasRecentlyCreated) {
            event(new NewCityAdded(
                $location->city->city, $location->state->state, $location->country->country,
                now(), $location->city->id, $lat, $lon, $photo->id
            ));
        }

        // 7. MetricsService processes after tags are added (not here)
        // Tags are added in a separate step. MetricsService::processPhoto()
        // is called when the user submits tags, not at upload time.
        // At upload time, the photo has 0 tags and 0 XP.

        return response()->json(['success' => true]);
    }
}

When MetricsService Runs

Important distinction: photo upload ≠ photo tagging.

  1. Upload — the controller above creates the photo with coordinates, image, and location FKs. No tags yet.
  2. Tagging — the user adds tags (litter categories, materials, brands) in a separate request. This is when MetricsService::processPhoto() should run, because that's when tags, XP, and litter counts exist.

If tags are submitted at upload time (e.g. pre-tagged uploads), then MetricsService::processPhoto() can be called at the end of the upload controller. But for the typical web flow where tagging is separate, the metrics call belongs in the tagging controller.

Files to Delete / Deprecate

FileActionReason
App\Helpers\Post\UploadHelperDeleteReplaced by ResolveLocationAction
App\Actions\Locations\UpdateLeaderboardsForLocationActionDeleteAlready @deprecated, replaced by MetricsService
App\Actions\Locations\UpdateLeaderboardsXpActionDeleteCalled only by the above
App\Events\Photo\IncrementPhotoMonthDeleteReplaced by metrics table time-series

Migration Checklist

  1. Create ResolveLocationAction + LocationResult DTO
  2. Create GeocodingException for proper error handling
  3. Update UploadPhotoController to v5 flow
  4. Update ImageUploaded event if it references deprecated photo columns
  5. Verify no code reads the deprecated photo string columns (country, county, city, etc.)
  6. Run Migration 1: drop deprecated location columns
  7. Run Migration 2: drop deprecated photo columns
  8. Delete UploadHelper, UpdateLeaderboardsForLocationAction, IncrementPhotoMonth
  9. Clean up old Redis keys (run a one-off script to delete the deprecated key patterns)
  10. Update $fillable on Country, State, City models
  11. Remove $appends entries that reference deleted columns (if any)

API Endpoints (Location Data)

LocationController (v1) — Browsing UI

app/Http/Controllers/Location/LocationController.php serves the hierarchical location browsing.

EndpointDescription
GET /api/v1/locationsGlobal view: list of countries with stats
GET /api/v1/locations/{type}/{id}Drill into country/state/city

Response format:

{
    "stats": { "countries": 120, "photos": 50000, "tags": 120000, ... },
    "locations": [
        {
            "id": 1,
            "name": "Ireland",
            "shortcode": "IE",
            "total_tags": 5000,
            "total_images": 1200,
            "total_members": 45,
            "xp": 15000,
            "created_at": "2020-01-15 10:00:00",
            "updated_at": "2026-02-28 14:30:00",
            "pct_tags": 3.3,
            "pct_photos": 2.4,
            "avg_tags_per_person": 111.1,
            "avg_photos_per_person": 26.7,
            "created_by": "username",
            "last_updated_by": "username2",
            "last_updated_at": "2026-02-28 14:30:00"
        }
    ],
    "location_type": "country",
    "breadcrumbs": [ { "name": "World", "type": "global", "id": null } ],
    "activity": { "today": { ... }, "this_month": { ... } }
}

Key naming: locations (not children) and location_type (not children_type). Children use total_tags, total_images, total_members (not tags, photos, contributors). Pinia store useLocationsStore reads these keys.

Time filtering: ?period=today|yesterday|this_month|last_month|this_year or ?year=2024. Mutually exclusive.

Legacy endpoints (Redis-backed)

All location aggregate data is served from Redis (fast) with MySQL metrics table as the source of truth (rebuildable).

EndpointSourceNotes
GET /api/countriesDB + Redis appendsList with aggregates from Redis
GET /api/countries/{country}DB + Redis appendsSingle country with full data
GET /api/countries/{country}/statesDB + Redis appendsStates within country
GET /api/states/{state}/citiesDB + Redis appendsCities within state
GET /api/leaderboardRedis sorted sets{scope}:lb:xp (see readme/Leaderboards.md)

The $appends on location models (total_litter_redis, total_photos_redis, etc.) read directly from Redis hashes, making these endpoints fast without any MySQL aggregate queries.