WayFy Database Configuration, Migrations, and Data Model

WayFy’s backend persists all application data in a relational database managed through SQLAlchemy and Flask-Migrate (Alembic). PostgreSQL is the recommended engine for both local development and production, but the stack also supports SQLite for quick local testing. The connection is configured entirely through the DATABASE_URL environment variable, and schema changes are versioned as Alembic migration files in backend/migrations/versions/ — there are currently eleven migration revisions tracking the full history of the schema.

Supported Database Engines

PostgreSQL (Recommended)
SQLite

PostgreSQL is the engine used in production on Render and is the best-tested option for WayFy. Use the standard postgres:// format — WayFy’s app.py automatically converts it to postgresql:// for SQLAlchemy compatibility:

DATABASE_URL=postgres://username:password@localhost:5432/wayfy

SQLAlchemy dropped support for the postgres:// scheme in version 1.4, but many hosting providers (including Render and older Heroku-style configs) still emit it. WayFy’s app.py detects the prefix and rewrites it to postgresql:// at startup so you never need to manually edit connection strings from your host.

SQLite requires no server process and is useful for fast local testing or CI environments where standing up a full Postgres instance is impractical:

DATABASE_URL=sqlite:////absolute/path/to/test.db
# or relative (four slashes = absolute, three = relative):
DATABASE_URL=sqlite:///test.db

SQLite does not support all migration operations (e.g. column type changes with constraints). For full migration fidelity, prefer PostgreSQL.

Running Migrations

WayFy uses Flask-Migrate, which wraps Alembic and ties migration commands into the Flask CLI. All migration operations are run from the backend/ directory with Pipenv using the scripts defined in backend/Pipfile.

Apply all pending migrations

Run the upgrade script to bring your database schema up to the latest revision. This is the command you run after cloning the repo for the first time, and after pulling new migration files from teammates:

cd backend
pipenv run upgrade

Internally this runs flask db upgrade, which applies any unapplied migration files in backend/migrations/versions/ in order.

Generate a new migration after model changes

After modifying a SQLAlchemy model in backend/src/api/models/, auto-generate a new migration file by running:

cd backend
pipenv run migrate

Alembic compares the current model definitions against the last known schema state and writes a new versioned file in backend/migrations/versions/. Always review the generated file before committing — Alembic cannot detect all changes (e.g. column renames) automatically.

Roll back the last migration

If you need to undo the most recent migration (e.g. to revise a model change before it is merged), run:

cd backend
pipenv run downgrade

This runs flask db downgrade, reverting one revision. Run it repeatedly to step back through multiple revisions.

Reset the database (development only)

To drop all tables and re-apply every migration from scratch, use the reset_db script:

cd backend
pipenv run reset_db

reset_db is destructive — it permanently deletes all data in the database. Never run this against a production database.

Seeding Test Data

WayFy provides two Flask CLI commands for populating a development database with realistic data:

Insert Test Users

Creates a specified number of test user accounts with hashed passwords:

cd backend
flask insert-test-users 5

Pass any integer to control how many users are created. Useful for testing pagination, admin dashboards, and user management flows.

Insert Test Data

Runs the full seed script from backend/src/commands.py, which populates places, trips, accessibility reviews, and related records:

cd backend
pipenv run insert-test-data

Use this to get a fully populated local environment with representative data across all eight tables.

Data Model Reference

WayFy’s schema spans eight tables. The diagram below shows the relationships, followed by a detailed field reference for each model.

users
 ├── user_favorites         (user_id → users.id)
 ├── accessibility_reviews  (last_modified_by_id → users.id)
 │    └── accessibility_photos (review_id → accessibility_reviews.id,
 │                              user_id → users.id)
 └── trips                  (user_id → users.id,
                              original_trip_id → trips.id self-FK)
      └── trip_days          (trip_id → trips.id)
           └── trip_day_places (trip_day_id → trip_days.id,
                                favorite_id → user_favorites.id)

places                       (standalone, created_by → users.id)

users

Stores WayFy account records. Passwords are stored as bcrypt hashes — never plain text.

Column	Type	Notes
`id`	integer	Primary key
`firstname`	string(50)	User’s first name
`lastname`	string(100)	User’s last name
`email`	string(120)	Unique; used as login credential
`password`	string(255)	bcrypt hash
`selected_mobility`	text (JSON)	User’s mobility profile stored as a JSON array string (e.g. `'["wheelchair", "walker"]'`); parsed to a list on serialize
`avatar`	string(255)	Filename served from `/api/users/avatar/<filename>`; defaults to `default_avatar.png`
`is_active`	boolean	Whether the account is active
`is_admin`	boolean	Grants access to admin endpoints and `AdminRoute` pages

user_favorites

Saved places for a user, sourced from OpenStreetMap. The combination of user_id + osm_id is enforced as unique via a UniqueConstraint, preventing duplicate saves of the same place by the same user.

Column	Type	Notes
`id`	integer	Primary key
`user_id`	integer	FK → `users.id`
`osm_id`	string(50)	OpenStreetMap element identifier; always stored as a string
`place_name`	string(200)	Display name of the place
`place_label`	string(100)	Full formatted address label
`longitude`	float	WGS 84 longitude
`latitude`	float	WGS 84 latitude
`wheelchair`	string(20)	OSM `wheelchair` tag value (e.g. `yes`, `limited`, `no`)
`osm_type`	string(20)	OSM element type (`node`, `way`, `relation`)
`sub_type`	string(100)	Place category (e.g. `restaurant`, `museum`)
`all_tags`	text (JSON)	Full OSM tags dictionary as a JSON string
`created_at`	datetime	Timestamp when the favorite was saved

accessibility_reviews

