# PRD — flutterflow_sync

Evidence date: 2026-10-15.

Source: `git@bitbucket.org:overdrive/flutterflow_sync.git`, `main` at `8525b9f` (2025-09-04, Jaco Brits, "first commit"). Full hash `8525b9f526eb25cd9b2452a10ec88a9712e90180`. `origin/HEAD` points at that commit. The only branch is `main`.

There is no prior PRD in Silicon Ark. The source repo's product description is `README.md`. `CHANGELOG.md` and `NewKnowledgeBase.md` add claims. `docs/` tells a FlutterFlow author how to paste the client into an app. Provenance is labelled. The server was not started for this investigation.

## Product purpose

**Documented.** README: a middleware server for FlutterFlow–Supabase synchronization, with a dashboard for monitoring and management, and a client library for FlutterFlow apps.

**Implemented.** Three programs in one repository:

1. An Express process that copies Supabase query results into MySQL as JSON documents, accepts writes into a `pending_changes` queue, and uploads files to local disk.
2. A React dashboard that reads operator JSON endpoints.
3. A Dart library that calls the Express API with the Supabase session access token and listens on Socket.IO.

**Discrepancy.** README and the changelog call the design offline-first, with local storage on the client. The Dart client has no local database. Reads and writes fail if the middleware is unreachable (`FlutterFlowSyncClient` throws when HTTP status is not 200). The queue lives in server MySQL. A phone that cannot reach the server cannot read or write.

**Unknown / Requires Confirmation:** the FlutterFlow app and the business this store/survey/promo/task schema serves.

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| FlutterFlow app user | Supabase access token on `/api/*` and on the socket handshake. `auth.js` sets `req.user.userId` from `supabase.auth.getUser` | **Implemented** as the API caller. The app UI is not in this repo |
| Dashboard operator | One shared username and password from env (`DASHBOARD_USERNAME`, `DASHBOARD_PASSWORD`). Login form in `dashboard/src/components/Login.tsx` | **Documented** and **Implemented** as a single shared login. Not a user directory |
| Deploy operator | `docker-compose.yml`, Dockerfiles, README PM2 snippet | **Documented**. No pipeline file |

No second dashboard role exists. The Users screen lists Supabase user ids found in `sync_metadata`. It does not create, disable, or edit those users.

## Systems the product talks to

| System | Direction | What is exchanged | Auth | Failure behaviour in code |
|---|---|---|---|---|
| Supabase Auth | Server and client → Supabase | Access token checked with `auth.getUser`. Sync uses `auth.admin.getUserById` | Anon key for `getUser`. Service role key for the sync client | API returns 401. Sync context falls back to `{ user_id, user_email: null, store_codes: [] }` |
| Supabase Postgres | Server → Supabase | `rpc('execute_sync_query', { query })` for pulls. `.from(table).insert/update/delete` for queued writes. Health reads `_health_check` | Service role key on the sync client | Pull throws, metadata row set to `error`, socket event `sync_error`. Pending write marks the row `failed` |
| MySQL 8 | Server ↔ MySQL | JSON documents, metadata, pending changes, conflicts table, logs, `file_uploads` | `DB_*` env | `initialize()` logs and rethrows on ping failure. Later SQL errors become HTTP 500 |
| Redis | Server → Redis | Session store (intended) and key `sync_stats` | `REDIS_URL` | `RedisService.initialize()` throws if connect fails, which fails sync init |
| Operator browser | Browser ↔ server or nginx | Dashboard HTML and `/dashboard/api/*` JSON | Cookie session, when the session store works | Login JSON `{ success: false }` or HTTP 500 |
| FlutterFlow app | App → server | REST and Socket.IO as wrapped by `client/lib` | `Authorization: Bearer` Supabase access token | Client throws `Exception` with the response body |
| Local disk | Server writes `server/uploads/` and `server/logs/` | Uploaded files, Winston logs | None on `GET /uploads` | Upload route deletes the temp file if the handler throws |

README also tells an operator to create Supabase function `execute_sync_query(text)` (dynamic `EXECUTE` of the query text) and to enable RLS on `store_user_link`. Those statements are setup instructions, not code in this repo. The function is `SECURITY DEFINER`. Combined with the service-role client, RLS on the pull path is **Unknown** — the service role typically bypasses RLS. **Requires confirmation** against the real Supabase project.

