# PRD — datafy_powersync

Evidence date: 2026-10-17. Source: `git@bitbucket.org:overdrive/datafy_powersync.git`, `main` at `0aa51f9` (2025-02-25, Jaco Brits, "added abolute paths"). Full SHA `0aa51f98ff6068260c0a96ccac1d1f47310e769e`.

There is no prior PRD in Silicon Ark. `README.md` in the source repository is empty. The demo READMEs describe upstream PowerSync examples. Requirements below are taken from Compose files, YAML, SQL, the demo client, and git history. Each item is labelled.

## Product purpose

Run a self-hosted PowerSync service that replicates a Postgres database into a bucket store and serves a sync API, with rules that copy `task`, `store`, `app_user`, and `store_user_link` to clients.

Provenance: **Inferred** from `config/sync_rules.yaml`, `docker-compose.yaml`, and `config/powersync.yaml`. No file states a customer, a release, or a success measure.

The Node and Supabase folders are upstream todo-list demos. Their screens are lists and todos. Their checked-in sync rules, where this repo replaced them, still name the four Datafy tables. See FR-012.

## Users and roles

| Role | What they do | Evidence | Provenance |
|---|---|---|---|
| Operator | Starts Compose, supplies env vars, mounts config | Compose files and `example.env` | **Implemented** as files. The person is not named beyond git author Jaco Brits `<jaco@netstart.co.za>` |
| Sync client | Connects to the PowerSync API with a JWT. `request.user_id()` is used in sync SQL | `config/sync_rules.yaml`, `client_auth` blocks | **Implemented** call. Which app holds the token is **Unknown / Requires Confirmation** |
| Demo user | Opens the Node demo. A random UUID is stored in `localStorage` and sent as `user_id` | `demos/nodejs/demo-app/src/library/powersync/DemoConnector.ts`, demo README | **Implemented** in the demo. The demo README says the token is not linked to a specific user and that all data syncs to all users |
| Supabase end user | Would authenticate if `client_auth.supabase` is honoured | `supabase: true` in every service YAML | **Implemented** flag. No Supabase project file is in this repo |

No admin role, store-manager role, or permission table is defined here. The sync-rule comment "admin view or similar" is not a role check. See BR-003 in `business-rules.md`.

`flutter_app` (separate Silicon Ark set) documents Rep versus Admin and a different PowerSync URL. This repository does not mention Rep, Admin, or that URL.

## Main workflows

### W1 — Start the root stack

Files: `docker-compose.yaml`, `ps-mongo.yaml`, `example.env`, `config/powersync.yaml`, `config/sync_rules.yaml`.

`example.env` sets `PG_DATABASE_PORT=5432`, `PG_DATABASE_NAME=postgres`, `PG_DATABASE_USER=postgres`, a local placeholder password, `PG_DATABASE_HOST=postgres`, and `PS_PORT=8080`.

The root compose file includes `ps-mongo.yaml`, then starts `powersync` with `depends_on: mongo` (not `mongo-rs-init`). Command is `["start", "-r", "unified"]`. Published port is `${PS_PORT}:${PS_PORT}`. `network_mode` is `"bridge"`.

Expected behaviour that the files themselves do not achieve without a fix is listed under FR-002 and FR-003. Provenance of the workflow steps: **Implemented** as declared services. A successful `docker compose up` was not run for this PRD.

### W2 — Replicate Postgres into Mongo and serve sync

`config/powersync.yaml` sets:

- One Postgres connection, URI `!env PS_PG_URI`, `sslmode: disable`. The file comment says only one connection is supported.
- Storage type `mongodb`, URI `!env PS_MONGO_URI`.
- Listen port `!env PS_PORT`.
- Sync rules path `sync_rules.yaml`.
- `client_auth.supabase: true`, audience `["powersync-dev", "powersync"]`.
- Telemetry `disable_telemetry_sharing: false` (sharing is on).

The root compose file sets `PS_MONGO_URI` to `mongodb://mongo:27017/powersync_datafy` with `replicaSet=rs0` and 30s connect and socket timeouts.

Provenance: **Implemented** in those two files. Whether the process loads `config/powersync.yaml` is FR-002.

### W3 — Choose rows for a user

Trigger: a client sync against the service, after JWT auth. The rules file does not implement the HTTP route; PowerSync does.

`config/sync_rules.yaml`:

1. Bucket `global` has no parameters. It selects every row of `task`, `store`, `app_user`, and `store_user_link`.
2. Bucket `user_tasks` parameters are `store_code` from `store_user_link` where `user_id = request.user_id()`. Data is `task` where `store_code = bucket.store_code`.
3. Bucket `user_stores` uses the same parameter query. Data is `store` where `store_code = bucket.store_code`.

The same SQL is inlined in `config.yaml` and in all four `v2/` service YAML files, and copied to `demos/supabase/config/sync_rules.yaml`.

Provenance: **Implemented** SQL. There is no `active = 1` predicate and no `customer_id` predicate.

### W4 — Run the v2 stack against RDS plus local Postgres

`v2/docker-compose.yaml` starts PowerSync on host port 8080 and a Postgres container published as `5431:5432` with user, database, and password all `postgres`, and `wal_level=logical`. Mongo services are commented out. Volume mounts are absolute paths under `/home/ubuntu/datafy_powersync/v2/`. `POWERSYNC_CONFIG_PATH` is commented out.

The service YAML points replication at an RDS endpoint and bucket storage at a local Postgres URI. Host and port differ between copies. See FR-008. Provenance: **Implemented** files. Deployment to that Ubuntu path is **Unknown / Requires Confirmation**.

### W5 — Node todo demo

`demos/nodejs/README.md` says `docker compose up` from that directory, or `docker compose -f demos/nodejs/docker-compose.yaml up` from the root, and that the UI is at `http://localhost:3030`.

The compose file includes `services/mongo.yaml` and `ps-nodejs-demo.yaml`, extends `services/powersync.yaml` and `services/postgres.yaml`, and mounts `init-scripts` into Postgres. `setup.sql` creates `lists` and `todos`, inserts two todos, and creates publication `powersync` for those two tables.

The client routes are `/` (immediate redirect), `/views/todo-lists`, `/views/todo-lists/:id`, and `/sql-console`. Credentials are `GET {backend}/api/auth/token?user_id=`. Uploads are `POST {backend}/api/data`. Checkpoint mode in the compose build args is `managed`.

Provenance: **Documented** in the demo README and **Implemented** in the client and `setup.sql`. The port `3030` is not the compose mapping (FR-011). The sync rules this demo mounts are the Datafy rules, not `lists` / `todos` (FR-012).

### W6 — Supabase local demo

`demos/supabase/README.md` tells the reader to install the Supabase CLI, copy `.env.template` to `.env`, run `supabase start`, fill the anon key and JWT secret, and `docker compose up`. It says the UI is at `http://localhost:4170`, and that the client stays on "busy with sync" until a todo exists.

`demos/supabase/docker-compose.yaml` starts only the PowerSync service (Mongo include plus extends). It does not build `demo-client`. `Mackup.yml` does build a client and publishes `4170:4173`, and it joins external network `supabase_network_powersync_datafy`.

Provenance: **Documented** README versus **Implemented** compose files. The README's todo behaviour does not match the Datafy sync SQL in `demos/supabase/config/sync_rules.yaml`.

## Functional requirements

### FR-001 — One PowerSync process serves API and replication

The service command is `["start", "-r", "unified"]` in the root compose file, `services/powersync.yaml`, `v2/docker-compose.yaml`, and `docker-compose_old.yaml`. Comments in the root file and in `services/powersync.yaml` say the same image can be split with `start -r api` and `start -r sync`, and that migrations can be run with `migrate up`. Those split commands are comments, not the running command.

Provenance: **Implemented** command. Split mode is **Documented** as an option and not selected.

### FR-002 — The root service must be pointed at a config file that exists

`docker-compose.yaml` mounts:

- `./powersync.yaml` at `/app/powersync.yaml` (read-only). There is no `powersync.yaml` at the repository root.
- `./config` at `/app/config` and again at `/config`.

`POWERSYNC_CONFIG_PATH` is commented out. `config/powersync.yaml` is the file that matches the env vars this compose file sets (`PS_PG_URI`, `PS_MONGO_URI`, `PS_PORT`).

Provenance: **Implemented** mounts and comments. Which path the image reads when `POWERSYNC_CONFIG_PATH` is unset is **Unknown / Requires Confirmation**. The image docs are not in this repo.

### FR-003 — Postgres URI templating has a broken host substitution

Root `docker-compose.yaml` sets:

```text
PS_PG_URI: postgres://${PG_DATABASE_USER}:${PG_DATABASE_PASSWORD}@{PG_DATABASE_HOST}:${PG_DATABASE_PORT}/${PG_DATABASE_NAME}
```