Community-submitted accessibility assessments for a place identified by osm_id. The osm_id column has a unique index — one review record per OSM element.

Column	Type	Notes
`id`	integer	Primary key
`osm_id`	string(50)	Unique OSM identifier for the reviewed place
`osm_type`	string(10)	OSM element type; defaults to `'node'`
`place_name`	string(200)	Display name at time of review
`last_modified_by_id`	integer	FK → `users.id` — most recent editor
`wheelchair`	string(10)	Overall wheelchair accessibility rating (`yes`, `limited`, `no`)
`has_ramp`	boolean	Entrance ramp present
`has_elevator`	boolean	Elevator available
`has_accessible_toilet`	boolean	Accessible toilet on premises
`has_accessible_parking`	boolean	Designated accessible parking available
`automatic_door`	boolean	Automatic or power-assisted door at entrance
`description`	text	Free-text notes from the reviewer
`created_at`	datetime	First submission timestamp
`updated_at`	datetime	Last update timestamp

accessibility_photos

Photos attached to an accessibility review, uploaded via Cloudinary.

Column	Type	Notes
`id`	integer	Primary key
`review_id`	integer	FK → `accessibility_reviews.id`
`user_id`	integer	FK → `users.id` — uploader
`filename`	string(255)	Cloudinary public ID or filename; served from `/api/accessibility/photos/<filename>`
`caption`	string(200)	Optional descriptive caption
`created_at`	datetime	Upload timestamp

trips

User-created itineraries. Trips can be public or private, and a trip can be forked from another via original_trip_id.

Column	Type	Notes
`id`	integer	Primary key
`user_id`	integer	FK → `users.id` — owner
`title`	string(200)	Trip name
`description`	text	Optional overview text
`is_public`	boolean	Whether the trip is visible to other users; defaults to `false`
`original_trip_id`	integer	Self-FK → `trips.id`; set when a trip is forked/cloned; nullable
`cover_image`	string(200)	Filename served from `/api/trips/cover/<filename>`; nullable
`created_at`	datetime	Creation timestamp
`updated_at`	datetime	Last modification timestamp

trip_days

Individual days within a trip, each representing one day of the itinerary. Days are ordered by day_number.

Column	Type	Notes
`id`	integer	Primary key
`trip_id`	integer	FK → `trips.id`
`day_number`	integer	Sequential day index within the trip
`date`	date	Calendar date for this day (optional)
`title`	string(200)	Optional day label (e.g. “Arrival Day”)
`notes`	text	Free-text notes for the day

trip_day_places

Ordered places visited on a specific trip day. Each entry links a day to either a saved favorite or a raw place coordinate. Places within a day are ordered by the order column.

Column	Type	Notes
`id`	integer	Primary key
`trip_day_id`	integer	FK → `trip_days.id`
`favorite_id`	integer	FK → `user_favorites.id`; nullable — not set when a place is added without saving it as a favorite
`place_name`	string(200)	Display name of the place
`latitude`	float	WGS 84 latitude
`longitude`	float	WGS 84 longitude
`osm_id`	string(50)	OSM identifier; may be null for manually added places
`sub_type`	string(100)	Place category
`order`	integer	Position within the day’s itinerary
`notes`	text	Visit-specific notes
`visit_time`	time	Planned arrival time
`visit_time_end`	time	Planned departure time

places

User-submitted places not yet in OpenStreetMap, subject to admin moderation. New submissions start as pending; admins approve them via the Flask-Admin interface, after which they appear on the map as the community-approved layer.

Column	Type	Notes
`id`	integer	Primary key
`name`	string(200)	Place name
`longitude`	float	WGS 84 longitude
`latitude`	float	WGS 84 latitude
`sub_type`	string(100)	Place category
`place_label`	string(100)	Full formatted address label
`created_by`	integer	FK → `users.id` — submitter
`status`	string(20)	`'pending'` (awaiting review) or `'approved'` (visible to all users)
`created_at`	datetime	Submission timestamp

Alembic Migration Workflow

Always commit your migration files alongside the model changes that produced them. If two developers generate migrations independently from the same base revision, Alembic will detect a branch and refuse to upgrade until the branches are merged with flask db merge.

The full migration lifecycle for a typical model change looks like this:

# 1. Edit a model in backend/src/api/models/

# 2. Generate the migration file
cd backend
pipenv run migrate
# → Creates backend/migrations/versions/<hash>_description.py

# 3. Review the generated file
cat backend/migrations/versions/<hash>_description.py

# 4. Apply the migration to your local database
pipenv run upgrade

# 5. Commit both the model change and the migration file
git add src/api/models/<name>_model.py migrations/versions/<hash>_description.py
git commit -m "feat: add cover_image column to trips"

To target a specific Alembic revision rather than always stepping one up or down, use Flask’s db commands directly:

# Upgrade to a specific revision
pipenv run flask db upgrade <revision_hash>

# Downgrade to a specific revision
pipenv run flask db downgrade <revision_hash>

# Show current revision
pipenv run flask db current

# Show full migration history
pipenv run flask db history

Get Started

Core Features

Configuration

Architecture

WayFy Database Configuration, Migrations, and Data Model

Supported Database Engines

Running Migrations

Seeding Test Data

Insert Test Users

Insert Test Data

Data Model Reference

Alembic Migration Workflow

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration

Architecture

Documentation Index

​Supported Database Engines

​Running Migrations

​Seeding Test Data

Insert Test Users

Insert Test Data

​Data Model Reference

​Alembic Migration Workflow

Build docs developers (and LLMs) love

Supported Database Engines

Running Migrations

Seeding Test Data

Data Model Reference

Alembic Migration Workflow