Architecture

Architecture — Local History Story Map

This page describes the system architecture as of the Final Milestone, including the tech stack, database schema, service layer design, and API structure.

---

Tech Stack

Layer	Technology
Backend	Python 3.11, FastAPI, SQLAlchemy (async), Alembic
Database	PostgreSQL 16
Object Storage	MinIO (local dev) / Supabase S3 (production)
Frontend	Vanilla HTML/JS served via Nginx
Mobile	Capacitor + Android (WebView wrapper)
Infrastructure	Docker Compose, GitHub Actions CI/CD, Render (production)
Linting	Ruff (backend), ESLint 9 (frontend)
Testing	pytest + httpx (backend), Jest + Playwright (frontend), Appium/WebdriverIO (mobile)
External APIs	Google OAuth 2.0, OpenAI Whisper API, Google Gemini API

---

Repository Layout

bounswe2026group2/
├── backend/
│   ├── app/
│   │   ├── main.py             # FastAPI app, CORS, health, router wiring
│   │   ├── core/
│   │   │   ├── config.py       # pydantic-settings (reads .env)
│   │   │   └── deps.py         # get_current_user() JWT dependency
│   │   ├── db/                 # SQLAlchemy ORM models + session + enums
│   │   ├── models/             # Pydantic request/response schemas
│   │   ├── routers/            # auth.py, story.py, users.py, admin.py, transcription.py
│   │   └── services/           # auth_service.py, story_service.py, user_service.py,
│   │                           #   storage.py, transcription_service.py, ai_tagging_system.py
│   ├── alembic/                # Database migration scripts (0001–0018)
│   └── tests/
│       ├── unit/
│       ├── integration/
│       └── api/
├── frontend/
│   ├── *.html                  # Static pages (login, map, story-create, story-detail, …)
│   ├── js/                     # auth.js, likes.js, comments.js, …
│   └── tests/
│       ├── unit/               # Jest unit tests
│       ├── uat/                # Playwright end-to-end acceptance tests
│       └── mobile-e2e/         # Appium/WebdriverIO mobile E2E tests
└── .github/workflows/          # ci.yml, deploy.yml, release.yml

---

Database Schema

Core tables

users

Column	Type	Notes
id	UUID PK
username	VARCHAR(50)	UNIQUE
email	VARCHAR(255)	UNIQUE
password_hash	VARCHAR(255)	NULL for OAuth-only accounts
google_sub	VARCHAR(255)	UNIQUE, NULL for password accounts
display_name	VARCHAR(100)	nullable
bio	TEXT	nullable
role	ENUM(user, admin)	default user
is_active	BOOLEAN	default true
created_at / updated_at	TIMESTAMP

stories

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
title	VARCHAR(255)
summary	TEXT	nullable
content	TEXT
status	ENUM(draft, published, archived)
visibility	ENUM(private, public, unlisted, anonymous)
place_name	VARCHAR(255)	nullable (legacy single location)
latitude / longitude	FLOAT	nullable (legacy single location)
date_start / date_end	DATE	nullable
date_precision	ENUM(year, date)	nullable
view_count	INTEGER	default 0, atomically incremented on detail fetch
created_at / updated_at	TIMESTAMP

media_files

Column	Type	Notes
id	UUID PK
story_id	UUID FK → stories	CASCADE delete
bucket_name	VARCHAR(63)
storage_key	VARCHAR(512)	UNIQUE with bucket
original_filename	VARCHAR(255)
mime_type	VARCHAR(255)
media_type	ENUM(image, audio, video, document)
file_size_bytes	BIGINT
sort_order	INT	default 0
alt_text / caption	TEXT	nullable

Interaction tables

likes

user_id UUID FK → users · story_id UUID FK → stories · composite PK

comments

id UUID PK · user_id FK → users · story_id FK → stories · content TEXT · timestamps

bookmarks

user_id UUID FK → users · story_id UUID FK → stories · composite PK

notifications

