# Technical documentation — flutterflow_sync

Evidence date: 2026-10-15. Source: `git@bitbucket.org:overdrive/flutterflow_sync.git`, `main` at `8525b9f` (2025-09-04, Jaco Brits `jaco@overdrive.co.za`, "first commit"). Full hash `8525b9f526eb25cd9b2452a10ec88a9712e90180`.

The scan-host tree was not available. Findings are from that git revision. Processes were not started. Defects below are call-shape and path mismatches visible in source. Behaviour that depends on a running MySQL, Redis, or Supabase was not observed.

## Repository layout

```text
flutterflow_sync/
├── client/lib/                 Dart library (3 files) + pubspec.yaml
├── dashboard/                  Vite + React + TypeScript
├── docs/                       Four FlutterFlow how-to guides
├── server/src/                 Express application
├── server/Dockerfile
├── server/healthcheck.js
├── docker-compose.yml
├── env.example
├── sync_rules.yaml             Not read by the server
├── README.md
├── CHANGELOG.md
└── NewKnowledgeBase.md
```

No `test/`, `server/sql/`, lockfile, pipeline, LICENSE, or ESLint config file is in the commit.

## Languages and frameworks

| Piece | Stack | Evidence |
|---|---|---|
| Server | Node.js, Express 4.18, Socket.IO 4.7, mysql2 3.6, redis 4.6, `@supabase/supabase-js` 2.38, Winston 3, node-cron 3, multer, sharp, Helmet, express-rate-limit, express-session, connect-redis, bcryptjs, jsonwebtoken (imported, unused), joi / lodash / moment (dependencies, not required) | `server/package.json`, source |
| Server runtime image | `node:18-alpine` | `server/Dockerfile` |
| Dashboard | React 18, React Router 6, Vite 4, TypeScript 5, Tailwind 3, Axios, Chart.js 4, lucide-react, date-fns | `dashboard/package.json` |
| Dashboard image | Build on `node:18-alpine`, serve with `nginx:alpine` | `dashboard/Dockerfile` |
| Client | Dart SDK `>=3.0.0 <4.0.0`, Flutter `>=3.10.0`, supabase_flutter 2, http, socket_io_client, image_picker, file_picker | `client/pubspec.yaml` |
| Data stores | MySQL 8.0 image, Redis 7 Alpine image | `docker-compose.yml` |
| Remote data | Supabase (Auth + Postgres RPC + table writes) | `syncManager.js`, `auth.js` |

README prerequisites say Redis 6.0+. Compose pins Redis 7. **Documented** versus **Implemented** image tag. Both can speak the Redis protocol this client uses. Which version a non-Docker install should run is the README's "6.0+".

## Process model

One Node process (`server/src/server.js`):

- HTTP and Socket.IO on `PORT` (default 3000).
- On `listen`: `syncManager.initialize(io)`, then three cron jobs.
- `SIGTERM` closes the HTTP server and exits 0. It does not call `redis.disconnect()` or end the MySQL pool.

Cron inside the process, not a worker queue:

| Expression | Call |
|---|---|
| `*/5 * * * *` | `performPeriodicSync` |
| `0 2 * * *` | `performCleanup` |
| `0 0 * * *` | log line only |

`SYNC_INTERVAL` (ms, default 300000) is unused. The 5-minute cron is a matching default, not a reader of that variable.

There is no message broker. `pending_changes` is the queue. It is a MySQL table polled during sync, not a worker loop of its own.

## Configuration

`server/src/config/index.js` loads `dotenv` and exports the keys in `env.example`, plus `LOG_MAX_SIZE` (default `20m`) and `LOG_MAX_FILES` (default `14d`).

The logger ignores `LOG_MAX_SIZE` and `LOG_MAX_FILES` and uses 10MB and 5 files.

Defaults that are insecure if unset:

| Key | Code default |
|---|---|
| `JWT_SECRET` | `your-secret-key` (unused by the auth function) |
| `SESSION_SECRET` | `your-session-secret` |
| `DASHBOARD_USERNAME` | `admin` |
| `DASHBOARD_PASSWORD` | `admin123` |
| `DB_PASSWORD` | empty string |
| `CORS_ORIGINS` | `http://localhost:3000` |

Compose passes Supabase keys, `JWT_SECRET`, and `SESSION_SECRET` through from the host environment and defaults the dashboard password to `admin123`. Compose does not pass `SYNC_INTERVAL`, `BATCH_SIZE`, `MAX_RETRY_ATTEMPTS`, rate-limit keys, or `CORS_ORIGINS`.