`{PG_DATABASE_HOST}` has no `$`. The other four variables use `${...}`. The scheme is `postgres://`. `config.yaml` uses `postgresql://`.

Provenance: **Implemented** string. Effect on the connection is **Inferred**: the host token is not expanded by Compose. Not executed in this investigation.

### FR-004 — MongoDB is a single-node replica set

`ps-mongo.yaml` and `services/mongo.yaml` run `mongo:7.0` with `--replSet rs0 --bind_ip_all`, publish `27017:27017`, and store data in volume `mongo_storage`. An init container calls `rs.initiate` with one member `mongo:27017`.

`ps-mongo.yaml` sleeps 10 seconds, then tries `rs.status()` and initiates on failure. `services/mongo.yaml` waits until `mongo` is healthy, then initiates only when `rs.status().ok` is false. The root stack includes `ps-mongo.yaml`, not `services/mongo.yaml`.

Provenance: **Implemented**.

`services/powersync.yaml` sets `PS_MONGO_URI` to `mongodb://mongo:27017/powersync_datafy` with no `replicaSet` parameter. The root compose file includes `replicaSet=rs0`. Provenance: **Implemented** difference.

### FR-005 — Root PowerSync does not wait for replica-set init

Root `docker-compose.yaml` `depends_on` lists `mongo` with no condition. It does not list `mongo-rs-init`. `services/powersync.yaml` as extended by the demos waits until `mongo-rs-init` completes successfully.

Provenance: **Implemented**.

### FR-006 — Root PowerSync is on Docker bridge, not the Compose project network

`powersync` sets `network_mode: "bridge"`. The Mongo services in the included file do not set `network_mode`. Commit `9f0d2a1` is titled "Add Bridge in".

Provenance: **Implemented** key. **Inferred** from Docker Compose behaviour: `network_mode: bridge` attaches that service to the default bridge and drops it from the Compose project network, so the hostname `mongo` in `PS_MONGO_URI` may not resolve. Not executed here. **Requires confirmation** if an operator has a working run.

### FR-007 — Client auth is Supabase JWT with two audiences

Every service config sets `client_auth.supabase: true` and audience `powersync-dev` plus `powersync`.

`config/powersync.yaml` leaves `jwks_uri`, static JWKS, and `block_local_jwks` commented. The comment says local JWKS ranges are not blocked by default and that production should enable the block. No second file turns that on.

`demos/supabase/config/powersync.yaml` also sets `supabase_jwt_secret: !env PS_SUPABASE_JWT_SECRET`. The root `config/powersync.yaml` does not. The Supabase demo compose passes `PS_SUPABASE_JWT_SECRET` from the environment. `example.env` does not define it.

Provenance: **Implemented** flags. The live JWKS URL is **Unknown**.

### FR-008 — v2 replicates from RDS and stores buckets in local Postgres

Four files carry the same replication URI shape: user `supabase_admin`, host `datafy-supabase-databasecluster.cx6ggccgkh70.eu-west-1.rds.amazonaws.com`, port `5432`, database `postgres`, `sslmode: disable`. The password is in the files and in git history. It is not copied into Silicon Ark.

| File | Bucket storage URI target |
|---|---|
| `v2/config.yaml` | `postgres` @ `localhost:5431` / `postgres` |
| `v2/powersync.yaml` | same, `localhost:5431` |
| `v2/config/powersync.yaml` | same, `localhost:5431` |
| `v2/config/config.yaml` | `postgres` @ `postgres:5431` / `postgres` |

`v2/docker-compose.yaml` publishes the Postgres container as `5431:5432` and names the service `postgres`. The container's own Postgres port is 5432 (`PGPORT=5432`).

Provenance: **Implemented** URIs. **Inferred**: a process inside the PowerSync container that uses `localhost:5431` does not follow the published host port, and `postgres:5431` is the host port rather than the container port 5432. **Requires confirmation** which file, if any, was the one actually mounted (`POWERSYNC_CONFIG_PATH` is commented out; three different YAML names are mounted).

`sslmode: disable` on that RDS host disagrees with the comment in `config/powersync.yaml`: disable is described as acceptable on a local or private network, not on a public network. Provenance: **Implemented** setting and **Documented** comment.

### FR-009 — Sync rules expose four tables and do not create them

The rules read `task`, `store`, `app_user`, and `store_user_link`. This repository has no `CREATE TABLE` and no `CREATE PUBLICATION` for those names. The only publication in git is `powersync` for `lists` and `todos` in the Node demo.