## Major workflows

### WF-1 — Configure and start the middleware

**Documented** in README: copy `env.example` to `.env`, create database `flutterflow_sync`, `npm start` in `server/`, build the dashboard, open `http://localhost:3000/dashboard` as `admin` / `admin123`.

**Implemented** entrypoint: `node src/server.js` (`npm start`). On listen it calls `syncManager.initialize(io)` and registers three cron jobs.

**Discrepancies (static reading, not executed):**

- `app.use('/api/sync', authMiddleware, …)` (and the same for `/api/data` and `/api/upload`) passes the module export `{ authMiddleware, socketAuth }`. The function that checks the bearer token is `authMiddleware.authMiddleware`. Express requires `app.use` handlers to be functions.
- The session store is constructed with `require('./services/redis').client` while that client is still `null`. `RedisService.initialize()` runs later, inside `syncManager.initialize`, after `listen`.
- Dashboard login calls `bcrypt.compare` on `DASHBOARD_PASSWORD`. `env.example`, the config default, and the Compose fallback are the plaintext `admin123`. `DASHBOARD_PASSWORD_HASH` is the only hash input the code checks, and nothing in the repo sets it.
- Express static files and the SPA fallback point at `server/dashboard/dist` (`path.join(__dirname, '../dashboard/dist')` from `server/src`). The React app lives in the sibling `dashboard/` directory. The server Docker build context is `./server` only.

Provenance of the happy path: **Documented**. Provenance of the mismatches: **Implemented** call shapes.

### WF-2 — Dashboard sign-in and navigation

`Login.tsx` posts `{ username, password }` through Axios. Axios `baseURL` is `/api`, and the path is `/dashboard/api/login`, so the browser requests `/api/dashboard/api/login`. The server route is `POST /dashboard/api/login`.

On success the server sets `req.session.dashboardAuthenticated`. `GET /dashboard/api/auth/check` returns `{ authenticated, user, loginTime }`. The React tree then shows Overview, Sync Status, Logs, Users, and Health.

`BrowserRouter` has no `basename`. Vite `base` is not set. Hosting the build under `/dashboard` does not match routes `/login` and `/`.

The nginx container proxies `/api/` to `sync_server:3000` and does not proxy `/dashboard/api`.

Provenance: **Implemented**. A completed login in any of the three host modes (Express static, Vite, nginx) was not observed. The path and basename mismatches are visible in source.

Operator screens poll Overview and Health every 30 seconds (`setInterval`). Logs load when filters change. Nothing in `dashboard/src` opens a socket. README "real-time log updates" and changelog "Real-time Updates via WebSocket" for the dashboard are **Documented** only.

### WF-3 — App user pulls remote data

1. App calls `FlutterFlowSyncActions.initializeSync` with server URL, Supabase URL, and anon key. The client initialises Supabase and connects Socket.IO with the current access token.
2. App calls `performSync`, which `POST`s `/api/sync/trigger` with the bearer token.
3. `SyncManager.handleClientSyncRequest` walks `syncConfig.buckets` in order: `global`, `user_data`, `user_stores`, `user_store_data`.
4. For each query it substitutes `{{user_id}}` or `{{store_code}}` from `getUserContext`, then `supabase.rpc('execute_sync_query', { query })`.
5. Each result row is stored in a local MySQL table of the same name: columns `id`, `user_id`, `data` (JSON of the whole remote row), timestamps, `sync_status`.
6. The server emits `sync_started` then `sync_completed` to room `user_<id>`.
7. It then pushes that user's `pending_changes` rows to Supabase.

`getUserContext` sets `user_id` and `store_codes` (an array). Query placeholders are `{{store_code}}` (singular). That key is never set, so the code replaces it with `NULL`. Store-scoped tables therefore sync with `store_code = NULL` unless a later change adds the field.

Bucket `parameters` SQL (`request.user_id()`, and the store-code select) is stored on the config object and never executed. One `store_code` is not expanded into one pull per store.