`env.example` is the template. `.gitignore` does not ignore `.env`. No `.env` is in the commit.

## Startup sequence and defects

Order in `server.js`:

1. Create Express, HTTP server, Socket.IO.
2. Helmet, compression, rate limit, CORS, body parsers.
3. `express-session` with `connect-redis`, client `require('./services/redis').client`.
4. Static `/dashboard` → `server/dashboard/dist` (directory does not exist in git; the app is `dashboard/`).
5. Static `/uploads` → `server/uploads`.
6. `GET /health`.
7. Mount `/dashboard/api` with no session middleware on the router.
8. Mount `/api/auth` with no bearer check.
9. Mount `/api/sync`, `/api/data`, `/api/upload` with the auth module object as middleware.
10. HTML fallbacks for `/dashboard/login` and `/dashboard/*`.
11. `io.use(authMiddleware.socketAuth)` — this property is a function.
12. `listen`, then `syncManager.initialize`.

### D-1 — API auth middleware is not a function

`middleware/auth.js` exports `{ authMiddleware, socketAuth }`. `server.js` does `app.use('/api/sync', authMiddleware, router)` where `authMiddleware` is that object.

Express 4 throws `TypeError` from `app.use` when a handler is not a function. That throw was not observed by running the process. The call shape does not match the export. Socket auth would be reachable only if route registration did not throw first. `io.use(authMiddleware.socketAuth)` itself is a function reference.

### D-2 — Redis session client is null at construction

`RedisService.client` stays `null` until `initialize()`, which runs after `listen`. The session store is constructed at step 3 with that null client.

Whether `connect-redis` 7 throws at construction was not executed.

### D-3 — Schema bootstrap is five statements in one `execute`

`initializeSyncTables` sends five `CREATE TABLE IF NOT EXISTS` scripts in a single string. The pool is created without `multipleStatements: true`. mysql2's default is one statement per `execute`. **Inferred** from that default: schema init fails even if D-1 is fixed. Not executed.

### D-4 — Dashboard password check

`loginHandler` uses `bcrypt.compare(password, config.DASHBOARD_PASSWORD_HASH || config.DASHBOARD_PASSWORD)`. `DASHBOARD_PASSWORD_HASH` is not in `env.example` or Compose. The fallback is plaintext. **Inferred:** bcryptjs does not treat that plaintext as a hash, so the README login does not succeed. Not executed.

### D-5 — Dashboard URL map

| Hop | Path the code produces |
|---|---|
| Axios `baseURL: '/api'` plus path `/dashboard/api/login` | Browser requests `/api/dashboard/api/login` |
| Express route | `POST /dashboard/api/login` |
| Vite proxy | Forwards `/api` to `localhost:3000`, so the wrong path is forwarded unchanged |
| nginx | Proxies `/api/` to `sync_server:3000`. Does not proxy `/dashboard/api` |
| React Router | Routes `/login` and `/*` with no `basename` |
| Express SPA | Serves `index.html` for `/dashboard/*` from `server/dashboard/dist` |

No `vite.config.ts` `base: '/dashboard'`. Dev server port is 3001, which matches the Compose host port for the nginx service, not the Express `/dashboard` URL in the README.

### D-6 — Health Redis check

`routes/dashboard.js` calls `syncManager.redis.exists('health_check')`. `SyncManager` never assigns `redis`. The call throws, the route returns 500, and the Health page does not receive the partial JSON. MySQL and Supabase flags in that handler are then unused.

Supabase health sets `supabaseHealthy = true` when `from('_health_check').select()` does not throw. The `error` property on the result is ignored.

### D-7 — Store code placeholder

`getUserContext` returns `store_codes: string[]`. Queries replace `{{store_code}}`. Missing keys become the SQL token `NULL`. Parameter queries on each bucket are never executed. Store-scoped pulls do not fan out per store.

### D-8 — Upload URL versus directory

Multer destination is `server/uploads/images` or `server/uploads/documents` (`fileUploadService` path `../../uploads` from `server/src/services`). `generateFileUrl` returns `/uploads/<basename>` with no subdirectory. Static `/uploads` and `serve/:filename` look for the basename in `server/uploads`, not in the subdirectory.

### D-9 — Docker install and init SQL

Both Dockerfiles run `npm ci`. No `package-lock.json` is committed. `npm ci` requires a lockfile.

