PowerSync self-hosting — operator prep on Supabase, sops, and sync rules export #37

New issue

Closed

opened 2026-05-24 23:47:58 +02:00 by dominik.polakovics · 2 comments

dominik.polakovics commented 2026-05-24 23:47:58 +02:00

Owner

Copy link

Problem Statement

PowerSync Cloud is being retired for the Cloonar fit app in favour of self-hosting PowerSync Service on web-arm. The implementation work is fully designed and scoped in a separate ready-for-agent issue, but several prerequisites can only be carried out by the operator (Supabase SQL editor access, decrypting sops, retrieving sync rules from the cloonar-fit repo or the Cloud dashboard). The agent's implementation issue is blocked until these artifacts exist.

Solution

A checklist of operator-only prep steps. Completing every item produces the inputs the implementation issue needs, after which the operator removes the blocked by relationship and the implementation can proceed.

User Stories

1. As the operator, I want a dedicated powersync_selfhost Postgres role on Supabase with LOGIN and REPLICATION attributes and a strong password, so that the self-hosted PowerSync Service can replicate logically from Supabase without sharing credentials with the existing Cloud setup.
2. As the operator, I want the powersync_selfhost role granted SELECT on every table PowerSync syncs (with ALTER DEFAULT PRIVILEGES covering future tables), so that sync rules don't fail at runtime with permission-denied.
3. As the operator, I want a parallel publication powersync_selfhost whose table list mirrors the existing Cloud publication, so that the self-hosted service replicates exactly the same data as Cloud during the parallel-run validation window.
4. As the operator, I want to verify the current Cloud publication's table list before recreating it for the self-hosted role, so that the new publication doesn't silently miss tables added since the Cloud setup was first configured.
5. As the operator, I want the Supabase replication DSN (with sslmode=require) added to hosts/web-arm/secrets.yaml under the key powersync-source-dsn, so that the implementation can reference the secret through sops without the agent ever touching secret material.
6. As the operator, I want the current sync rules retrieved from the cloonar-fit repo (or exported from the Cloud dashboard if the cloonar-fit copy is stale) and attached to this issue, so that the implementation can drop the file at the configured path without my flipping between contexts mid-PR.
7. As the operator, I want to record the path of the Flutter URL constant in the cloonar-fit repo on this issue, so that cutover-day client release work can find it without a second hunt.
8. As the operator, I want to manually verify each prep step succeeded (role exists with REPLICATION attribute, publication includes the expected tables, sops file decrypts, replication user can connect over TLS) before unblocking the implementation issue, so that the agent doesn't pick up work whose prerequisites are still missing.
9. As a future me, I want a record of the migration's prerequisites and the order in which they were satisfied, so that if I ever do this again (or have to roll back) I have a runbook.

Implementation Decisions

This issue produces operator-only artifacts; no NixOS modules, no code.