Provenance: **Implemented** by absence. Where the source schema lives is **Unknown / Requires Confirmation**. Silicon Ark sets for `flutter_app` and `datafy-supabase-functions` describe those table names in other repositories. This repo does not cite them.

### FR-010 — Global bucket is unfiltered; user buckets do not replace it

`task` and `store` are selected in `global` with `SELECT *` and again in `user_tasks` / `user_stores` filtered by `store_code`. `app_user` and `store_user_link` appear only in `global`, as `SELECT *`.

Provenance: **Implemented** SQL. **Inferred**: a client that syncs `global` already receives every row of those four tables, so the user buckets do not hide rows from that client. PowerSync's exact merge of parameterised and global buckets was not executed here.

The comment on `global` says "Sync all tasks for admin view or similar". There is no `role` predicate. Provenance: **Documented** comment, **Implemented** SQL without a role filter.

### FR-011 — Demo ports and env files do not match the READMEs

| Source | Claim |
|---|---|
| `demos/nodejs/README.md` | UI at `http://localhost:3030` |
| `demos/nodejs/ps-nodejs-demo.yaml` | Client published as `${DEMO_CLIENT_PORT}:4173`. Backend port is `${DEMO_BACKEND_PORT}` |
| `demos/nodejs/demo-app/.env.template` | `VITE_BACKEND_URL=http://localhost:6060`, `VITE_POWERSYNC_URL=http://localhost:8080`. Comment says copy `.env.local.template`. The tracked file is `.env.template` |
| `example.env` | Does not define `DEMO_CLIENT_PORT`, `DEMO_BACKEND_PORT`, `DEMO_BACKEND_DATABASE_URI`, `DEMO_JWKS_PUBLIC_KEY`, or `DEMO_JWKS_PRIVATE_KEY` |
| `demos/supabase/README.md` | UI at `http://localhost:4170` |
| `demos/supabase/docker-compose.yaml` | No client service |
| `Mackup.yml` | Client published `4170:4173` |

Provenance: **Documented** versus **Implemented**. A complete env file for the Node demo is not in git.

### FR-012 — Checked-in sync rules do not match the todo demos

`demos/nodejs/init-scripts/setup.sql` and `AppSchema.ts` use `lists` and `todos`. The PowerSync service the Node compose extends mounts `services/powersync.yaml`, whose volume is `../config:/config` (the repo `config/` directory) and whose config path is `/config/powersync.yaml`. That file's rules query `task`, `store`, `app_user`, and `store_user_link`.

`services/powersync.yaml` injects `PS_DATA_SOURCE_URI`. `config/powersync.yaml` reads `PS_PG_URI`. The Supabase demo config reads `PS_DATA_SOURCE_URI` and is mounted explicitly by `demos/supabase/docker-compose.yaml` (`./config:/config`). The Node demo does not override that volume.

Provenance: **Implemented** mismatch. **Inferred** that the Node demo service config does not receive the Postgres URI variable it declares. Not executed here.

`demos/supabase/config/sync_rules.yaml` is the Datafy SQL, while the README still describes a todo list. Provenance: **Documented** versus **Implemented**.

### FR-013 — Demo client identity is a random local UUID

`DemoConnector` reads `ps_user_id` from `localStorage`. If missing, it stores a new UUID. `fetchCredentials` calls the backend token route with that id. `uploadData` posts the CRUD batch to `/api/data` and, when `VITE_CHECKPOINT_MODE` is `custom`, calls `PUT /api/data/checkpoint`. The compose build arg sets `VITE_CHECKPOINT_MODE` to `managed`.

The demo README says this is anonymous authentication and that all data syncs to all users.

Provenance: **Implemented** client and **Documented** README. The backend that issues the token is not source in this repo; the image build clones `https://github.com/powersync-ja/powersync-nodejs-backend-todolist-demo.git` with no ref.

### FR-014 — Telemetry sharing is enabled where it is set

`config/powersync.yaml` sets `telemetry.disable_telemetry_sharing: false` and `client_endpoints: []`. The comment links to `https://docs.powersync.com/self-hosting/telemetry`.

In `config.yaml` the telemetry block is commented out. In the `v2/` YAML files a `telemetry` block with `disable_telemetry_sharing: false` is nested under `client_auth`, not at the document root.

Provenance: **Implemented** difference. Whether the service ignores a nested `telemetry` key is **Unknown**.

