Files

T

nngo a5c800e87c Jul 13 - Optimize code 2

2026-07-13 17:07:30 -04:00

51 KiB

Raw Permalink Blame History

CLAUDE.md — Classifieds Marketplace

Master spec and working agreement for an AI coding assistant. Read this fully before writing any code. Follow conventions exactly. Ask before deviating from schema, architecture, or naming.

1. Project Overview

National US classifieds marketplace targeting Vietnamese and Hispanic communities, with full trilingual UI (English / Vietnamese / Spanish) and location-based filtering.

Live site: https://classifieds.ngodanguyen.tech

Mission: Connect people with needs — buy, sell, request, hire, and find services across their local community.

Six listing categories:

For Sale — goods, items
Wanted / Requesting — reverse listings ("looking for X")
Jobs — employer posts + optional job-seeker posts
Services — offering labor/skills
Supplies — bulk/wholesale, B2B
Community — events, announcements, freebies

Each category has subcategories and a JSON-driven field_schema (admin-editable, no redeploy required).

2. Tech Stack (fixed — do not substitute)

Layer	Technology
Language	Python 3.10+
Web framework	Flask (app factory + blueprints)
Database	MySQL 8.0 — `utf8mb4` / `utf8mb4_unicode_ci`. NOT MariaDB.
ORM + migrations	SQLAlchemy 2.x + Alembic (Flask-Migrate)
Cache / queue / rate-limit	Redis
WSGI	Gunicorn (unix socket)
Reverse proxy / static	Nginx
Process manager	systemd
OS	Ubuntu 22.04 Server
Background jobs	systemd timers (expiry/boost/keyword sweeps). RQ planned — not yet installed
Payments	Stripe (Billing + Payment Intents + Tax + Customer Portal)
Email	SMTP relay via Brevo (smart relay, existing infra)
i18n	Flask-Babel
Forms / CSRF	Flask-WTF
Password hashing	Argon2 (`argon2-cffi`)
Image processing	Pillow (re-encode, strip EXIF, thumbnail)
CAPTCHA	Cloudflare Turnstile (bypassed in dev when keys blank)

Current requirements.txt:

Flask==3.0.3
Flask-SQLAlchemy==3.1.1
Flask-Migrate==4.0.7
Flask-Login==0.6.3
Flask-WTF==1.2.1
Flask-Babel==4.0.0
Flask-Limiter==3.8.0
SQLAlchemy==2.0.34
alembic==1.13.2
argon2-cffi==23.1.0
email-validator==2.2.0
itsdangerous==2.2.0
python-dotenv==1.0.1
redis==5.0.8
requests==2.32.3
PyMySQL==1.1.1
gunicorn==23.0.0
Pillow==10.4.0
stripe==10.12.0

3. Architecture