There is no `updated_at` filter. Each pull is the full SQL text. `last_sync_timestamp` is written afterwards and not read back into the next query. Changelog "incremental sync" is **Documented** only.

Provenance of steps 1–7: **Implemented**. The NULL store substitution is **Inferred** from the context object keys versus the placeholders.

### WF-4 — App user writes a row

`POST /api/data/:tableName` with body `{ operation, data }`. `operation` must be `INSERT`, `UPDATE`, or `DELETE`.

The handler inserts `pending_changes` and updates the local JSON table immediately (`sync_status = 'pending'`). Response: `{ success: true, message: 'Data queued for sync' }`.

The push to Supabase happens on the next sync for that user (manual, socket `request_sync`, periodic, or dashboard trigger). `processPendingChange` calls `supabase.from(table).insert/update/delete` and does not check the `{ error }` field on the result.

On throw, the code sets `status = 'failed'` and increments `retry_count` in the same update. The selector only loads `status = 'pending'`. A failed row is not retried. There is no delay or exponential backoff. Changelog and `NewKnowledgeBase.md` say there is. That claim is **Documented**. The one-shot failure update is **Implemented**.

`readData` in `flutterflow_sync_actions.dart` passes `whereArgs` into `FlutterFlowSyncClient.readData`. The client method does not declare `whereArgs`. The README sample uses `where: 'user_id = ?'` and `whereArgs`. The server appends the `where` string as SQL text and does not bind `?` placeholders.

Provenance: **Implemented** signatures. The library was not analysed with the Dart analyzer in this investigation.

### WF-5 — File upload

Authenticated `POST /api/upload/single` (field `file`) or `/multiple` (field `files`, max 10). Allowed types: jpeg, png, gif, webp, pdf, plain text, `.doc`, `.docx`. Max size 10MB.

Images are resized with sharp to fit inside 1200×800, JPEG quality 85, plus a 200×200 cover thumbnail. Metadata is inserted into `file_uploads` and queued as a `pending_changes` insert to Supabase table `file_uploads`.

`FileUploadService.uploadToSupabase` returns a public URL string and does not upload bytes. Nothing in the routes calls it.

`generateFileUrl` returns `/uploads/<basename>`. Multer writes `uploads/images/` or `uploads/documents/`. The static handler serves the uploads root, not those subfolders.

`GET /api/upload/files` lists the caller's rows. `DELETE /api/upload/files/:fileId` unlinks the file, sets `deleted_at`, and queues a Supabase delete. `GET /uploads/:filename` and `GET /api/upload/serve/:filename` do not check the caller against `file_uploads`.

Provenance: **Implemented**.

### WF-6 — Periodic sync and cleanup

Cron `*/5 * * * *` calls `performPeriodicSync`. That loads distinct `user_id` from `sync_metadata` where `updated_at` is within 24 hours, then runs WF-3 for each. A user with no metadata row is never picked up by the timer. The first sync has to be a client or dashboard trigger.

`SYNC_INTERVAL` (default 300000 ms) is loaded in config and not read by the cron expression.

Cron `0 2 * * *` deletes:

- `pending_changes` older than 7 days
- `sync_metadata` older than 30 days
- `sync_conflicts` with `resolved_at` older than 7 days
- `sync_logs` older than 30 days

Cron `0 0 * * *` logs "Performing log rotation..." and does not rotate files. Winston itself caps each file at 10MB and 5 files. Config `LOG_MAX_SIZE` / `LOG_MAX_FILES` are not read by the logger.

Provenance: **Implemented**.

### WF-7 — Operator monitors and forces a sync

| Screen | Client call | Server route | Behaviour |
|---|---|---|---|
| Overview | `GET /dashboard/api/stats` every 30s | `dashboard.js` | In-memory counters plus SQL counts of active users, pending rows, recent errors. Bar chart of those counters. `Line` is imported and unused |
| Sync status | `GET /dashboard/api/sync-status` | same | Latest 1000 `sync_metadata` rows. Button posts trigger for one user |
| Logs | `GET /dashboard/api/logs` | `getSyncLogs` | Filters user, level, dates, limit. CSV export is client-side of the rows already loaded. `offset` is accepted by the route and not applied |
| Users | `GET /dashboard/api/users` | aggregate SQL | Per user: sync count, last sync, total records, average duration. Trigger button |
| Health | `GET /dashboard/api/health` every 30s | same | MySQL `SELECT 1`. Redis via `syncManager.redis.exists` — `SyncManager` has no `redis` property. Supabase `from('_health_check')` without reading the `error` field |