Compose bind-mounts `./server/sql/init.sql` onto the MySQL entrypoint directory. That file is not in git. A bind mount of a missing file fails Compose startup. **Inferred** from Compose file-mount behaviour. Not executed.

The server image context is `./server`, so it does not contain `dashboard/dist` or the repo-root `sync_rules.yaml`. The JS config is under `server/src/config/` and is copied. The YAML is not needed at runtime.

### D-10 — Client `whereArgs`

`FlutterFlowSyncActions.readData` calls `readData(whereArgs: whereArgs)`. `FlutterFlowSyncClient.readData` has no `whereArgs` parameter. README samples use `whereArgs` and `?` placeholders. The server concatenates `where` into the SQL string and does not bind placeholders.

The Dart analyzer was not run. The two signatures disagree.

### D-11 — `package.json` script `build-dashboard`

`cd dashboard && npm run build` runs with the server directory as cwd. The dashboard project is `../dashboard`. **Implemented** script text.

## Data model

Created in `initializeSyncTables` (see D-3) and, for each synced name, in `createTableIfNotExists`:

### `sync_metadata`

`user_id`, `bucket_name`, `table_name` unique together. `last_sync_timestamp` bigint, `sync_status` enum `pending|syncing|completed|error`, `error_message`, `records_synced`, `sync_duration_ms`, timestamps.

The status value `syncing` is in the enum. `executeQuery` writes `completed` or `error` only. The in-memory map holds `syncing` and is not this column.

### `pending_changes`

`user_id`, `table_name`, `operation` enum `INSERT|UPDATE|DELETE`, `data` JSON, `retry_count`, `status` enum `pending|processing|completed|failed`, `error_message`, `created_at`.

`processing` is never written.

### `sync_conflicts`

`user_id`, `table_name`, `record_id`, `local_data`, `remote_data`, `conflict_type` enum `update_conflict|delete_conflict|insert_conflict`, `resolution` enum `local_wins|remote_wins|manual` default `manual`, `resolved_at`. No writer.

### `sync_logs`

`user_id`, `level` enum, `message`, `metadata` JSON, `timestamp`.

### `file_uploads` and per-table document tables

`id` varchar primary key, `user_id`, `data` JSON, `created_at` and `updated_at` bigint epoch ms, `deleted_at` bigint null, `sync_status` enum `synced|pending|conflict`.

`conflict` is never written. Synced remote ids are used when `row.id` is present. Otherwise the code generates `` `${tableName}_${Date.now()}_${Math.random()}` ``.

Remote columns are not MySQL columns. They live inside `data`.

### Redis keys

| Key | Value | TTL |
|---|---|---|
| `sync_stats` | JSON of total, successful, failed, lastSync, activeUsers | 3600 seconds |
| session keys | connect-redis session records, if the store connects | cookie max age 24h |

`saveSyncStats` overwrites the key. Counters reset when the process restarts and the key has expired or Redis was empty. `loadSyncStats` merges on init.

## API catalogue

Auth intended for `/api/sync`, `/api/data`, `/api/upload`: `Authorization: Bearer <Supabase access token>`.

| Method | Path | Body / query | Success body |
|---|---|---|---|
| GET | `/health` | | `{ status, timestamp, version, uptime }` |
| GET | `/api/auth/health` | | `{ success, message, supabaseConnected, timestamp }` |
| POST | `/api/sync/trigger` | optional JSON, logged only | `{ success, message, timestamp }` |
| GET | `/api/sync/status` | | `{ success, data: metadata rows }` |
| GET | `/api/sync/history` | `limit` default 50, `offset` default 0 | `{ success, data }` |
| GET | `/api/data/:tableName` | `where`, `orderBy`, `limit`, `offset` | `{ success, data, count }` |
| POST | `/api/data/:tableName` | `{ operation, data }` | `{ success, message, timestamp }` or 400 |
| POST | `/api/upload/single` | multipart field `file` | `{ success, data: file record, message }` |
| POST | `/api/upload/multiple` | multipart field `files` | `{ success, data, count, message }` |
| GET | `/api/upload/files` | `limit`, `offset`, `mimeType` | `{ success, data, count }` |
| DELETE | `/api/upload/files/:fileId` | | `{ success, message }` or 404 |
| GET | `/api/upload/serve/:filename` | | file stream or 404 |
| GET | `/uploads/:path` | | static files |
| POST | `/dashboard/api/login` | `{ username, password }` | `{ success, message, user }` |
| POST | `/dashboard/api/logout` | | `{ success, message }` |
| GET | `/dashboard/api/auth/check` | | `{ authenticated }` or with `user`, `loginTime` |
| GET | `/dashboard/api/stats` | | counters plus `activeUsers`, `pendingChanges`, `errorCount` |
| GET | `/dashboard/api/logs` | `userId`, `level`, `startDate`, `endDate`, `limit` | `{ success, data }` |
| GET | `/dashboard/api/sync-status` | | up to 1000 metadata rows |
| GET | `/dashboard/api/pending-changes` | `limit`, `offset` | |
| GET | `/dashboard/api/conflicts` | `limit`, `offset` | |
| GET | `/dashboard/api/users` | | aggregates |
| POST | `/dashboard/api/trigger-sync/:userId` | | `{ success, message }` immediately |
| GET | `/dashboard/api/health` | | `{ database, redis, supabase, uptime, memory, timestamp }` or 500 (D-6) |