Internet
   │ 443 (TLS via Certbot / Let's Encrypt)
Nginx
   ├─ /static/  → app/static/           (30d cache)
   ├─ /media/   → instance/media/       (7d cache)
   └─ /         → Gunicorn unix socket  → Flask app factory
                                              │
                    ┌─────────────────────────┼──────────────┐
                  MySQL 8.0              Redis            Stripe
                (utf8mb4)        (sessions, cache,    (webhooks,
                                  rate-limit, RQ)      billing)
                                              │
                                         SMTP relay
                                          (Brevo)

ProxyFix applied in app factory (x_for=1, x_proto=1, x_host=1) so request.is_secure, secure cookies, and rate-limit IP are correct behind Nginx.

systemd units (in deploy/):

classifieds.service — Gunicorn web process
classifieds-expire.service + classifieds-expire.timer — hourly: listing expiry
classifieds-nightly.service + classifieds-nightly.timer — 2am: boost expiry + promoted keyword cleanup + subscription reconcile + expiry warning/expired emails

4. Project File Structure

classifieds/
├── wsgi.py                        # WSGI entry point for Gunicorn + flask CLI
├── seed.py                        # Seed plans, categories, zip_geo; --admin flag
├── babel.cfg                      # pybabel extraction config
├── requirements.txt
├── .env.example                   # All env vars (no inline # comments)
├── .gitignore
├── CLAUDE.md                      # This file
├── README.md                      # Setup, run, deploy instructions
│
├── app/
│   ├── __init__.py                # App factory: create_app()
│   ├── config.py                  # BaseConfig / DevConfig / ProdConfig
│   ├── extensions.py              # db, migrate, login_manager, csrf, babel, limiter
│   │
│   ├── models/
│   │   ├── __init__.py            # Exports all models (import order matters)
│   │   ├── enums.py               # Role, UserStatus, TrustTier, TrustEventType,
│   │   │                          #   ListingStatus, Lang, ReportReason
│   │   ├── plan.py                # Plan (tier config JSON)
│   │   ├── user.py                # User (Argon2, RBAC helpers, Flask-Login)
│   │   ├── trust.py               # TrustEvent (append-only trust ledger)
│   │   ├── category.py            # Category (self-ref subcategories, field_schema)
│   │   ├── listing.py             # Listing + ListingImage
│   │   ├── geo.py                 # ZipGeo + Metro
│   │   ├── messaging.py           # Conversation + Message
│   │   ├── favorite.py            # Favorite
│   │   ├── payments.py            # Subscription, Transaction, Boost
│   │   ├── ads.py                 # Ad, Sponsor, PromotedKeyword
│   │   ├── report.py              # Report (user-submitted listing reports)
│   │   ├── audit.py               # AuditLog (append-only admin action trail)
│   │   ├── setting.py             # Setting (key/JSON-value admin runtime config)
│   │   └── review.py              # Review (1–5 star seller reviews, unique per listing+author)
│   │
│   ├── blueprints/
│   │   ├── auth/                  # register, login, logout, verify-email, reset
│   │   ├── main/                  # index, /healthz, /robots.txt, /sitemap.xml,
│   │   │                          #   /classifieds/category/<slug>, /classifieds/state/<state>
│   │   ├── i18n/                  # /lang/<code> locale switcher
│   │   ├── listings/              # browse+promoted, detail, create, edit, delete, images,
│   │   │                          #   report, review
│   │   ├── messaging/             # inbox, conversation, start, favorites, /api/unread
│   │   ├── payments/              # pricing, checkout, portal, webhook, boost, billing
│   │   ├── ads/                   # click tracking, sponsor directory, inject_ads()
│   │   └── admin/                 # full admin backend (dashboard, users, listings,
│   │                              #   reports, categories, plans, ads, sponsors,
│   │                              #   promoted-keywords, transactions, analytics,
│   │                              #   audit log, settings)
│   │
│   ├── services/
│   │   ├── email.py               # send_email (SMTP or dev console)
│   │   ├── turnstile.py           # Cloudflare Turnstile verify
│   │   ├── trust.py               # record_event, trust tier computation
│   │   ├── geo.py                 # geocode_zip, haversine_mi, bounding_box
│   │   ├── field_schema.py        # validate_attributes, hot_values
│   │   ├── images.py              # process_upload, delete_image_files
│   │   ├── listings.py            # create/update/browse/search/radius/expiry +
│   │   │                          #   keyword blocklist enforcement
│   │   ├── messaging.py           # conversations, send, mark-read, inbox, unread
│   │   ├── favorites.py           # toggle, is_favorited, user_favorites
│   │   ├── contact.py             # mask_body, contact_revealed, contact_density
│   │   ├── billing.py             # Stripe checkout, portal, webhooks, sync, reconcile
│   │   ├── ads.py                 # get_ad, record_impression/click, promoted_listings,
│   │   │                          #   active_sponsors, expire_promoted_keywords
│   │   ├── reports.py             # create_report (auto-flag at threshold), ReportError
│   │   ├── moderation.py          # flag_queue, approve, hide, remove (+ audit_log)
│   │   ├── audit.py               # log_action — append AuditLog row, caller commits
│   │   ├── settings.py            # get_setting, set_setting (runtime config from DB)
│   │   ├── admin_dashboard.py     # KPI queries: active_listings, new_users, mrr,
│   │   │                          #   revenue_30d, flag_queue_depth
│   │   ├── admin_users.py         # search_query, set_status, set_tier, adjust_trust
│   │   ├── expiry_notifications.py # warn_expiring(days), notify_expired — email sweeps
│   │   └── reviews.py             # create_review, seller_rating
│   │
│   ├── utils/
│   │   ├── __init__.py            # RBAC decorators: role_required, admin_required,
│   │   │                          #   moderator_required
│   │   ├── security.py            # hash_password, verify_password, generate_token,
│   │   │                          #   read_token (itsdangerous)
│   │   ├── text.py                # normalize() — accent-insensitive (phở→pho, ñ→n)
│   │   └── time.py                # utcnow() — naive-UTC helper (replaces datetime.utcnow)
│   │
│   ├── templates/
│   │   ├── base.html              # Layout: responsive nav, OG/meta blocks, toast JS,
│   │   │                          #   header/footer ad slots, lang switcher
│   │   ├── index.html             # Landing page
│   │   ├── sitemap.xml            # XML sitemap template
│   │   ├── auth/                  # login, register, reset_request, reset, _macros
│   │   ├── listings/              # browse, detail (JSON-LD, reviews), form, mine,
│   │   │                          #   review_form
│   │   ├── main/                  # category_landing.html, state_landing.html
│   │   ├── messaging/             # inbox, conversation, start, favorites
│   │   ├── payments/              # pricing, billing, boost, success
│   │   ├── ads/                   # _slot.html (reusable ad slot partial)
│   │   ├── sponsors/              # directory.html
│   │   ├── admin/                 # dashboard, users, user_detail, listings, reports,
│   │   │                          #   categories, category_schema, plans, plan_edit,
│   │   │                          #   ads, ad_edit, sponsors, sponsor_edit,
│   │   │                          #   promoted_keywords, promoted_keyword_new,
│   │   │                          #   transactions, analytics, audit, settings, _nav
│   │   └── errors/                # 403, 404, 500, maintenance
│   │
│   ├── static/
│   │   └── style.css              # Single CSS file (Phase 1–7 accumulated)
│   │
│   └── translations/              # Flask-Babel .po/.mo for vi + es
│
├── migrations/                    # Alembic migration scripts
│
├── deploy/
│   ├── classifieds.service        # systemd: Gunicorn web
│   ├── classifieds-expire.service # systemd: oneshot listing expiry
│   ├── classifieds-expire.timer   # systemd: hourly
│   ├── classifieds-nightly.service # systemd: boost expiry + keyword cleanup +
│   │                               #   subscription reconcile + expiry emails
│   ├── classifieds-nightly.timer  # systemd: 2am daily
│   ├── gunicorn.conf.py           # workers, socket, timeouts
│   └── nginx.conf.sample          # HTTPS redirect + proxy + static + media
│
└── tests/
    └── test_smoke.py              # Integration test (SQLite + in-memory Redis)
                                   #   isolated temp media dir; all Phase 1–6 checks green

5. Route Map (70+ routes, Phase 1–7)

Public & Auth (Phase 1)

Method	Path	Blueprint	Auth
GET	`/`	main	—
GET	`/healthz`	main	—
GET	`/robots.txt`	main	—
GET	`/sitemap.xml`	main	—
GET/POST	`/auth/register`	auth	—
GET/POST	`/auth/login`	auth	—
GET	`/auth/logout`	auth	login
GET	`/auth/verify/<token>`	auth	—
GET/POST	`/auth/reset`	auth	—
GET/POST	`/auth/reset/<token>`	auth	—
GET	`/lang/<code>`	i18n	—

SEO Landing Pages (Phase 7)

Method	Path	Blueprint	Auth
GET	`/classifieds/category/<slug>`	main	—
GET	`/classifieds/state/<state>`	main	—

Listings (Phase 2–7)

Method	Path	Blueprint	Auth
GET	`/listings`	listings	—
GET	`/listings/new`	listings	login
GET	`/listings/<id>`	listings	—
GET/POST	`/listings/<id>/edit`	listings	login+owner
POST	`/listings/<id>/delete`	listings	login+owner
POST	`/listings/<id>/sold`	listings	login+owner
POST	`/listings/<id>/images/<img_id>/delete`	listings	login+owner
POST	`/listings/<id>/report`	listings	login
GET/POST	`/listings/<id>/review`	listings	login
GET	`/media/<path>`	listings	—
GET	`/my/listings`	listings	login

Method	Path	Blueprint	Auth
GET/POST	`/listings/<id>/contact`	messaging	login
POST	`/listings/<id>/favorite`	messaging	login
GET	`/my/favorites`	messaging	login
GET	`/messages`	messaging	login
GET/POST	`/messages/<id>`	messaging	login+participant
GET	`/api/unread`	messaging	login

Payments (Phase 4)

Method	Path	Blueprint	Auth
GET/POST	`/listings/<id>/boost`	payments	login+owner
GET	`/listings/<id>/boost/success`	payments	login
GET	`/my/billing`	payments	login
GET	`/pricing`	payments	—
GET	`/billing/subscribe/<slug>`	payments	login
GET	`/billing/portal`	payments	login
GET	`/billing/success`	payments	login
POST	`/billing/webhook`	payments	CSRF-exempt

Ads & Sponsors (Phase 5)

Method	Path	Blueprint	Auth
GET	`/ads/<id>/click`	ads	—
GET	`/sponsors`	ads	—

Admin (Phase 6)

Method	Path	Blueprint	Auth
GET	`/admin`	admin	admin
GET	`/admin/users`	admin	admin
GET	`/admin/users/<id>`	admin	admin
POST	`/admin/users/<id>/status`	admin	admin
POST	`/admin/users/<id>/tier`	admin	admin
POST	`/admin/users/<id>/trust`	admin	admin
POST	`/admin/users/<id>/impersonate`	admin	admin
GET	`/admin/listings`	admin	admin/mod
POST	`/admin/listings/<id>/approve`	admin	admin/mod
POST	`/admin/listings/<id>/hide`	admin	admin/mod
POST	`/admin/listings/<id>/remove`	admin	admin/mod
GET	`/admin/reports`	admin	admin/mod
POST	`/admin/reports/<id>/resolve`	admin	admin/mod
GET	`/admin/categories`	admin	admin
GET/POST	`/admin/categories/new`	admin	admin
GET/POST	`/admin/categories/<id>/edit`	admin	admin
GET/POST	`/admin/categories/<id>/schema`	admin	admin
GET	`/admin/plans`	admin	admin
GET/POST	`/admin/plans/<id>/edit`	admin	admin
GET	`/admin/ads`	admin	admin
GET/POST	`/admin/ads/new`	admin	admin
GET/POST	`/admin/ads/<id>/edit`	admin	admin
POST	`/admin/ads/<id>/delete`	admin	admin
POST	`/admin/ads/<id>/toggle`	admin	admin
GET	`/admin/sponsors`	admin	admin
GET/POST	`/admin/sponsors/new`	admin	admin
GET/POST	`/admin/sponsors/<id>/edit`	admin	admin
POST	`/admin/sponsors/<id>/delete`	admin	admin
GET	`/admin/promoted-keywords`	admin	admin
GET/POST	`/admin/promoted-keywords/new`	admin	admin
POST	`/admin/promoted-keywords/<id>/delete`	admin	admin
GET	`/admin/transactions`	admin	admin
POST	`/admin/transactions/<id>/refund`	admin	admin
GET	`/admin/analytics`	admin	admin
GET	`/admin/audit`	admin	admin
GET/POST	`/admin/settings`	admin	admin

6. User Roles & RBAC

Role	Capabilities
free	Register, post (limited), message, buy boosts, sees ads
subscriber	Tier features (Basic/Pro/Business), ad-free
moderator	Flag queue, hide/remove listings, ban users — no billing
admin	Full control

RBAC enforced via decorators in app/utils/__init__.py: @role_required('admin'), @admin_required, @moderator_required

7. Subscription Tiers

Limits stored in plans.config JSON — editable in admin without redeploy.

Feature	Free	Basic ($9.99)	Pro ($24.99)	Business ($59.99)
Active listings	3	15	50	Unlimited
Listing life (days)	14	30	60	90
Images / listing	3	8	15	25
Featured slots / mo	0	1	5	20
Auto-bump	—	—	Weekly	Daily
Analytics	—	Basic	Full	Full + export
Storefront page	—	—	✓	✓ + custom URL
Verified badge	—	—	✓	✓
Ad-free	—	✓	✓	✓
Scheduled posting	—	—	✓	✓
Bulk CSV upload	—	—	—	✓
Priority support	—	—	—	✓

Plan slugs: free, basic, pro, business. Seeded by seed.py.

8. Monetization Streams

Subscriptions — recurring via Stripe Billing ✅ Phase 4
À la carte boosts — featured ($4.99/7d), bump ($1.99/3d), highlight ($2.99/7d), urgent ($1.99/7d) — Stripe Payment Intents ✅ Phase 4
Banner ads — internal ad server, hidden for subscribers ✅ Phase 5
Sponsors — directory + category sponsorship ✅ Phase 5
Promoted search — keyword-pinned listing results ✅ Phase 5

9. Database Schema

All tables: MySQL 8.0, utf8mb4, UTC datetimes, BIGINT PKs with BigInteger().with_variant(Integer, "sqlite"), autoincrement=True for SQLite test compatibility. Money: integer cents always. Never floats.

Identity & Billing

users              id, email (unique), password_hash, display_name,
                   role ENUM(free,subscriber,moderator,admin),
                   status ENUM(active,suspended,banned),
                   locale(5), tier_id→plans, trust_score INT, trust_tier ENUM,
                   verified BOOL, email_verified BOOL, last_login_at,
                   created_at, updated_at

plans              id, slug (unique), name, price_monthly_cents, stripe_price_id,
                   config JSON, is_active, sort_order, created_at, updated_at

trust_events       id, user_id→users, type ENUM(account_age,listing_survived,
                   flag_received,verified_email,payment), delta INT, created_at

subscriptions      id, user_id→users (unique), plan_id→plans,
                   stripe_customer_id, stripe_sub_id (unique),
                   status ENUM(active,past_due,canceled,trialing),
                   current_period_end, cancel_at_period_end BOOL,
                   created_at, updated_at

transactions       id, user_id→users, type ENUM(subscription,boost,refund),
                   amount_cents, currency, stripe_object_id, status,
                   meta JSON, created_at

boosts             id, listing_id→listings, user_id→users,
                   type ENUM(featured,bump,highlight,urgent),
                   expires_at [indexed], transaction_id→transactions, created_at

Catalog

categories         id, slug (unique), name, parent_id→categories (nullable),
                   field_schema JSON, icon, sort_order, sponsor_id (nullable),
                   is_active, created_at, updated_at

listings           id, user_id→users, category_id→categories,
                   title(140), title_norm(140) [indexed, accent-stripped],
                   body TEXT, lang ENUM(en,vi,es), price_cents INT nullable,
                   zip(12), city(80), state(2), lat FLOAT, lng FLOAT,
                   attributes JSON,
                   attr_condition(40) [indexed], attr_job_type(40) [indexed],
                   attr_salary_min INT [indexed], attr_salary_max INT [indexed],
                   status ENUM(active,flagged,sold,expired,removed),
                   is_featured BOOL, bump_at DATETIME, flag_count INT,
                   view_count INT, expires_at DATETIME [indexed],
                   created_at, updated_at
                   Indexes: ix_listing_browse(status,expires_at),
                            ix_listing_sort(is_featured,bump_at)

listing_images     id, listing_id→listings, path(255), thumb_path(255),
                   sort_order, width, height, created_at

Geo

zip_geo            zip PK, city, state [indexed], lat, lng, metro [indexed]
                   (~42k rows from SimpleMaps/Census ZCTA for prod;
                   10-row sample seeded for dev)

metros             id, slug (unique), name, state, center_lat, center_lng
                   (SEO landing pages — Phase 7)

conversations      id, listing_id→listings, buyer_id→users, seller_id→users,
                   last_message_at [indexed], created_at
                   UNIQUE(listing_id, buyer_id)

messages           id, conversation_id→conversations, sender_id→users,
                   body TEXT, read_at DATETIME nullable, created_at

favorites          id, user_id→users, listing_id→listings, created_at
                   UNIQUE(user_id, listing_id)

Ads & Sponsors

ads                id, advertiser_name, slot ENUM(header,sidebar,inline,footer),
                   creative_path, target_url, alt_text,
                   lang nullable, geo_state nullable,
                   starts_at, ends_at, impressions INT, clicks INT,
                   is_active, created_at, updated_at

sponsors           id, name, logo_path, url, tagline,
                   tier ENUM(directory,category),
                   category_id→categories nullable,
                   starts_at, ends_at, is_active, created_at

promoted_keywords  id, keyword(80) [indexed], listing_id→listings,
                   priority INT, expires_at [indexed], created_at
                   UNIQUE(keyword, listing_id)

Admin & Ops (Phase 6)

reports            id, listing_id→listings, reporter_id→users,
                   reason ENUM(spam,scam,offensive,duplicate,miscategorized,other),
                   note, created_at
                   UNIQUE(reporter_id, listing_id)

audit_log          id, actor_id→users, action, target_type, target_id,
                   meta JSON, created_at

settings           key PK, value JSON
                   (registration_open, ads_enabled, maintenance_mode,
                    flag_threshold, keyword_blocklist, new_user_trust_gate_days,
                    contact_density_threshold)

Reviews (Phase 7)

reviews            id, listing_id→listings, author_id→users, seller_id→users,
                   rating SMALLINT (1–5, CHECK CONSTRAINT),
                   body TEXT nullable, created_at
                   UNIQUE(listing_id, author_id)

Deferred

listing_translations  id, listing_id→listings, lang, title, body, cached_at
                      (Phase 7: auto-translate cache — not yet implemented)

10. Key Services (implemented)

`services/listings.py`

create_listing(user, category, ...) — tier-enforced cap, ZIP geocode, accent-norm title_norm, hot-column denorm, expires_at from plan life
update_listing(listing, category, ...) — same validations, in-place update
browse_query(...) — filters: category, state, price, condition, keyword (title_norm LIKE)
search_with_radius(base_query, lat, lng, radius_mi) — bbox prefilter + haversine refine
expire_due_listings() — flips past-due active → expired; hourly systemd timer
can_create(user), active_count(user), image_cap(user) — tier helpers

`services/geo.py`

geocode_zip(zip) → (lat, lng, city, state, metro) from zip_geo table
haversine_mi(lat1, lng1, lat2, lng2) — exact distance in miles
bounding_box(lat, lng, radius_mi) → (min_lat, max_lat, min_lng, max_lng)

`services/field_schema.py`

validate_attributes(category, raw_dict) → (cleaned, errors)
hot_values(cleaned) → attr_* column dict for denormalization

`services/images.py`

process_upload(file_storage, listing_id, sort_order) — MIME validate, Pillow re-encode JPEG, strip EXIF, 1600px max, 400px thumb, random filename
delete_image_files(image) — removes full + thumb from disk

`services/messaging.py`

get_or_create_conversation(listing, buyer) — idempotent, blocks self-message
send_message(conv, sender, body) — validates, contact-density heuristic, saves, updates last_message_at, sends notification email
mark_conversation_read(conv, reader)
inbox(user, page, per_page) — paginated, newest first
total_unread(user) — injected into every template via context processor

`services/contact.py`

mask_body(text, reveal) — hides phone/email/URL for low-trust users
contact_revealed(user) — True when email_verified AND trust_tier >= trusted
contact_density(text) — count of contact signals (auto-flag heuristic)

`services/trust.py`

record_event(user, event_type, delta) — appends event, updates score + tier (new=0, basic=5, trusted=20, verified=50)

`services/favorites.py`

toggle_favorite(user_id, listing_id) → bool
is_favorited(user_id, listing_id) → bool
user_favorites(user_id, page, per_page) → pagination

`services/billing.py`

stripe_enabled() — guard; app runs without keys in dev
get_or_create_customer(user) → Stripe customer ID
create_subscription_checkout(user, plan, success_url, cancel_url) → Stripe URL
create_customer_portal(user, return_url) → Stripe URL
create_boost_checkout(user, listing, boost_type, success_url, cancel_url) → URL
activate_boost(user_id, listing_id, boost_type, payment_intent_id, amount_cents) — idempotent on payment_intent_id; writes Transaction + Boost; applies listing effects
sync_subscription(user_id, stripe_sub_obj) — upserts Subscription, upgrades role/tier
downgrade_to_free(user_id) — reverts role + tier, marks subscription canceled
handle_webhook(payload, sig_header) — verifies signature, dispatches to handlers
expire_boosts() — clears expired boosts, reverts is_featured; nightly timer
reconcile_subscriptions() — compares local vs Stripe; fixes mismatches; nightly timer

Webhook events handled: checkout.session.completed (subscription + boost), customer.subscription.updated/created/deleted, invoice.payment_failed

`services/ads.py`

get_ad(slot, lang, state) — targeted → untargeted → any fallback chain
record_impression(ad_id) — increments ads.impressions
record_click(ad_id) — increments ads.clicks
promoted_listings(keyword) — accent-insensitive keyword match, priority-ordered, live listings only; prepended to browse results
active_sponsors(tier, category_id) — running sponsors filtered by tier/category
expire_promoted_keywords() — removes expired rows; nightly timer

`services/reports.py` (Phase 6)

create_report(listing, reporter, reason, note) — writes Report, auto-flags listing when distinct reporter count ≥ flag_threshold setting (default 5)
ReportError — raised for duplicate reports or self-reports

`services/moderation.py` (Phase 6)

flag_queue(page, per_page) — listings sorted by flag_count × recency
approve(listing, actor) — clears flags, sets active, logs to audit_log
hide(listing, actor) — sets flagged status, logs
remove(listing, actor) — sets removed status, logs

`services/audit.py` (Phase 6)

log_action(actor, action, target_type, target_id, meta) — appends AuditLog row

`services/settings.py` (Phase 6)

get_setting(key, default) — reads from settings table, cached in app context
set_setting(key, value) — upserts, invalidates cache
Keys: registration_open, ads_enabled, maintenance_mode, flag_threshold, keyword_blocklist (list), new_user_trust_gate_days, contact_density_threshold

`services/admin_dashboard.py` (Phase 6)

kpis() → dict with active_listings, new_users_7d, new_users_30d, mrr_cents, revenue_30d_cents, flag_queue_depth
signups_per_day(days), listings_per_day(days), revenue_per_day(days) → lists
top_categories(n) — by active listing count

`services/admin_users.py` (Phase 6)

search_query(q, role, status, tier) — filtered User query
set_status(user, status, actor) — ban/suspend/activate + audit log
set_tier(user, plan, actor) — tier override + audit log
adjust_trust(user, delta, actor) — adds trust event, recomputes tier + audit log

`services/expiry_notifications.py` (Phase 7)

warn_expiring(days=3) — emails listing owners whose listings expire within N days; marks _warn_sent in attributes JSON to prevent duplicates
notify_expired() — emails owners of newly-expired listings; marks _expired_sent in attributes JSON; both are CLI commands via __init__.py

`services/reviews.py` (Phase 7)

create_review(listing, author, rating, body) — validates sold status + not-self; catches IntegrityError for duplicate review
seller_rating(user_id) → {"avg": float|None, "count": int}

11. i18n (Trilingual: EN / VI / ES)

A. UI chrome — Flask-Babel. All strings in _() / lazy_gettext(). Locale order: session['lang'] → user.locale → Accept-Language → en.

pybabel extract -F babel.cfg -o messages.pot .
pybabel init -i messages.pot -d app/translations -l vi
pybabel init -i messages.pot -d app/translations -l es
pybabel compile -d app/translations

B. User content — stored as-is, listings.lang declared. Phase 7: auto-translate button → listing_translations cache.

Accent-insensitive search (utils/text.py):

normalize("Phở Bò Đặc Biệt") == "pho bo dac biet"
normalize("Ñandú Jalapeño")   == "nandu jalapeno"

đ/Đ handled explicitly. Shadow column title_norm + promoted keyword matching both use this normalizer.

12. Geography & Location

zip_geo table: offline US ZIP → lat/lng/city/state/metro.
Seed sample: 10 VN/Hispanic hub ZIPs. Replace with full SimpleMaps/Census ZCTA (~42k rows) before production.
Radius search: bounding-box SQL prefilter → haversine exact refine.
metros table wired; SEO landing pages built in Phase 7.

MySQL spatial upgrade (optional):

ALTER TABLE listings ADD COLUMN geo POINT
  GENERATED ALWAYS AS (ST_SRID(POINT(lng, lat), 4326)) STORED,
  ADD SPATIAL INDEX spx_listings_geo (geo);

13. Anti-Abuse

Listings go active immediately (post-and-flag model).

Status lifecycle: active → flagged / sold / expired / removed

Active controls (Phase 1–5):

Trust tier gates contact-info reveal (email_verified AND tier ≥ trusted)
Rate limits: register 10/hr, login 20/hr, post 30/hr, message 20/hr
Contact density heuristic: ≥3 signals in message body → warning log
Turnstile CAPTCHA on register + post (dev bypass when keys blank)
Duplicate email blocked at register

Phase 6: flag threshold → auto-flip, keyword blocklist, reports queue, audit_log.

14. Security

Argon2 password hashing
CSRF on all forms (Flask-WTF); @csrf.exempt only on /billing/webhook
RBAC decorators on all protected routes
Rate limiting (Flask-Limiter + Redis)
Image upload: MIME sniff, Pillow re-encode, 8MB cap, 1600px max, random filenames
Cloudflare Turnstile CAPTCHA
Contact masking for low-trust users
Signed time-limited tokens for email verify + password reset
ProxyFix for correct is_secure + client-IP behind Nginx
Mid-session ban enforcement: enforce_active_account before_request hook logs out any authenticated user whose status != active on their next request (Flask-Login only checks is_active at login time)
Stripe webhook signature verification on every event
Secrets via .env only — never committed
HTTPS only in prod; SESSION_COOKIE_SECURE=True in ProdConfig

15. Environment Variables (`.env`)

CRITICAL: No inline # comments after values — python-dotenv does NOT strip them, causing int() parse errors. Comments must be on their own lines.

FLASK_CONFIG=prod
SECRET_KEY=<long random string>
SERVER_NAME=classifieds.ngodanguyen.tech

# Database
DB_USER=classifieds
DB_PASSWORD=<password>
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=classifieds
DATABASE_URL=

# Redis
REDIS_URL=redis://127.0.0.1:6379/0

# i18n
DEFAULT_LOCALE=en
SUPPORTED_LOCALES=en,vi,es

# Email (Brevo)
MAIL_SERVER=smtp-relay.brevo.com
MAIL_PORT=587
MAIL_USE_TLS=true
MAIL_USERNAME=<brevo login>
MAIL_PASSWORD=<brevo smtp key>
MAIL_FROM=no-reply@classifieds.ngodanguyen.tech
MAIL_FROM_NAME=Classifieds

# Media
MEDIA_ROOT=

# Turnstile
TURNSTILE_SITE_KEY=
TURNSTILE_SECRET_KEY=

# Token lifetimes (seconds — no inline comments!)
TOKEN_VERIFY_MAX_AGE=86400
TOKEN_RESET_MAX_AGE=3600

# Stripe
STRIPE_SECRET_KEY=sk_live_...
STRIPE_PUBLISHABLE_KEY=pk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...

Getting Stripe keys

Secret + Publishable keys: https://dashboard.stripe.com/apikeys
Webhook secret: https://dashboard.stripe.com/webhooks → Add endpoint → URL: https://classifieds.ngodanguyen.tech/billing/webhook → Events: checkout.session.completed, customer.subscription.created, customer.subscription.updated, customer.subscription.deleted, invoice.payment_failed → Reveal signing secret → copy whsec_...
Test mode: use stripe listen --forward-to .../billing/webhook (Stripe CLI) for local dev; prints a temporary whsec_... to stdout

Activating Stripe plans

After creating Products + Prices in the Stripe dashboard, map stripe_price_id:

flask shell
>>> from app.models.plan import Plan; from app.extensions import db
>>> Plan.query.filter_by(slug='basic').first().stripe_price_id = 'price_xxx'
>>> Plan.query.filter_by(slug='pro').first().stripe_price_id = 'price_yyy'
>>> Plan.query.filter_by(slug='business').first().stripe_price_id = 'price_zzz'
>>> db.session.commit()

16. Setup & Run

Smoke test (no MySQL/Redis needed)

python3 -m venv venv && source venv/bin/activate
pip install -r requirements.txt
python -m tests.test_smoke      # all checks green (Phase 1–6 complete)

Dev server

Use absolute SQLite path — Flask-SQLAlchemy resolves relative paths against instance/, not CWD. Stale instance/dev.db causes ghost-schema issues.

DATABASE_URL=sqlite:////tmp/classifieds_dev.db

export FLASK_APP=wsgi:app
flask db init && flask db migrate -m "initial" && flask db upgrade
python seed.py --admin admin@example.com 'StrongPass123'
flask run

MySQL 8.0 — both host variants required

CREATE DATABASE classifieds CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'classifieds'@'localhost'  IDENTIFIED BY 'password';
CREATE USER 'classifieds'@'127.0.0.1'  IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON classifieds.* TO 'classifieds'@'localhost';
GRANT ALL PRIVILEGES ON classifieds.* TO 'classifieds'@'127.0.0.1';
FLUSH PRIVILEGES;

Both @'localhost' and @'127.0.0.1' required — MySQL treats them as different accounts. If caching_sha2_password errors: pip install cryptography.

Production deploy

# 1. Deploy code to server
# 2. python3 -m venv venv && pip install -r requirements.txt
# 3. cp .env.example .env && nano .env   (no inline comments!)
# 4. flask db upgrade && python seed.py
# 4b. Compile translations (committed .mo travel with the repo, but recompile
#     after any .po change):  pybabel compile -d app/translations
# 5. Copy systemd units:
cp deploy/classifieds.service /etc/systemd/system/
cp deploy/classifieds-expire.{service,timer} /etc/systemd/system/
cp deploy/classifieds-nightly.{service,timer} /etc/systemd/system/
systemctl enable --now classifieds classifieds-expire.timer classifieds-nightly.timer
# 6. Configure Nginx:
ln -s /etc/nginx/sites-available/classifieds /etc/nginx/sites-enabled/
nginx -t && systemctl reload nginx
# 7. TLS:
certbot --nginx -d classifieds.ngodanguyen.tech

Nginx troubleshooting

Config must be symlinked to sites-enabled/ (not just sites-available/)
curl -H "Host: classifieds.ngodanguyen.tech" http://127.0.0.1/ -I — tests Nginx config independently of DNS
DNS A record must point to server's public IP
curl -4 ifconfig.me vs dig +short classifieds.ngodanguyen.tech must match
CSS cache busting: bump the ?v=N query string on the style.css link in base.html (single style.css file; the old style.vN.css copy approach was dropped)

17. Coding Conventions

App factory pattern. No global app. Extensions in extensions.py.
Blueprints per domain. Routes thin; all logic in services/.
SQLAlchemy models, Alembic migrations for every schema change. Never db.create_all() in production.
All datetimes UTC in DB. Localize only at render.
Money: integer cents everywhere. Never float for currency.
db.session.get(Model, pk) not Model.query.get(pk) (SA 2.0 deprecated).
UTC now: use utcnow() from app/utils/time.py — never datetime.utcnow() (deprecated on Python 3.12+). The helper returns a naive UTC datetime to match the naive DateTime columns; do not mix in tz-aware datetimes.
Enums: DB ENUM columns + Python str, enum.Enum in models/enums.py.
Validate listings.attributes against field_schema server-side on every save.
i18n: all user-facing strings in _(). No hardcoded English in templates.
CSRF on every state-changing form; inline POST forms use {{ csrf_token() }}.
merge_query(**overrides) context helper for pagination links.
inject_ads() runs on every request via context processor; errors suppressed (ads must never break page renders).
Stripe webhook handler is CSRF-exempt; always verify signature before processing.

18. Build Roadmap & To-Do List

✅ Phase 1 — Foundation (Done)

App factory, Dev/Prod config, env-driven, ProxyFix
Extensions: SQLAlchemy, Migrate, LoginManager, CSRF, Babel, Limiter
users, plans, trust_events schema + Alembic migrations
Argon2 hashing, itsdangerous signed tokens
Auth: register, email verify (+5 trust), login, logout, password reset
Rate limiting on auth endpoints
RBAC decorators: role_required, admin_required, moderator_required
Trilingual i18n scaffold (EN/VI/ES), /lang/<code> locale switcher
Accent-insensitive normalizer (phở→pho, ñ→n)
Cloudflare Turnstile CAPTCHA hook
seed.py: 4 plans with JSON limit configs + --admin flag
systemd + Gunicorn + Nginx deploy files
Smoke test: 14 checks

✅ Phase 2 — Listings Core (Done)

categories, listings, listing_images, zip_geo, metros models
6 categories + subcategories seeded with field schemas
10 sample ZIP rows (Westminster, Houston, San Jose, Miami, Falls Church…)
Field schema validation (text/number/select/bool, required, coerce)
Hot-column denormalization (attr_condition, attr_job_type, attr_salary_*)
Listing CRUD: create, edit, delete, mark-sold, my-listings
Tier enforcement: active-listing cap, image cap, listing life from plan
Image pipeline: MIME check, Pillow re-encode JPEG, strip EXIF, thumbnail
Browse + filters: category, state, price, condition, keyword
Accent-insensitive keyword search via title_norm LIKE
Radius search: bounding-box SQL + haversine refine
flask expire-listings CLI + hourly systemd timer
Smoke test: +6 checks

✅ Phase 3 — Messaging + Favorites (Done)

conversations, messages, favorites models
Messaging: get-or-create, send, mark-read, inbox, total-unread
Self-message block; contact density heuristic
Contact masking (phone/email/URL hidden for low-trust users)
Trust-gated reveal (email_verified AND trust_tier ≥ trusted)
Notification email on every new message
Favorites: toggle, paginated list
Unread badge in nav via context processor
Contact seller + ♥ Save buttons on listing detail
/api/unread JSON endpoint
Smoke test: +15 checks

✅ Phase 4 — Monetization / Stripe (Done)

subscriptions, transactions, boosts models
stripe==10.12.0 added to requirements
Stripe config: STRIPE_SECRET_KEY, STRIPE_PUBLISHABLE_KEY, STRIPE_WEBHOOK_SECRET
billing.py service: customer, checkout, portal, boost, sync, downgrade, webhooks
Subscription checkout → Stripe Billing (Checkout Session + Tax)
Customer Portal (self-serve upgrade/cancel/update card)
À la carte boost checkout (Payment Intents, idempotent on payment_intent_id)
Webhook handler: 4 event types, signature-verified, CSRF-exempt
activate_boost() — writes Transaction + Boost, applies listing effects
sync_subscription() — upserts local DB, upgrades user role/tier
downgrade_to_free() — reverts role + tier on cancellation
expire_boosts() + reconcile_subscriptions() — nightly systemd timer
Pricing page, billing dashboard, boost selector UI
Boost + Upgrade buttons on listing detail + my-listings
Smoke test: +12 checks

✅ Phase 5 — Ads & Sponsors (Done)

ads, sponsors, promoted_keywords models
ads.py service: get_ad (targeted→untargeted fallback), impression/click tracking
promoted_listings(keyword) — accent-insensitive, live only, priority-ordered
active_sponsors(tier, category_id) — running sponsors
expire_promoted_keywords() — nightly timer
Ad slots in templates: header, sidebar (browse), inline (every 6th card), footer
Ads suppressed for subscribers (plan.limit('ad_free'))
Promoted listings prepended to browse results with "Promoted" badge
Click tracking redirect /ads/<id>/click
Sponsor directory /sponsors
inject_ads() context processor on every request
ads/_slot.html reusable partial
Sponsors link in footer nav
flask expire-promoted-keywords CLI added to nightly systemd unit
Smoke test: +14 checks (58 total)

✅ Phase 6 — Admin Backend (Done)

Dashboard

/admin dashboard: KPI cards (active listings, new users 7d/30d, MRR, ad revenue, flag-queue depth)
Data tables: signups/day, listings/day, revenue trend (last 30d)

User management

/admin/users — searchable/filterable table (role, status, tier, trust)
User detail: profile, listing history, subscription, trust events
Actions: ban/suspend/activate, tier override, trust adjust, impersonate (→ audit_log)

Listing moderation

/admin/listings — flag queue sorted by flag_count × recency
Quick actions: approve (clear flags), hide (flagged), remove, view
Auto-flag threshold: N distinct-user flags → auto-flip to flagged (threshold stored in settings table, default 5)
Keyword blocklist editor (stored in settings, checked on listing submit)

Reports queue

/admin/reports — flagged content with reporter reasons
Mark resolved action
reports + audit_log + settings table models + routes

Category management

/admin/categories — CRUD, reorder (sort_order field)
Field schema editor per category (add/remove fields, type/required/options)

Plan / pricing management

/admin/plans — edit config JSON limits, name, price, Stripe price ID
No-redeploy: limits read at runtime from DB

Ads & sponsors management

/admin/ads — CRUD, slot/targeting/schedule, impressions/clicks/CTR stats
/admin/sponsors — CRUD sponsor entries, assign category
/admin/promoted-keywords — assign keyword → listing, set priority + expiry

Transactions & billing

/admin/transactions — full log, filter by type/status/date
Refund action (Stripe Refund API + local refund transaction)

Settings

/admin/settings — toggle UI for settings table: registration_open, ads_enabled, maintenance_mode, flag_threshold, keyword_blocklist, new_user_trust_gate_days, contact_density_threshold

Audit log

All admin write actions → audit_log
/admin/audit — searchable trail

Analytics

Conversions: registrations/day, listings/day, revenue/day
Top categories by listing count; ad CTR summary

Tests

Smoke: admin 200, non-admin 403 for all admin routes
Smoke: flag threshold auto-flips listing status
Smoke: impersonate logs to audit_log
Smoke: settings (registration_open, ads_enabled, maintenance_mode, contact_density_threshold, new_user_trust_gate_days) all verified

🔄 Phase 7 — Polish (In Progress)

SEO & discoverability

State landing pages /classifieds/state/<state>
Category landing pages /classifieds/category/<slug>
Dynamic <title> + <meta description> blocks in base.html (all pages)
JSON-LD structured data on listing detail (Product schema)
XML sitemap /sitemap.xml (static pages + active categories + 500 listings)
robots.txt (disallows admin/auth/my/messages/billing, includes Sitemap URL)
Open Graph tags (og:title, og:description, og:type, og:image, og:url + canonical)
Metro landing pages /classifieds/<metro-slug>

Email notifications

Listing expiry warning — warn_expiring(days=3) + flask warn-expiring-listings CLI
Listing expired notification — notify_expired() + flask notify-expired-listings CLI
Move message notifications to RQ worker (async)
Weekly digest email (new listings in saved categories) — opt-in

Reviews

reviews table model (listing_id, author_id, seller_id, rating 1–5, body)
POST /listings/<id>/review — leave review after mark-sold (buyer only, once)
services/reviews.py — create_review, seller_rating
Seller aggregate rating shown on listing detail sidebar
Full review list displayed below listing

Translation (deferred from Phase 2)

Per-listing "Translate" button → DeepL/Google API
Cache in listing_translations
Language filter on browse

Performance

Redis sessions (SESSION_TYPE=redis)
Query caching for hot browse (Redis, 60s TTL)
Lazy-load images (loading="lazy", eager for first image)
WebP thumbnails
MySQL FULLTEXT(title, body) + MATCH ... AGAINST
Keyset pagination for large datasets

UX & mobile

Responsive nav (hamburger on mobile, CSS + JS toggle)
Listing image lightbox
"Load more" / infinite scroll on browse
Toast notifications (auto-dismiss after 5s with JS)
"Back to results" preserving filter state
Listing preview before publish

i18n completion

Translate VI + ES .po files (75-string Phase 1–2 catalog: auth + listings chrome)
Compile .mo for vi + es; verified loadable via gettext (placeholders + plural-forms intact)
.mo files un-gitignored so they deploy (see Known Issues)
Re-extract .pot to capture Phase 3–7 strings (messaging/payments/admin) — needs pybabel extract (Babel not currently installed); then translate + recompile
Test all three locales end-to-end in the running app

🔲 Phase 8 — JSON API (Optional, for iOS app)

JWT auth (flask-jwt-extended), /api/v1/auth/login + /register
GET/POST /api/v1/listings (browse + create)
GET/PUT/DELETE /api/v1/listings/<id>
GET/POST /api/v1/messages/<conv_id>
POST /api/v1/listings/<id>/favorite
GET /api/v1/categories, /api/v1/zip/<zip>, /api/v1/me
Consistent {"error":"...", "code":"..."} JSON error shape
CORS headers, stricter rate limits
API smoke tests (separate suite)

🔲 Deferred Items

Full ZIP dataset — replace 10-row sample with SimpleMaps/Census ZCTA (~42k rows)
MySQL spatial — POINT column + SPATIAL INDEX + ST_Distance_Sphere
MySQL FULLTEXT — FULLTEXT(title,body) + MATCH ... AGAINST (Phase 7)
MySQL generated columns — GENERATED ALWAYS AS (JSON_EXTRACT(...)) for attr_*
RQ worker — replace inline email + APScheduler with proper RQ queue
Redis sessions — SESSION_TYPE=redis (Phase 7)
Bulk CSV upload — Business tier: parse, validate, batch-create listings
Scheduled posting — Pro/Business: publish_at datetime, worker flips to active
Auto-bump/renew — Pro (weekly) / Business (daily): worker extends bump_at/expires_at
Storefront page — Pro/Business: /seller/<username> with all active listings
Custom URL — Business: vanity slug (e.g. /shop/alices-cleaning)
Job-seeker posts — reverse listings under Jobs category

19. Known Issues / Decisions Locked

Item	Decision
DB portability	SQLite for tests (`BigInteger().with_variant(Integer,"sqlite")` + `autoincrement=True`), MySQL 8.0 for prod. MariaDB NOT supported.
Spatial search	lat/lng + bounding box + haversine. MySQL `POINT` + `SPATIAL INDEX` upgrade documented above.
FULLTEXT search	`title_norm LIKE` for now. MySQL `FULLTEXT` upgrade in Phase 7.
Hot filter columns	App-maintained `attr_*` indexed columns. MySQL `GENERATED ALWAYS` upgrade optional.
ZIP dataset	10-row sample seeded. Replace with full dataset (~42k rows) before prod.
Workers	No RQ yet. Expiry + boost + keyword sweeps via systemd timers. Message notifications inline. RQ in Phase 7.
Session storage	Flask default. Switch to Redis sessions in Phase 7.
Ad impression tracking	Direct DB increment per request. Phase 7: batch to Redis, flush periodically.
`.env` inline comments	MUST NOT use `# comments` after values — python-dotenv does not strip them → `int()` ValueError on startup.
Stripe webhook	`/billing/webhook` is `@csrf.exempt`. Always verify `Stripe-Signature` header first.
MySQL user grants	Must create BOTH `@'localhost'` AND `@'127.0.0.1'` — MySQL treats them as different accounts.
CSS cache busting	Nginx serves static with 30d expires. To bust: bump the `?v=N` query string on the `style.css` link in `base.html`.
Translations (.mo)	`.mo` is gitignored except* `app/translations/**` (negated) so compiled catalogs deploy with the code — the no-build-step deploy has no compile phase. Recompile with `pybabel compile -d app/translations` after editing any `.po`.
Python / datetime	Target 3.11–3.12 for prod parity. `datetime.utcnow()` is deprecated on 3.12+ — use `utcnow()` from `app/utils/time.py`.

End of spec. Phase 1–6 complete. Phase 7 (Polish) in progress — SEO, UX, email notifications, reviews, and VI/ES translation compilation done (existing catalog). Remaining: metro landing pages, i18n re-extraction for Phase 3–7 strings, Redis sessions, WebP thumbnails, listing lightbox, load-more, "back to results", translation cache, weekly digest. Note: run the smoke suite in a venv matching requirements.txt (Flask 3.0.3 / Python 3.11–3.12); the ambient Python 3.14 env lacks flask_login and drifts from the pins.

51 KiB Raw Permalink Blame History Unescape Escape