• Supabase SQL (executed in the project's SQL editor): audit the existing Cloud publication via pg_publication_tables; create the powersync_selfhost role with LOGIN REPLICATION and a generated strong password; grant USAGE on public, SELECT on all current tables, and matching default privileges for future tables; create publication powersync_selfhost for the audited table list. Do not create the replication slot manually — PowerSync auto-creates it on first connect with slot_name: powersync_selfhost in its config.
• sops secret: open hosts/web-arm/secrets.yaml via nix-shell -p sops --run 'sops hosts/web-arm/secrets.yaml' and add a powersync-source-dsn entry. Per CLAUDE.md, only the operator edits encrypted files. The value is the full DSN: postgresql://powersync_selfhost:PASSWORD@db.<project>.supabase.co:5432/postgres?sslmode=require.
• Sync rules artifact: if the cloonar-fit repo's current copy is the source of truth, paste it into a comment on this issue. Otherwise export from the Cloud dashboard and paste that. The implementation issue copies it byte-for-byte into the NixOS repo.
• Flutter URL constant location: file path + constant name noted as a comment on this issue.
• Sequencing: the Supabase SQL must land before the sops secret is added (the DSN's password is generated by the role creation step). Sync rules export and Flutter URL note can happen in any order relative to those.

Testing Decisions

Verification, not unit tests. After each prep step:

• \du powersync_selfhost on Supabase shows the Replication attribute.
• SELECT * FROM pg_publication_tables WHERE pubname = 'powersync_selfhost'; lists the expected tables.
• nix-shell -p sops --run 'sops -d hosts/web-arm/secrets.yaml' decrypts cleanly and shows the new key.
• From a host with network access to Supabase: psql "<dsn>" -c "SELECT 1;" succeeds. (This confirms the password, the role's LOGIN attribute, and sslmode=require all line up.)

Out of Scope

• All NixOS module code, nginx vhost, container definition, update.sh, postgres storage role bootstrap, blackbox probe — covered in the linked implementation issue.
• The Flutter cutover release itself (separate work in the cloonar-fit repo).
• PowerSync Cloud teardown — happens after parallel-run validation succeeds, tracked in the linked implementation issue's closing steps.

Further Notes

• This issue is p0 because PowerSync Cloud cost is the motivating concern; the longer this sits, the longer that monthly subscription continues.
• Once all checklist items above are done, edit the linked implementation issue to remove the blocked by reference (or close this issue, which surfaces the dependency in Forgejo's UI). The implementation issue is already labelled ready-for-agent; closing this one effectively releases it.

## Problem Statement PowerSync Cloud is being retired for the Cloonar fit app in favour of self-hosting PowerSync Service on `web-arm`. The implementation work is fully designed and scoped in a separate `ready-for-agent` issue, but several prerequisites can only be carried out by the operator (Supabase SQL editor access, decrypting `sops`, retrieving sync rules from the cloonar-fit repo or the Cloud dashboard). The agent's implementation issue is blocked until these artifacts exist. ## Solution A checklist of operator-only prep steps. Completing every item produces the inputs the implementation issue needs, after which the operator removes the `blocked by` relationship and the implementation can proceed. ## User Stories 1. As the operator, I want a dedicated `powersync_selfhost` Postgres role on Supabase with `LOGIN` and `REPLICATION` attributes and a strong password, so that the self-hosted PowerSync Service can replicate logically from Supabase without sharing credentials with the existing Cloud setup. 2. As the operator, I want the `powersync_selfhost` role granted `SELECT` on every table PowerSync syncs (with `ALTER DEFAULT PRIVILEGES` covering future tables), so that sync rules don't fail at runtime with permission-denied. 3. As the operator, I want a parallel publication `powersync_selfhost` whose table list mirrors the existing Cloud publication, so that the self-hosted service replicates exactly the same data as Cloud during the parallel-run validation window. 4. As the operator, I want to verify the current Cloud publication's table list before recreating it for the self-hosted role, so that the new publication doesn't silently miss tables added since the Cloud setup was first configured. 5. As the operator, I want the Supabase replication DSN (with `sslmode=require`) added to `hosts/web-arm/secrets.yaml` under the key `powersync-source-dsn`, so that the implementation can reference the secret through sops without the agent ever touching secret material. 6. As the operator, I want the current sync rules retrieved from the cloonar-fit repo (or exported from the Cloud dashboard if the cloonar-fit copy is stale) and attached to this issue, so that the implementation can drop the file at the configured path without my flipping between contexts mid-PR. 7. As the operator, I want to record the path of the Flutter URL constant in the cloonar-fit repo on this issue, so that cutover-day client release work can find it without a second hunt. 8. As the operator, I want to manually verify each prep step succeeded (role exists with REPLICATION attribute, publication includes the expected tables, sops file decrypts, replication user can connect over TLS) before unblocking the implementation issue, so that the agent doesn't pick up work whose prerequisites are still missing. 9. As a future me, I want a record of the migration's prerequisites and the order in which they were satisfied, so that if I ever do this again (or have to roll back) I have a runbook. ## Implementation Decisions This issue produces operator-only artifacts; no NixOS modules, no code. - **Supabase SQL** (executed in the project's SQL editor): audit the existing Cloud publication via `pg_publication_tables`; create the `powersync_selfhost` role with `LOGIN REPLICATION` and a generated strong password; grant `USAGE` on `public`, `SELECT` on all current tables, and matching default privileges for future tables; create publication `powersync_selfhost` for the audited table list. **Do not create the replication slot manually** — PowerSync auto-creates it on first connect with `slot_name: powersync_selfhost` in its config. - **sops secret**: open `hosts/web-arm/secrets.yaml` via `nix-shell -p sops --run 'sops hosts/web-arm/secrets.yaml'` and add a `powersync-source-dsn` entry. Per CLAUDE.md, only the operator edits encrypted files. The value is the full DSN: `postgresql://powersync_selfhost:PASSWORD@db.<project>.supabase.co:5432/postgres?sslmode=require`. - **Sync rules artifact**: if the cloonar-fit repo's current copy is the source of truth, paste it into a comment on this issue. Otherwise export from the Cloud dashboard and paste that. The implementation issue copies it byte-for-byte into the NixOS repo. - **Flutter URL constant location**: file path + constant name noted as a comment on this issue. - **Sequencing**: the Supabase SQL must land before the sops secret is added (the DSN's password is generated by the role creation step). Sync rules export and Flutter URL note can happen in any order relative to those. ## Testing Decisions Verification, not unit tests. After each prep step: - `\du powersync_selfhost` on Supabase shows the `Replication` attribute. - `SELECT * FROM pg_publication_tables WHERE pubname = 'powersync_selfhost';` lists the expected tables. - `nix-shell -p sops --run 'sops -d hosts/web-arm/secrets.yaml'` decrypts cleanly and shows the new key. - From a host with network access to Supabase: `psql "<dsn>" -c "SELECT 1;"` succeeds. (This confirms the password, the role's LOGIN attribute, and `sslmode=require` all line up.) ## Out of Scope - All NixOS module code, nginx vhost, container definition, `update.sh`, postgres storage role bootstrap, blackbox probe — covered in the linked implementation issue. - The Flutter cutover release itself (separate work in the cloonar-fit repo). - PowerSync Cloud teardown — happens after parallel-run validation succeeds, tracked in the linked implementation issue's closing steps. ## Further Notes - This issue is `p0` because PowerSync Cloud cost is the motivating concern; the longer this sits, the longer that monthly subscription continues. - Once all checklist items above are done, edit the linked implementation issue to remove the `blocked by` reference (or close this issue, which surfaces the dependency in Forgejo's UI). The implementation issue is already labelled `ready-for-agent`; closing this one effectively releases it.

dominik.polakovics added the

ready-for-human

enhancement

p0

labels 2026-05-24 23:47:58 +02:00

dominik.polakovics referenced this issue 2026-05-24 23:49:20 +02:00

Implement self-hosted PowerSync on web-arm (Cloonar fit) #38

dominik.polakovics commented 2026-06-03 14:26:16 +02:00

Author

Owner

Copy link

This was generated by AI during triage.

Sync rules — operator-supplied artifact for this issue (US#6). #38 copies this byte-for-byte into the NixOS repo at the sync-rules path it configures.

bucket_definitions:
  global:
    data:
      - SELECT * FROM exercise_library WHERE is_system = true

  user_data:
    parameters: SELECT request.user_id() as user_id
    data:
      - SELECT * FROM exercise_library WHERE user_id = bucket.user_id
      - SELECT * FROM workout_templates WHERE user_id = bucket.user_id
      - SELECT * FROM workout_sessions WHERE user_id = bucket.user_id
      - SELECT * FROM user_profiles WHERE user_id = bucket.user_id
      - SELECT * FROM user_metrics WHERE user_id = bucket.user_id
      - SELECT * FROM chat_messages WHERE user_id = bucket.user_id
      # Explicit columns to exclude apple_original_transaction_id and manual_override_reason from client
      - SELECT id, user_id, status, subscription_tier, subscription_platform, current_period_start, current_period_end, manual_override, manual_override_expires_at, created_at, updated_at FROM subscriptions WHERE user_id = bucket.user_id
      - SELECT * FROM user_exercise_overrides WHERE user_id = bucket.user_id
      - SELECT * FROM feedback WHERE user_id = bucket.user_id
      - SELECT * FROM user_ai_usage WHERE user_id = bucket.user_id

  user_template_exercises:
    parameters: SELECT id as template_id FROM workout_templates WHERE user_id = request.user_id()
    data:
      - SELECT * FROM template_exercises WHERE template_id = bucket.template_id

  user_template_sets:
    parameters: SELECT id as template_id FROM workout_templates WHERE user_id = request.user_id()
    data:
      - SELECT * FROM template_sets WHERE template_id = bucket.template_id

  user_session_exercises:
    parameters: SELECT id as session_id FROM workout_sessions WHERE user_id = request.user_id()
    data:
      - SELECT * FROM session_exercises WHERE session_id = bucket.session_id

  user_session_sets:
    parameters: SELECT id as session_id FROM workout_sessions WHERE user_id = request.user_id()
    data:
      - SELECT * FROM session_sets WHERE session_id = bucket.session_id

Validation — cross-checked against the powersync_selfhost publication: all 14 published tables are referenced, and no table outside the publication is queried. Coverage is 1:1.

Note: the subscriptions rule projects explicit columns, so apple_original_transaction_id and manual_override_reason never reach clients. They are still logically replicated into web-arm's powersync_storage DB (whole-row replication) — excluded from devices, not from the server.

> *This was generated by AI during triage.* **Sync rules** — operator-supplied artifact for this issue (US#6). #38 copies this **byte-for-byte** into the NixOS repo at the sync-rules path it configures. ```yaml bucket_definitions: global: data: - SELECT * FROM exercise_library WHERE is_system = true user_data: parameters: SELECT request.user_id() as user_id data: - SELECT * FROM exercise_library WHERE user_id = bucket.user_id - SELECT * FROM workout_templates WHERE user_id = bucket.user_id - SELECT * FROM workout_sessions WHERE user_id = bucket.user_id - SELECT * FROM user_profiles WHERE user_id = bucket.user_id - SELECT * FROM user_metrics WHERE user_id = bucket.user_id - SELECT * FROM chat_messages WHERE user_id = bucket.user_id # Explicit columns to exclude apple_original_transaction_id and manual_override_reason from client - SELECT id, user_id, status, subscription_tier, subscription_platform, current_period_start, current_period_end, manual_override, manual_override_expires_at, created_at, updated_at FROM subscriptions WHERE user_id = bucket.user_id - SELECT * FROM user_exercise_overrides WHERE user_id = bucket.user_id - SELECT * FROM feedback WHERE user_id = bucket.user_id - SELECT * FROM user_ai_usage WHERE user_id = bucket.user_id user_template_exercises: parameters: SELECT id as template_id FROM workout_templates WHERE user_id = request.user_id() data: - SELECT * FROM template_exercises WHERE template_id = bucket.template_id user_template_sets: parameters: SELECT id as template_id FROM workout_templates WHERE user_id = request.user_id() data: - SELECT * FROM template_sets WHERE template_id = bucket.template_id user_session_exercises: parameters: SELECT id as session_id FROM workout_sessions WHERE user_id = request.user_id() data: - SELECT * FROM session_exercises WHERE session_id = bucket.session_id user_session_sets: parameters: SELECT id as session_id FROM workout_sessions WHERE user_id = request.user_id() data: - SELECT * FROM session_sets WHERE session_id = bucket.session_id ``` **Validation** — cross-checked against the `powersync_selfhost` publication: all 14 published tables are referenced, and no table outside the publication is queried. Coverage is 1:1. > Note: the `subscriptions` rule projects explicit columns, so `apple_original_transaction_id` and `manual_override_reason` never reach clients. They are still logically replicated into web-arm's `powersync_storage` DB (whole-row replication) — excluded from devices, not from the server.

dominik.polakovics commented 2026-06-03 14:44:02 +02:00

Author

Owner

Copy link

This was generated by AI during triage.

Operator prep complete — closing

• ✅ Supabase: powersync_selfhost role (LOGIN REPLICATION) + grants (SELECT on all public tables + default privileges for future tables) + powersync_selfhost publication over the 14 audited tables.
• ✅ sops secret reptide-powersync-source-dsn added to hosts/web-arm/secrets.yaml (on main). Named with the reptide- prefix to match web-arm's <app>-<purpose> secret convention (cf. fueltide-supabase-db-password); #38's three references were updated to the prefixed key.
• ✅ Sync rules attached (comment above); validated 1:1 against the publication's 14 tables.
• ⏭️ Flutter URL constant location — deferred to the cutover release (an input to the client cutover, not to #38's NixOS work).

Note for #38: the DSN targets Supabase's IPv4 pooler (the direct db.<project> endpoint is IPv6-only). psql … SELECT 1 over TLS succeeds, but that does not exercise logical replication — pooler compatibility (a transaction-mode pooler cannot carry the logical-replication protocol) must be confirmed when the service first connects and creates its slot.

#38 remains needs-triage pending re-review.

> *This was generated by AI during triage.* ## Operator prep complete — closing - ✅ **Supabase**: `powersync_selfhost` role (`LOGIN REPLICATION`) + grants (SELECT on all public tables + default privileges for future tables) + `powersync_selfhost` publication over the 14 audited tables. - ✅ **sops secret** `reptide-powersync-source-dsn` added to `hosts/web-arm/secrets.yaml` (on `main`). Named with the `reptide-` prefix to match web-arm's `<app>-<purpose>` secret convention (cf. `fueltide-supabase-db-password`); #38's three references were updated to the prefixed key. - ✅ **Sync rules** attached (comment above); validated 1:1 against the publication's 14 tables. - ⏭️ **Flutter URL constant location** — deferred to the cutover release (an input to the client cutover, not to #38's NixOS work). **Note for #38:** the DSN targets Supabase's **IPv4 pooler** (the direct `db.<project>` endpoint is IPv6-only). `psql … SELECT 1` over TLS succeeds, but that does not exercise logical replication — pooler compatibility (a transaction-mode pooler cannot carry the logical-replication protocol) must be confirmed when the service first connects and creates its slot. #38 remains `needs-triage` pending re-review.

dominik.polakovics closed this issue 2026-06-03 14:44:02 +02:00

dominik.polakovics referenced this issue 2026-06-03 21:20:24 +02:00

Implement self-hosted PowerSync on web-arm (Cloonar fit) #38

dominik.polakovics referenced this issue from a commit 2026-06-03 21:58:57 +02:00

feat(web-arm): self-host PowerSync Service for Cloonar fit

dominik.polakovics referenced this issue 2026-06-03 21:59:42 +02:00

feat(web-arm): self-host PowerSync Service for Cloonar fit #87

dominik.polakovics referenced this issue 2026-06-04 21:20:24 +02:00

feat(web-arm): fetch PowerSync sync rules live from Cloonar/fit instead of vendoring #95

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

chore/epicenter-vm-disk-128g

fix/lab-pin-opus-4-8

feat/diag-on-internal-vms

feat/dev-lab

feat/fw-cpu-priorities-and-nix-shell

No results found.

Labels

Clear labels   

bug

  

enhancement

  

in-progress

  

needs-info

  

needs-triage

  

p0

  

ready-for-agent

  

ready-for-human

  

wontfix

No labels

bug

enhancement

in-progress

needs-info

needs-triage

p0

ready-for-agent

ready-for-human

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

Cloonar/nixos#37

No description provided.