Socket.IO, after a token on `handshake.auth.token`:

| Event | Direction | Payload |
|---|---|---|
| `request_sync` | client → server | passed to `handleClientSyncRequest` as `data` |
| `sync_started` | server → user room | `{ userId, timestamp }` |
| `sync_completed` | server → user room | `{ userId, timestamp, status: 'success', recordsSynced, pendingProcessed, duration }` |
| `sync_error` | server → user room, and to the requesting socket on throw | `{ userId, timestamp, error, duration }` or `{ message }` |
| `data_updated` | client listens | server does not emit |

Server socket CORS methods are GET and POST only.

## Sync implementation notes

`executeQuery` replaces every placeholder with `'<value>'` or `NULL` using `replaceAll`. Values are not bound as MySQL or Postgres parameters and quotes inside the value are not escaped.

The RPC name and argument are fixed: `execute_sync_query`, `{ query: processedQuery }`. README shows the function wrapping the text in `SELECT json_agg(row_to_json(t)) FROM (<query>) t`. If that function returns one JSON array, `data.length` in the server is the length of whatever the Supabase client puts in `data`. **Unknown / Requires Confirmation** whether the client receives an array of rows or a single json aggregate value. The code treats `data` as an array of row objects with an `id` field (`data.length`, `row.id`). A single aggregated JSON value would not match that loop. This was not called against Supabase.

`processPendingChange` uses the table name from the queued row as `.from(table_name)`. That name is the URL parameter from the original write, or the literal `file_uploads`.

`storeQueryResults` and `createTableIfNotExists` interpolate `tableName` into DDL and DML.

Connection pool: `connectionLimit: 10`, `queueLimit: 0`, `acquireTimeout: 60000`, `timeout: 60000`, `reconnect: true`. mysql2 pool option names have changed across 3.x; whether `acquireTimeout`, `timeout`, and `reconnect` are honoured was not executed.

User context also calls `supabase.auth.admin.getUserById` and `from('store_user_link').select('store_code').eq('user_id', userId)` using the service-role client. `user_email` is stored on the context object and no query placeholder reads it. Failures return empty store codes and a null email.

## File processing

Directories created at service load: `server/uploads/images`, `documents`, `thumbnails`.

Filename: `<uuid>-<epoch><original extension>`.