id UUID PK · recipient_id FK → users · actor_id FK → users · story_id FK → stories · type ENUM(like, comment, bookmark) · is_read BOOLEAN · timestamps

Final Milestone additions

story_locations

Supports multiple named locations per story (replaces single lat/lng on the stories row for multi-location stories).

Column	Type	Notes
id	UUID PK
story_id	UUID FK → stories	CASCADE delete
latitude	FLOAT	NOT NULL, −90 to 90
longitude	FLOAT	NOT NULL, −180 to 180
label	VARCHAR(255)	nullable display name for this pin
sort_order	INT	default 0
created_at / updated_at	TIMESTAMP

tags

Normalised tag dictionary; slugs are lowercase ASCII for search matching.

Column	Type	Notes
id	UUID PK
name	VARCHAR(100)	UNIQUE
slug	VARCHAR(120)	UNIQUE, lowercased
created_at / updated_at	TIMESTAMP

story_tags (association table)

story_id UUID FK → stories · tag_id UUID FK → tags · composite PK
Composite index on (tag_id, story_id) for tag-based story lookup.

badges

Column	Type	Notes
id	UUID PK
name	VARCHAR(100)
description	TEXT
icon_key	VARCHAR(100)
rule_type	ENUM(BadgeRuleType)	UNIQUE — one badge definition per rule

user_badges

user_id UUID FK → users · badge_id UUID FK → badges · composite PK · awarded_at TIMESTAMP

---

Service Layer

Service	Responsibility
auth_service.py	Register, login, JWT creation/validation, Google OAuth exchange
story_service.py	Story CRUD, location upsert, tag upsert, list/search/nearby/timeline queries, like/comment/bookmark/report logic
user_service.py	Profile update, avatar upload, password change, dashboard/stats aggregation
admin_service.py	Report queue, report status updates, soft-delete stories
transcription_service.py	Async audio transcription via OpenAI Whisper API (whisper-1); returns None gracefully when OPENAI_API_KEY is unset
ai_tagging_system.py	Background tag generation via Google Gemini; strips markdown fences from response; no-ops when GEMINI_API_KEY is unset
storage.py	MinIO / S3-compatible upload, presigned URL generation

Architecture conventions

• Routers — HTTP layer only (auth, validation, response shaping). No business logic.
• Services — All business logic. Called by routers via await.
• Models — Pydantic schemas for request validation and response serialization.
• db/ — SQLAlchemy ORM models, enums, and the async session factory.
• All database access is async: asyncpg driver, AsyncSession.
• Alembic manages migrations — all new ORM models must be imported in alembic/env.py.

---

API Structure

Router	Prefix	Tags
Auth	/auth	auth
Stories	/stories	stories
Users	/users	users
Admin	/admin	admin
Transcription	/transcription	transcription

Full interactive API docs are available at /docs (Swagger UI) and /redoc when the backend is running.

Key endpoints (Final Milestone additions)

Method	Path	Description
GET	/auth/google/login	Initiates Google OAuth flow
GET	/auth/google/callback	Exchanges code for JWT, creates account if needed
GET	/stories?tags=tag1&tags=tag2	Filter public stories by one or more tags (OR matching)
GET	/stories/search?q=query	Hybrid semantic + fuzzy search across title, content, and tags
GET	/stories/timeline	Chronological timeline, filtered by location or place name
GET	/stories/nearby	Haversine geospatial nearby stories
GET	/stories/{id}	Story detail; increments view count
GET	/users/me/stats	Engagement stats including total views received
GET	/users/{id}	Public profile with earned badges
POST	/transcription/preview	Transcribe audio without persisting

---

CI/CD Pipeline

backend-lint + frontend-lint
        ↓
   health check
        ↓
   unit tests
        ↓
integration tests
        ↓
   API tests
        ↓
frontend unit (Jest)   UAT (Playwright)
        ↓                    ↓
         ── test-summary ──
                ↓
            deploy (Render)

The release.yml workflow is manually triggered from main, calls CI + deploy, then publishes a GitHub Release with test results.