`POST /dashboard/api/trigger-sync/:userId` starts `handleClientSyncRequest` and responds immediately.

These `/dashboard/api` routes do not call `dashboardAuth`. The session check is only on `GET /dashboard/*` HTML. The JSON API is open to any caller who can reach the port.

`GET /dashboard/api/conflicts` and `GET /dashboard/api/pending-changes` exist. No screen calls them.

Provenance: **Implemented**.

## Functional requirements

### FR-001 — Middleware purpose

The system copies configured Supabase queries into MySQL and queues client writes for later push.

| | |
|---|---|
| Who | FlutterFlow app user, via the Dart client or any HTTP client with a Supabase access token |
| Trigger | `POST /api/sync/trigger`, socket event `request_sync`, dashboard trigger, or the 5-minute cron |
| Inputs | Bearer token; optional JSON body on the HTTP trigger (stored only in the start log) |
| Behaviour | Walk buckets, store JSON rows, push pending changes, emit socket events, write `sync_logs` and `sync_metadata` |
| Output | HTTP `{ success: true }` or socket `sync_completed` / `sync_error` |
| Validation | Token must pass `supabase.auth.getUser` once the auth function is actually mounted |
| Errors | Missing token or invalid token: 401 from the auth function. Sync failure: 500 with `error.message`, metadata status `error` |
| Dependencies | Supabase `execute_sync_query`, MySQL, the service-role key |

Implementation: **Implemented** in `syncManager.js` and `routes/sync.js`, behind the middleware export mismatch in WF-1.

Confidence: high for the source. Runtime not observed.

### FR-002 — Sync selection rules

Pulls use the SQL in `server/src/config/syncConfig.js`, not a live read of `sync_rules.yaml`.

Global tables (`survey`, `survey_multiple_choice`, `survey_questions`, `category_images`) are `SELECT *` with no predicate.

User tables (`customer_user`, `app_user`) filter `id = {{user_id}}`.

`store_user_link` selects an explicit column list where `user_id = {{user_id}}`.

Store-scoped tables filter `store_code = {{store_code}}`, with extra predicates recorded in `business-rules.md`.

Implementation: **Implemented** as those strings. Store-code substitution does not receive a value (WF-3).

### FR-003 — Local read

`GET /api/data/:tableName` returns rows for the token's user. JSON `data` is parsed and mixed with `_local_id`, `_sync_status`, `_created_at`, `_updated_at`.

Optional query params `where`, `orderBy` are concatenated into SQL. `limit` and `offset` are parsed as integers and concatenated.

Implementation: **Implemented**. This is not a parameterised filter.

### FR-004 — Queued write

`POST /api/data/:tableName` accepts only `INSERT`, `UPDATE`, `DELETE`, writes `pending_changes`, and updates the local table.

Implementation: **Implemented**. Retry and conflict resolution described in the changelog are not implemented (WF-4).

### FR-005 — File upload and image derivatives

See WF-5. Implementation: **Implemented** for local disk and metadata. Supabase Storage upload is a stub method that is not called.

### FR-006 — Dashboard authentication

One configured username. Password check is `bcrypt.compare` against `DASHBOARD_PASSWORD_HASH` or, if unset, `DASHBOARD_PASSWORD`.

Session cookie: `httpOnly`, `secure` when `NODE_ENV === 'production'`, max age 24 hours. Store is Redis when the client exists.

Implementation: **Implemented** with the plaintext-vs-bcrypt mismatch in WF-1. README's default `admin` / `admin123` is **Documented** and is the config fallback.

### FR-007 — Dashboard monitoring

Screens and routes in WF-7. Implementation: **Implemented** as those pages. Realtime sockets, a conflict UI, and CSV generation on the server are **Documented** and absent.

### FR-008 — API authentication

Protected routes are intended to require `Authorization: Bearer <Supabase access token>`. Socket handshake reads `socket.handshake.auth.token`.

