Architecture named GPS Dashboard's direct, client-side Supabase access as the single biggest platform risk, and set out four vision items: each product owns its data, one identity layer, a narrow API between products, and a batch-fed reporting layer. This page is that vision turned into an actual engineering plan for GPS specifically — the first product to get it — verified against the real repositories, not proposed in the abstract. Full detail on every decision lives in the source plan this page summarizes.
Reading trackezz_etl/supabase/schema.sql directly (not just GPS Dashboard's code)
confirms it's worse than "a public key plus RLS": every RLS policy on every GPS table is
to authenticated using (true) — any signed-in dashboard login can read the
entire fleet's full position history, every driver mapping, every ride/revenue number, with zero
per-user or per-row scoping. Upahar's own repo independently reached the same conclusion first: a
migration already revoked anon/authenticated grants on its own tables, and
its docstring names gps_dashboard by name as the sibling app still exposed.
trackezz_etl carries
substantial business logic as Postgres functions (day/night metrics, GPS anomaly detection,
ride-vs-GPS matching, revenue rollups, plate reconciliation) with real production-incident history
— a naive DISTINCT ON view that silently became a 46-second scan at production
scale, a manual SQL deploy that silently didn't land, DROP FUNCTION-before-CREATE
footguns. None of it is unit-testable outside Postgres. This is the "bad ETL design" half of the
problem Architecture's debt inventory didn't fully capture.
Architecture's vision said "not one shared database." This plan collapses Supabase-hardening and the eventual RDS migration into one move, and answers why below.
Gray: new work this plan builds. Every product owns its own database and is the only thing that talks to it directly — the "shared" part is infrastructure (one instance, one bill, one thing to patch and back up), never data.
drivra_engine
(like Upahar already) talks to Postgres directly via SQLAlchemy/asyncpg, never through Supabase's
PostgREST layer — the application code is identical regardless of which of the two hosts
Postgres. Hardening Supabase in place would be real security work thrown away days later when
Express's already-urgent RDS migration (no managed backups today) happens anyway. RDS is also
strictly better: a private-VPC instance has no public REST surface at all, versus Supabase's
PostgREST endpoint still existing publicly, just de-authorized.
drivra_engine never touches Supabase at all — this is the plan's main buildMade simpler specifically because every product now lives on one RDS instance:
postgres_fdw foreign tables in a new reporting_db point directly at
upahar_db/express_db/gps_db — no bespoke extraction
Lambda needed, since it's all one instance. dbt owns the transform step (its core
use case: testable, documented, lineage-tracked SQL), triggered by one nightly scheduled job. No
Step Functions, no message bus — this is exactly the "right-sized, not enterprise-sized"
principle Architecture already committed to. This is the piece that unblocks rent-to-own's credit
scoring (below).
drivra_engine: same proven stack, own repo, own dataMirrors Upahar's architecture directly — not extended into Upahar's own repo (product
autonomy), a fresh service copying what's already proven: FastAPI + SQLAlchemy 2.0 (async) +
asyncpg + Alembic, routers → services → ORM, JWT auth, Docker + Caddy + GitHub Actions
→ ECR → SSM deploy, t4g.small EC2 (one size up from Upahar's
t4g.micro, given more concurrent dashboard sessions and larger JSON payloads).
| API group | Replaces | Notes |
|---|---|---|
positions/ | fetchLatest, history queries | Latest-position endpoint served from a cache table refreshed every 30–60s — faster than today's on-demand recursive-CTE view, not slower |
driver-mapping/ | set_vehicle_driver, lookup, suggestions, reconciliation | 409 with a structured error code replaces raw Postgres exception text on conflict |
attributes/ | vehicle type/fuel CRUD | Unchanged shape |
analytics/ | 6 heavy SQL functions | Geofence coordinates move server-side (single source of truth, no more client-supplied config) |
auth/ | Supabase Auth | New ops_users table, JWT, mirrors Upahar's admin-auth pattern exactly — own audience, own secret |
Table ownership going forward: vehicle_positions
stays Lambda-written (unchanged ETL), drivra_engine reads only. vehicle_driver_mapping
and vehicle_attributes get adopted — drivra_engine becomes the sole
writer via idempotent migrations that no-op against the already-migrated data. The existing
fleet.*_cache tables and pg_cron jobs are superseded, not reused (a new Python-refreshed
writer against the same table would race the old pg_cron one during the build) — they go dead
at cutover and can be dropped at leisure.
The ask is a new service, not a new algorithm. Recommendation: keep row-level computation as SQL (near-verbatim copies of the existing, already-tuned function bodies) called from Python service functions — the new Python work is the orchestration (params, caching, auth, error shaping), not re-deriving window functions and gaps-and-islands logic that's already been tuned through real incidents once.
| Risk | Function | Why |
|---|---|---|
| Highest | _vehicle_ride_segments | Two documented real mis-plan incidents already; a 5-way classification precedence resolved against a slowly-changing-dimension range-join |
| Highest | _vehicle_day_metrics_live | Three-stage documented tuning saga (97s→44s→20s→2-3s); easy-to-invert gap-exclusion and overnight-boundary rules |
| Medium | Maintenance visits, GPS anomalies | Same gaps-and-islands technique, subtler "is this still ongoing" joins |
| Low | Revenue, reconciliation, matching, lookups | Straightforward joins/branching — set_vehicle_driver (lock row, branch, write) is the one genuinely well-suited to a real Python port |
Mitigation regardless: before cutting any single function over, a shadow-diff script calls both the still-live SQL and the new endpoint for the same real vehicles/date ranges and diffs row-for-row across 30–60 days × every vehicle — a concrete gate, not a suggestion. Testing also deviates from Upahar's own convention on purpose: Upahar accepts SQLite-only tests because none of its logic is Postgres-specific; that's not true here, so a small Postgres-backed integration suite runs on every PR.
RDS storage encryption at instance creation (can't be retrofitted without a
snapshot-restore). TLS everywhere — sslmode=require on every connection,
Caddy termination on every API. KYC documents (future): S3 with SSE-KMS, never a public bucket
URL.
Audit logging as a first-class table from day one. Least-privilege DB roles per product. No PII in logs, ever — IDs only. KYC documents (future): 5-year retention floor per Nepal's Asset Laundering Prevention Act, access restricted to a KYC-reviewer role specifically.
Three tiers, applied uniformly — read, write,
admin — not a per-module permission matrix. Same shape as Upahar's existing
owner/staff dependency pattern, extended rather than reinvented.
WAF on every public API (rate-limited specifically on login), GuardDuty for
account-level anomaly detection, in-app rate limiting from day one (a gap Upahar's own research
already flagged), MFA for admin-role accounts, dependency scanning in CI.
Every phase is additive and reversible until explicitly noted. The dashboard and ETL Lambda keep working, completely unchanged, through Phase 4.
| Phase | What happens | Live system |
|---|---|---|
| 0 | Provision drivra_engine's own scoped Postgres role, RLS policies | Untouched |
| 1 | Skeleton, read-only positions, auth — deployed to staging only | Untouched |
| 2 | Driver-mapping + attributes CRUD parity, concurrency-tested | Untouched |
| 3 | Six analytics functions ported, shadow-verified against real data | Untouched |
| 4 | Dashboard rewritten, deployed to staging only, QA'd page by page against production | Untouched — last fully-reversible checkpoint |
| 5 | Cutover: production dashboard points at drivra_engine | Live on new backend; same-day rollback available for 1–2 week soak |
| 6 | Revoke anon/authenticated PostgREST grants — mirrors Upahar's own precedent exactly | The concrete fix for the flagged critical vulnerability |
| 7 | Cleanup: old SQL functions/cache tables removed at leisure | Optional |
On-demand ap-south-1 rates, not a
quote — confirm via the AWS Pricing Calculator before treating this as a committed budget.
db.t4g.medium) + drivra_engine EC2 + Fargate reporting job + storageAgainst this: Supabase's usage-based growth avoided, Express's currently uninsured no-backup risk closed, and one migration instead of two (the "harden now, RDS later" alternative pays for this work twice).
| Category | Services |
|---|---|
| Already in use, unchanged | Lambda, EC2, ECR, SSM, IAM/OIDC, EventBridge Scheduler, CloudWatch |
| New, needed | RDS, S3, ECS Fargate (nightly dbt), KMS |
| Needed later, not now | Athena — only once a cold-storage tier actually exists |
| Recommended against | Glue (no messy multi-source schema-discovery problem exists here), Redshift/a message bus (explicitly what Architecture's vision says to avoid), API Gateway (Caddy already fills this role), Cognito (would replace already-proven JWT auth for no benefit) |
app_rider/) was inspected directly, not assumed: no
Supabase dependency anywhere in its source or its package manifest. It talks exclusively to
Upahar's own API over a plain HTTP client. Since the database migration is entirely internal to
the backend, every installed app is unaffected, with no new release required —
the concrete payoff of Upahar already having a real backend in front of its data, which is exactly
what this plan gives GPS Dashboard for the first time.
This plan's "product autonomy, proven stack, separate database" pattern is exactly what financing.html already independently specifies for the rent-to-own service: its own repo, own database (a database on the shared RDS instance under this model, rather than a fully separate Supabase project), same FastAPI + SQLAlchemy + Alembic stack. Its credit scoring depends on Upahar's tenure/earnings/streak data — exactly the cross-product signal the new reporting database (above) exists to serve, and explicitly does not depend on GPS telematics data in v1.
drivers/orders read boundary: table-level grant with the ORM
limiting columns (simplest, zero Upahar changes) vs. Upahar adding a narrow view (preserves
column-level least privilege exactly, matches Upahar's own stated forward policy, but touches its repo).t4g.small vs. t4g.micro for the new instance — a reasoned
recommendation, cheap to revisit with real post-launch metrics.husig.ai/drivra_tech and the wider infra-as-code source of truth should live
long-term, given the existing two-GitHub-org split — not resolved by this plan, worth a
deliberate decision before a fifth product adds to it.