### FR-015 — No automated test or pipeline is in git

No test directory, no `bitbucket-pipelines.yml`, no `sonar-project.properties`. The 2026-09-28 scan listed Sonar files that are not in this revision.

Provenance: **Implemented** by absence, plus the scan discrepancy (**Unknown**).

## Integrations

| System | Purpose | Direction | Auth | Failure behaviour in repo |
|---|---|---|---|---|
| Postgres source | Logical replication of application tables | PowerSync reads | URI in env or, under `v2/`, a committed URI. `sslmode: disable` | Not described. No retry policy beyond the image |
| MongoDB `powersync_datafy` | Bucket storage | PowerSync reads and writes | No user or password in the URIs | Root stack does not wait for `rs.initiate` (FR-005) |
| Local Postgres in `v2/` | Bucket storage | PowerSync reads and writes | user/password `postgres` inside the compose file | Healthcheck is `pg_isready`. PowerSync waits until that succeeds |
| Amazon RDS | Replication source in `v2/` only | PowerSync reads | User `supabase_admin`. Password in git, omitted here | `sslmode: disable`. No retry text in repo |
| Supabase Auth | Client JWT | Clients present tokens; flag `supabase: true` | JWT secret only on the Supabase demo config | JWKS URI is a TODO comment |
| PowerSync telemetry | Usage metrics | Outbound from the service | Not set in repo | Sharing left on (FR-014) |
| GitHub `powersync-ja` | Demo backend and Supabase demo UI | Image build clones or uses a Git build context | None in repo | No pin. Build fails if GitHub is unreachable. **Inferred** |
| Docker Hub images | `journeyapps/powersync-service`, `mongo`, `postgres`, `node` | Pull on start or build | None | Tags `latest` and `mongo:7.0` and `node:20` |

`flutter_app` documents a hosted PowerSync project on `powersync.journeyapps.com` and sync buckets `global`, `user_data`, `user_stores`, `user_store_data`, and `user_surveys`, including surveys, promos, and KaChing. This repo's buckets are `global`, `user_tasks`, and `user_stores` over four tables, with `SELECT *` and no `active = 1`.

Provenance of the other set: Silicon Ark `flutter_app/technical-documentation.md`. Provenance of this set: **Implemented** here. They are not the same rule file. Whether one replaced the other is **Unknown / Requires Confirmation**.

## Constraints

- Compose include syntax is commented as requiring Docker Compose greater than 2.20.3.
- `config/powersync.yaml` comments that a single replication connection is what the service supports, and that multiple connections are "on the roadmap". That roadmap is the vendor comment, not an Overdrive plan.
- `sslmode: disable` is the value in every connection block.
- Image tag `latest` is not pinned.
- `services/powersync.yaml` sets `NODE_OPTIONS=--max-old-space-size=1000` and a liveness probe against `http://localhost:${PS_PORT}/probes/liveness` (interval 5s, timeout 1s, 15 retries). The root compose file does not set that memory cap and has no healthcheck. `old.txt` curls `http://localhost:8080/health` instead.
- Demo app licence file is CC0 1.0 (`demos/nodejs/demo-app/LICENSE`).
- YAML language-server schema paths `../schema/schema.json` and `../../../schema/schema.json` are referenced. No `schema.json` is in the repository.

## Error and edge behaviour present in the files

| Case | What the repo does | Provenance |
|---|---|---|
| Missing root `powersync.yaml` | Compose still lists the mount | **Implemented** path. Runtime error text is **Unknown** |
| Missing `$` on `PG_DATABASE_HOST` | The env value is stored as written | **Implemented**. Connection error text is **Unknown** |
| Mongo not yet a replica set | Root PowerSync does not wait; demo PowerSync waits for `mongo-rs-init` | **Implemented** |
| `v2` config path commented out | Three YAML files are mounted at different container paths | **Implemented**. Which one wins is **Unknown** |
| Demo token endpoint non-200 | `DemoConnector.fetchCredentials` throws with status and body | **Implemented** |
| Demo upload non-200 | `uploadData` throws and does not call `transaction.complete` | **Implemented** |
| Empty CRUD queue | `uploadData` returns | **Implemented** |
| Supabase demo with no rows | README says the client shows "busy with sync" until a todo is created | **Documented**. The checked-in rules do not query todos |
| Invalid or expired JWT | No application handler in this repo | **Unknown** (left to the PowerSync image) |
| Duplicate client writes | No idempotency rule in this repo | **Unknown** |