`jsonwebtoken` is imported in `auth.js` and not called. `JWT_SECRET` and `JWT_EXPIRES_IN` are not used to issue or verify API tokens.

README "JWT authentication" is **Documented**. The check that exists is Supabase `getUser`. **Implemented**, if the function is mounted (WF-1).

### FR-009 — Rate limit and request size

`/api/` is limited to 100 requests per 15 minutes per IP, message "Too many requests from this IP, please try again later." Config keys `RATE_LIMIT_WINDOW` and `RATE_LIMIT_MAX` are not read. The numbers in code match the `env.example` defaults.

JSON and urlencoded bodies are capped at 10MB. `/dashboard/api` is not rate limited.

Implementation: **Implemented** as the hardcoded limiter.

### FR-010 — Health

`GET /health` returns `{ status: 'healthy', timestamp, version, uptime }` with no dependency checks. Docker `healthcheck.js` treats HTTP 200 on that path as success.

`GET /api/auth/health` (GET, not the POST listed in the README) calls `supabase.auth.getSession()` and returns `supabaseConnected: !error`.

`GET /dashboard/api/health` is WF-7.

Implementation: **Implemented**.

### FR-011 — FlutterFlow client surface

`FlutterFlowSyncActions` exposes initialize, read, write, insert, update, delete, batch variants, search, count, exists, latest, date range, sync, status, events, connection, current user, and file helpers.

Widgets: sync status, sync button, connection, progress, data list, file upload, image upload, image gallery.

The four files under `docs/` repeat these as FlutterFlow custom-code instructions, including sample widgets that are not the same source as `client/lib/flutterflow_sync_widgets.dart`.

Implementation: **Implemented** as the Dart library, with the `whereArgs` mismatch in WF-4. The docs' extra sample widgets are **Documented** paste-in examples.

### FR-012 — Scheduled maintenance

WF-6. Implementation: **Implemented**. Configurable interval and real log rotation are **Documented** only.

## Important constraints

- Sync SQL identifies tables and columns. Renaming a Supabase column without editing `syncConfig.js` changes the JSON stored in MySQL only when the query still runs. `SELECT *` tables follow the live row shape. The column-listed tables (`store_user_link`, `task`) drop columns that are not in the list.
- Local tables are not the remote schema. Every synced table is created as `(id, user_id, data JSON, …)`.
- Table names from the URL and from config are interpolated into SQL.
- `execute_sync_query` as printed in the README executes arbitrary SQL as `SECURITY DEFINER`.
- One MySQL database is shared by all users, separated by `user_id` on the local tables. Global bucket rows are still stored with the requesting user's id on the local row, so each user gets their own copy of global data.
- Cleanup deletes `sync_metadata` after 30 days, which also drops those users from the periodic-sync set and from the Users screen.

## Requirements the docs state that the code does not meet

| Claim | Where stated | What the code does |
|---|---|---|
| Offline-first client cache | README, changelog, NewKnowledgeBase | No device store. Server queue only |
| Conflict detection and manual resolution | Changelog, NewKnowledgeBase | Table and a GET route. No inserts, no UI |
| Exponential backoff retries | Changelog, NewKnowledgeBase | Failed rows leave the pending set immediately |
| Incremental sync | Changelog | Full queries every run |
| Joi validation | NewKnowledgeBase | `joi` is not required anywhere. `lodash` and `moment` are dependencies and are not required |
| Unit, integration, e2e, security, and load tests | Changelog | No test file in git. `npm test` runs Jest with no tests. `flutter test` has no test directory |
| API, deployment, and troubleshooting guides | README links `docs/api.md`, `docs/deployment.md`, `docs/troubleshooting.md` | Those paths are not in git |
| MIT license | README link `LICENSE` | No `LICENSE` file |
| Default dashboard password works | README | bcrypt compare against the plaintext default |
| Dashboard served at `:3000/dashboard` | README | Static path, Axios prefix, and router basename do not line up (WF-1, WF-2) |
| `sync_rules.yaml` is the live rules file | README says rules live in `syncConfig.js`; the YAML sits at the repo root | Only the JS module is loaded |

These are discrepancies. They are not rewritten here as new product rules.