Processed image path: original path with `_processed.jpeg` (route passes `format` default `jpeg` inside the service; the route's `processImage` options do not set `format`, so the default applies). Sharp is always asked for JPEG bytes. The `format` argument only changes the filename.

Thumbnail: `uploads/thumbnails/<basename>_thumb.jpg`.

Delete looks for the thumbnail at `server/uploads/<basename of thumbnail_url>`. `thumbnail_url` is `/uploads/<thumb file name>`, so the basename is the thumb file, and the lookup directory is the uploads root rather than `thumbnails/`. **Implemented** path join. The thumbnail file is likely left on disk. Not executed.

Public URL stored on the record is the middleware path, not a Supabase Storage URL.

## Logging and monitoring

Winston JSON logs under `server/logs/` (path `../../logs` from `server/src/utils`). Service metadata `flutterflow-sync-middleware`.

Docker healthcheck hits `GET /health` on localhost every 30 seconds, timeout 3 seconds, 3 retries. That endpoint does not check MySQL, Redis, or Supabase.

nginx in the dashboard image returns text `healthy` for its own `/health`. That does not reflect the Node process.

No metrics exporter, no alert transport, no APM. Changelog and NewKnowledgeBase describe alerting. **Documented** only.

Overview and Health poll every 30 seconds over HTTP. That is the dashboard's refresh mechanism.

## Security controls present in source

| Control | Where | Limit |
|---|---|---|
| Helmet CSP | `server.js` | `styleSrc` and `scriptSrc` allow `cdn.jsdelivr.net` and, for styles, `'unsafe-inline'`. `imgSrc` allows any `https:` |
| CORS allow-list | config | Default is localhost only |
| Rate limit | `/api/` | Fixed window, not applied to dashboard JSON |
| Bearer check via Supabase | `auth.js` | Not mounted as a function (D-1) |
| Shared dashboard password | `dashboardAuth.js` | bcrypt mismatch (D-4); JSON routes ungated (business rule BR-004) |
| Cookie flags | session | `httpOnly`; `secure` only in production |
| Upload MIME and size | multer | Client-supplied MIME. No content sniff beyond sharp's later read of images |
| Production error sanitisation | `errorHandler` | Route catches often send `error.message` anyway |
| Non-root user in the server image | Dockerfile | `nodejs` uid 1001 |

Not present: CSRF token, dashboard lockout, file download authorisation, SQL allow-list, secret scanning, RLS enforcement in this repo, hashing of the dashboard password at rest.

`execute_sync_query` as written in the README is dynamic SQL. The server sends full query strings, with substituted user ids, to that RPC. A compromised or buggy caller that reached `executeQuery` with a crafted context value can close the quote. The current substitution wraps the value in single quotes and does not escape. Table names from the client URL are concatenated on the MySQL side in `routes/data.js`.

This is a description of the code. It is not a procedure.

## Development and test

| Command | Defined | What git supports |
|---|---|---|
| `npm start` in `server/` | `node src/server.js` | Source is present. D-1 is on the startup path |
| `npm run dev` | nodemon | Dev dependency |
| `npm test` | `jest` | No tests, no Jest config file |
| `npm run lint` in `server/` | `eslint src/` | No ESLint config in git |
| `npm run build` in `dashboard/` | `vite build` | No lockfile |
| `flutter test` in `client/` | README | No `test/` directory. `flutter_lints` is a dev dependency. No `analysis_options.yaml` |
| `flutter pub get` | README | `pubspec.yaml` only. No `pubspec.lock` |

No Bitbucket Pipelines file, no GitHub Actions, no editor config, no seed data.

`client/` is not a Flutter app. It has no `lib` export barrel beyond the three libraries, and no `analysis_options.yaml`. FlutterFlow integration, per `docs/`, is copy-paste of these files into a FlutterFlow custom-code project. The package name in the import sample (`package:flutterflow_sync_client/...`) matches `pubspec.yaml` `name`.

## Deployment shapes in the repo

1. **Bare Node**, README: MySQL and Redis already running, `npm start`, dashboard built and expected at `http://localhost:3000/dashboard`. PM2 example: `pm2 start src/server.js --name sync-middleware` from `server/`.
2. **Compose**: MySQL `3306`, Redis `6379`, server `3000`, dashboard nginx `3001:80`, network `sync_network`, volumes `mysql_data` and `redis_data`, server log bind `./server/logs`. Restart policy `unless-stopped`.
3. **README Dockerfile snippet** under "Docker Deployment": a generic `node:18-alpine` file that runs `npm run build`. The server `package.json` has no `build` script that compiles the server (`build-dashboard` is a different script). The committed `server/Dockerfile` does not run a build. Two Docker stories, one of them a README example.

No Kubernetes, no IaC, no host name, no TLS terminator. README production section says to use HTTPS, strong secrets, a firewall, and monitoring, without files for those.

`NODE_ENV=production` in Compose for the server. That turns off the Winston console transport and turns on the secure session cookie. A secure cookie over plain HTTP on port 3000 will not be stored by the browser. **Inferred** from cookie rules. The Compose port mapping is HTTP.

## Related documents in this directory

- `project-plan.md` — purpose, scope, status.
- `prd.md` — workflows and functional requirements.
- `features.md` — current, stub, documented-only, planned, missing files.
- `business-rules.md` — sync predicates, queue, retention, access.

## Human confirmation still required

- Which FlutterFlow and Supabase project these table names belong to.
- Whether `execute_sync_query` exists there and what shape it returns.
- Whether `sync_rules.yaml` came from PowerSync.
- Whether `main` has ever been run or deployed.
- Whether the scan-host Sonar files were meant to stay untracked.
- Whether the changelog 1.1 / 1.2 / 2.0 items are still the plan.
- Whether dashboard JSON being unauthenticated, and local SQL concatenation, are accepted or defects to fix. This document records them. It does not change the requirement.