productgalaxy — shared DB + APIs + MCP behind 4 existing apps

Context

Four sibling tools under /Users/parhumm/Projects/SabaIdea/product/ track complementary slices of the same businesses (Televika, Filimo, Aparat, Forsi, and competitors Namava, GapFilm, FilmNet, Sheyda, plus 14 foreign VODs):

• ABtest-dashboard — A/B tests in Supabase, per-brand product column. Vanilla JS UI.
• Product-Audits — UX/health audits, issues with status enum, in per-brand JSON folders. React + Vite UI.
• product-decisions — PM work items (PM-001…) with goals/areas/journeys. React + Vite UI. JSON + localStorage + unwired D1 schema.
• Televika-Foreign-Comment-Analysis-Dashboard — 220K+ scraped app-store reviews + AI-generated insights. Flask + Alpine.js UI. JSON files.

Each app is owned by a separate team. Their UIs do not change. They are kept as-is (or very nearly so). The only change inside each app is the data layer — it stops reading/writing local JSON, localStorage, or its own Supabase instance, and instead calls a versioned REST API exposed by productgalaxy.

productgalaxy does not ship a domain UI competing with the four apps. It is:

• One PostgreSQL 17 database (source of truth for every domain)
• Versioned REST API per domain (/api/v1/audit-issues, /api/v1/abtests, /api/v1/pm-tasks, /api/v1/comments, …)
• MCP server (Streamable HTTP) for Claude/LLM access — read + structured writes
• Minimal admin UI for shared dims: businesses, users + RBAC, taxonomies, share-link tokens, audit log, importer/sync status
• Per-entity share links (token + optional Argon2id password + optional expiry)

Goal: Galaxy is the new SoT for IDs, statuses, cross-domain links, business taxonomy, and user auth — but every team keeps its UI and ships independently. Old data stores are deprecated per-app, on each team's own schedule, once their data-layer swap is verified.

---

Architecture

                        ┌──────────────────────────────────┐
                        │     Claude / Claude Desktop      │
                        └────────────────┬─────────────────┘
                                         │ MCP Streamable HTTP (2026-07-28 spec)
                                         │ + Bearer JWT (Better Auth-issued)
                                         ▼
   ┌──────────────────────────────────────────────────────────────────────┐
   │  productgalaxy (Docker Compose: caddy + app + mcp + postgres + bk)   │
   │                                                                       │
   │  ┌──────────────┐   ┌──────────────────────┐   ┌──────────────────┐ │
   │  │  Admin UI    │   │   REST API (v1)       │   │   MCP server     │ │
   │  │  (Next.js)   │   │  Hono routes inside   │   │ (Node + sdk +    │ │
   │  │ businesses,  │   │  Next.js              │   │  Streamable HTTP)│ │
   │  │ users, tax.  │   │  Zod-OpenAPI 3.1      │   │  read + narrow   │ │
   │  │ share links, │   │  generates clients    │   │  write tools     │ │
   │  │ audit log    │   │  for TS + Python      │   │  + audit log     │ │
   │  └──────┬───────┘   └──────────┬───────────┘   └────────┬─────────┘ │
   │         └──────────────┬───────┴────────────────────────┘           │
   │                        ▼                                             │
   │             ┌──────────────────────┐                                 │
   │             │  PostgreSQL 17        │ + pgvector + pg_trgm           │
   │             │  + Hunspell FA dict   │   comments partitioned by year │
   │             │  + Generated FTS cols │                                 │
   │             └──────────┬───────────┘                                 │
   │                        │ WAL stream                                  │
   │                        ▼                                             │
   │             ┌──────────────────────┐                                 │
   │             │   pgBackRest sidecar │  → S3-compatible (Backblaze B2  │
   │             │   PITR + retention   │     or MinIO on second volume)  │
   │             └──────────────────────┘                                 │
   └─────────────────────────▲────────────────────────────────────────────┘
                             │ REST + per-app OAuth 2.0 client-credentials JWT
       ┌─────────────────────┼─────────────────────┬─────────────────────┐
       │                     │                     │                     │
┌──────┴──────┐    ┌─────────┴────────┐  ┌─────────┴────────┐  ┌─────────┴───────┐
│ ABtest-     │    │  Product-Audits  │  │ product-decisions │  │ Televika        │
│ dashboard   │    │  (React + Vite)  │  │ (React + Vite)    │  │ Comment Anal.   │
│ (Vanilla JS)│    │  UI unchanged    │  │ UI unchanged      │  │ (Flask + Alpine)│
│ UI unchanged│    │  swap data layer │  │ swap data layer   │  │ UI unchanged    │
│ swap data   │    └──────────────────┘  └───────────────────┘  │ swap data layer │
│   layer     │                                                  └─────────────────┘
└─────────────┘

The data-layer swap inside each old app is surgical:

• ABtest-dashboard/js/storage.js — Supabase SDK → fetch('/api/v1/abtests/...')
• Product-Audits/src/data/*.json reads → fetch('/api/v1/audit-issues?business=televika')
• product-decisions/site/src/proposal/store.ts localStorage → fetch('/api/v1/pm-tasks')
• Televika/src/web/app.py load_data() + _filter_rows() → requests.get('http://galaxy/api/v1/comments?...')

UI components, routes, styling, statistical math, scrapers — unchanged.

---

Recommended stack (post-research, ~200 sources)

Layer	Choice	Why (citations under each layer in "Research findings")
Database	PostgreSQL 17 (not 16) + pgvector + pg_trgm + custom Persian Hunspell dict	PG17 vacuum memory −20×, WAL throughput 2×, partition-wise aggregate pushdown (~70% less inter-partition data); free upgrade path for 5+ years
Comment search	Hybrid: tsvector with 'simple' for FA + generated column for EN english config, GIN per partition; pg_trgm for fuzzy name search only	Persian/Farsi has no built-in PG dictionary — Hunspell pack is required; trigram alone underperforms on complex script + diacritics
Backups	pgBackRest sidecar container → S3-compatible (Backblaze B2 ≈ $5/TB/mo) + on-VPS second volume for hot WAL	pg_basebackup alone is unsuitable for production; pgBackRest gives parallel + incremental + PITR + S3 + 5-year retention
Embeddings	pgvector with HNSW (m=16, ef_construction=128), 1536 dim, on comment_insights only — NOT on raw comments	HNSW is 2026 default; ef_search tuned per recall target; CREATE INDEX CONCURRENTLY required in production
App / API	Next.js 15 App Router for admin UI + Hono routes embedded (/api/v1/*) — single deployable	Matches existing React/TS team skills; Hono adds 5KB/50ms vs Next route handlers; built-in Zod-OpenAPI generates spec from schemas
ORM	Drizzle ORM + drizzle-kit	33KB vs Prisma's 800KB engine; SQL-first; readable migrations; long-term maintainable; drizzle-kit check in CI catches drift; never push in production
Auth (humans)	Better Auth (Lucia is deprecated as of March 2025) + organizations + RBAC plugin	Lucia maintainer officially sunset the library; Better Auth has built-in organizations/RBAC/passkeys; first-class Drizzle adapter; Argon2id for password hashing
Auth (M2M, per-app)	OAuth 2.0 Client Credentials Flow issuing short-lived (15 min) scoped JWTs + 7-day refresh	Modern M2M pattern; scoped to one or more businesses per app; revocable; auditable; replaces "long-lived API key" pattern
Share links	Custom: 16-char URL-safe token (~96 bits) + optional Argon2id (m=19 MiB, t=2, p=1) password + optional expiry + scopes jsonb + access log	OWASP/NIST 2026 recommend Argon2id over bcrypt; Notion has no native password-protected links (we are a differentiator)
API style	REST + OpenAPI 3.1 (Zod schemas → spec → generated TS + Python clients)	tRPC doesn't work for Flask + vanilla JS polyglot clients; REST is the only universally-supported choice
MCP server	Separate Node process, @modelcontextprotocol/sdk, Streamable HTTP transport (2026-07-28 spec — stateless), narrow split tools, audit log with cryptographic integrity, dedicated mcp_app Postgres role	Avoids archived @modelcontextprotocol/server-postgres auth bypass; stateless lets load-balancers route on Mcp-Method/Mcp-Name headers
Audit log	Append-only table with hash-chained rows (each row hashes prior row + own payload)	Audit records need cryptographic integrity to be defensible (SOC2 evidence pattern)
Reverse proxy	Caddy 2	Automatic Let's Encrypt; trivial config; ships in single binary; well-suited for single-VPS deploys
Deploy	Docker Compose on Hetzner/OVH VPS from day 1, NOT Cloudflare-first	Cloudflare Containers GA'd April 2026 but explicitly NOT for Postgres (ephemeral, cold-start, no persistence); Postgres has to live on a VPS anyway, so the "Cloudflare today → VPS later" split adds zero value
Process manager	Docker Compose with restart: unless-stopped + health checks; Watchtower for image auto-update OR Haloy for explicit deploys	Coolify (web UI) had Jan 2026 plaintext-secret CVE; Kamal is Rails-flavored; raw Compose is the most boring and most-future-proof choice
Observability	OpenTelemetry sidecar → self-hosted Grafana + Loki on same Compose	No external dependency; lightweight; covers logs + traces + metrics

Hosting verdict — single VPS from day 1. Earlier in this session you said "Cloudflare for now, maybe single VPS for future." Research changed the call: Cloudflare Containers can't host Postgres (ephemeral runtime by design), so Postgres needs a VPS regardless. Running everything on one VPS from day 1 is simpler than maintaining two deploy topologies. Hetzner ax-line at ~€20/mo (4 vCPU / 16 GB / 512 GB NVMe) is plenty for 5+ years of growth at the comment-volume implied by the data audit.

---

Core schema

Shared dimensions (Galaxy-owned)

businesses          (id, slug UNIQUE, name, name_fa, kind ['primary'|'competitor'],
                     parent_business_id NULL, country, active, created_at)
                    -- seed: televika, filimo, aparat, forsi, namava, gapfilm,
                    --       filmnet, sheyda, netflix, primevideo, disneyplus, ...
                    -- new businesses added at runtime via admin UI

users               (id, email UNIQUE, name, password_hash ARGON2ID,
                     system_role, created_at)
                    -- managed by Better Auth core table

organizations       (id, slug UNIQUE, name)  -- Better Auth org plugin
                    -- one org = one business OR one team-spanning-businesses

user_organizations  (user_id, organization_id, role)  -- Better Auth membership

user_businesses     (user_id, business_id, role ['owner'|'pm'|'analyst'|'viewer'])
                    -- finer-grained than org: user → many businesses

oauth_clients       (id, name, client_id UNIQUE, client_secret_hash,
                     allowed_scopes text[], allowed_businesses int[],
                     created_at, last_used_at, revoked_at NULL)
                    -- one per old app (abtest-dashboard, product-audits, etc.)
                    -- issues short-lived JWTs via Client Credentials Flow

taxonomies          (id, kind ['journey'|'area'|'goal'|'theme'|'issue_type'|
                     'platform'|'owner_team'|'phase'], slug, name, name_fa,
                     business_id NULL, parent_id NULL, sort_order)

share_links         (id, entity_kind, entity_id, token UNIQUE,
                     password_hash NULL,  -- Argon2id (m=19456, t=2, p=1)
                     expires_at NULL, scopes jsonb, max_uses NULL, use_count,
                     created_by, created_at, revoked_at NULL)
share_access_log    (id, share_link_id, accessed_at, ip, user_agent,
                     password_ok BOOLEAN, hash_chain TEXT)

entity_links        (id, from_kind, from_id, to_kind, to_id,
                     link_type ['validates'|'blocks'|'derives_from'|
                                'duplicates'|'related_to'],
                     created_by, created_at)

audit_log           (id, entity_kind, entity_id, field, before, after,
                     actor_user_id NULL, actor_client_id NULL,
                     source ['ui'|'api'|'mcp'|'importer'], at,
                     prev_hash TEXT, row_hash TEXT)  -- hash-chained for integrity

Per-domain tables (Galaxy hosts; old IDs preserved as legacy_id)

-- ABtest-dashboard domain
abtests             (id, legacy_id UUID, business_id FK, name, hypothesis,
                     status ['running'|'concluded'|'archived'],
                     traffic_split, test_type, period_days,
                     start_date, end_date, inputs jsonb, metrics jsonb,
                     analysis_md, screenshots jsonb,
                     validates_audit_issue_id FK NULL,
                     linked_pm_task_id FK NULL,
                     created_at, created_by)

-- Product-Audits domain
audit_issues        (id, legacy_id, business_id, display_id TEXT UNIQUE,
                     title, title_fa, priority, type_id FK, journey_id FK,
                     status ['to_do'|'in_progress'|'done'|'archived'],
                     platforms jsonb, impact int, effort int, evidence jsonb,
                     owner_team_id FK, phase_id FK,
                     problem, problem_fa, solution, solution_fa,
                     created_at, created_by)
audit_pages         (id, legacy_id, business_id, name, name_fa, url, captures jsonb)
audit_walkthroughs  (id, business_id FK, flow_slug TEXT,  -- 'payment'|'player'|'login'|...
                     viewport TEXT,                         -- 'desktop'|'mobile'
                     step_number INT, screenshot_url TEXT, caption TEXT NULL,
                     captured_at, captured_by)
                    -- UNIQUE(business_id, flow_slug, viewport, step_number)
audit_health_runs   (id, business_id, ran_at, trigger, summary jsonb)
audit_health_tests  (id, run_id FK, name, title, title_fa, status,
                     duration_ms, error, screenshot, viewports jsonb)
ai_insights         (id, business_id, kind, generated_at, model,
                     title, title_fa, summary, summary_fa,
                     evidence jsonb, impact, recommendation,
                     embedding vector(1536) NULL)

-- product-decisions domain
pm_tasks            (id, legacy_id TEXT,   -- W-001 / PM-001 → re-displayed TVK-PM-001
                     business_id, goal_id FK, area_id FK,
                     initiative, initiative_fa, task, task_fa,
                     owner_team_id FK,
                     status ['active'|'waiting'|'done'],
                     priority, journey_id FK,
                     start_date, due_date, done_date,
                     blocked_by_id FK NULL,
                     merged_into_id FK NULL,  -- supports W-018 → W-100 merge semantics
                     deleted_at TIMESTAMP NULL,  -- soft-delete like W-012; never hard-delete
                     notes, phase_id FK,
                     created_by, created_at)
pm_pipeline         (id, legacy_id, business_id, goal_id FK, area_id FK,
                     initiative, initiative_fa, proposed_solution,
                     stage ['raw'|'researched'|'validated'|'ready'|'active'],
                     owner_team_id FK, source, priority, impact, effort,
                     evidence, journey_id FK, created_by, created_at)

-- Comments + insights (largest table, PARTITION BY RANGE on date yearly)
comments            (id BIGSERIAL, legacy_id TEXT,   -- review_00001
                     business_id,                     -- platform reviewed
                     primary_business_id,             -- our brand whose ecosystem
                     source ['app_store'|'google_play'|'cafebazaar'|...],
                     rating, text, lang,
                     text_fts_en tsvector GENERATED ALWAYS AS
                       (to_tsvector('english', CASE WHEN lang='en'
                                                    THEN text ELSE '' END)) STORED,
                     text_fts_fa tsvector GENERATED ALWAYS AS
                       (to_tsvector('persian_hunspell', CASE WHEN lang='fa'
                                                              THEN text ELSE '' END)) STORED,
                     date, app_version, device, developer_reply,
                     sentiment, theme_id FK, area_id FK, confidence,
                     raw jsonb,  -- preserves any field the importer doesn't model
                     ingested_at)
                    -- PARTITION BY RANGE (date) yearly
                    -- INDEX GIN(text_fts_en), GIN(text_fts_fa), btree(business_id, date)
                    -- INDEX gin(text gin_trgm_ops) WHERE date > now() - '90 days'  -- fuzzy on recent only
comment_insights    (id, business_id, kind ['executive_summary'|'opportunity'],
                     generated_at, model, sample_size,
                     title, title_fa, recommendation,
                     evidence_quotes jsonb, stats jsonb,
                     embedding vector(1536))  -- HNSW index for semantic MCP search

doc_pages           (id, business_id NULL, slug, title, title_fa,
                     content_md, content_md_fa, updated_at, updated_by)
                    -- legacy/replaced by galaxy-docs notes tables below

-- galaxy-docs domain (5th product — Obsidian-compatible documentation system)
notes               (id, business_id NULL,  -- NULL = cross-business / org-wide note
                     slug TEXT,              -- URL-safe, doubles as wikilink target
                     title TEXT,             -- humanized; also indexed for wikilink resolution
                     title_fa TEXT NULL,     -- bilingual support, optional
                     aliases TEXT[],         -- additional names a wikilink may resolve to
                     frontmatter jsonb,      -- parsed YAML frontmatter (queryable)
                     content_md TEXT,        -- raw markdown body
                     content_html_cache TEXT, -- rendered HTML (regen on save)
                     content_fts tsvector GENERATED ALWAYS AS
                       (setweight(to_tsvector('english', coalesce(title,'')), 'A')
                        || setweight(to_tsvector('english', coalesce(content_md,'')), 'B')
                       ) STORED,
                     content_fts_fa tsvector GENERATED ALWAYS AS
                       (setweight(to_tsvector('persian_hunspell', coalesce(title_fa,'')), 'A')
                        || setweight(to_tsvector('persian_hunspell', coalesce(content_md,'')), 'B')
                        WHERE frontmatter->>'lang' = 'fa') STORED,
                     folder TEXT,            -- virtual folder path for Obsidian-style organization
                     archived BOOLEAN DEFAULT false,
                     created_by, created_at, updated_by, updated_at)
                    -- UNIQUE(business_id, slug) WHERE archived=false
                    -- INDEX GIN(content_fts), GIN(content_fts_fa), GIN(frontmatter jsonb_path_ops)

note_versions       (id, note_id FK, version INT, content_md TEXT,
                     frontmatter jsonb, edited_by, edited_at, commit_message TEXT)
                    -- append-only history; keeps every save (Obsidian-style time machine)

tags                (id, business_id NULL,   -- NULL = global tag
                     slug TEXT,               -- "engineering/backend"
                     name TEXT,
                     name_fa TEXT NULL,
                     parent_tag_id FK NULL,   -- hierarchical: #engineering/backend
                     color TEXT NULL,
                     description TEXT NULL,
                     created_by, created_at)

note_tags           (note_id FK, tag_id FK, PRIMARY KEY (note_id, tag_id))

note_attachments    (id, note_id FK, filename, mime_type, size_bytes,
                     storage_key TEXT,        -- relative path under attachments/
                     created_by, created_at)

-- wikilinks + cross-domain links reuse the shared `entity_links` table:
--   from_kind='note', from_id=note.id,
--   to_kind='note'|'audit_issue'|'pm_task'|'abtest'|'comment_insight'|...
--   link_type='wikilink'|'embed'|'related_to'|...
-- backlinks = SELECT ... FROM entity_links WHERE to_kind='note' AND to_id=?

Hybrid JSONB pattern (from research anti-patterns):

• Fixed/queried fields → typed columns (so they get indexed, FK-checked, FTS-extracted)
• Variable/optional fields → raw jsonb for forward-compatibility with future scraper formats
• Generated columns extract specific JSONB paths into typed columns when needed (PG12+ feature, fully supported in PG17)

Status enums stay per domain (semantics differ). A SQL view unified_work_state rolls them up for cross-domain MCP queries.

Display ID convention (globally unique across domains, human-friendly):

• TVK-AI-001 (audit issue), TVK-AB-001 (abtest), TVK-PM-001 (pm task), TVK-PL-001 (pipeline), TVK-CM-000001 (comment derived). Existing PM-001 namespace re-displays as TVK-PM-001; mapping table preserves the original.

---

REST API contract (v1)

Versioned at /api/v1. Auth via OAuth 2.0 Client Credentials per old app: app exchanges client_id + client_secret at /oauth/token for a 15-min scoped JWT, refreshes via 7-day refresh token. All list endpoints accept ?business=televika; JWTs are business-scoped so cross-business reads are 403.

# Shared dims (read by every app's init)
GET    /api/v1/businesses
GET    /api/v1/taxonomies?kind=journey&business=televika

# ABtest domain
GET    /api/v1/abtests?business=televika&status=running
POST   /api/v1/abtests
GET    /api/v1/abtests/{display_id}
PATCH  /api/v1/abtests/{display_id}
DELETE /api/v1/abtests/{display_id}
POST   /api/v1/abtests/{display_id}/screenshots   (multipart → presigned URL)

# Audit domain
GET    /api/v1/audit-issues?business=televika&status=to_do&journey=playback
POST   /api/v1/audit-issues
GET    /api/v1/audit-issues/{display_id}
PATCH  /api/v1/audit-issues/{display_id}
GET    /api/v1/audit-pages?business=televika
GET    /api/v1/audit-health/runs?business=televika
GET    /api/v1/audit-health/runs/{id}
POST   /api/v1/audit-health/runs                 (called by Playwright workflow)
GET    /api/v1/ai-insights?business=televika
# New since 2026-05-20 audit (Product-Audits walkthrough modal feature)
GET    /api/v1/audit-walkthroughs?business=televika&flow=payment
POST   /api/v1/audit-walkthroughs/bulk           (Playwright workflow inserts captures)
GET    /api/v1/audit-walkthroughs/{business}/{flow}/{viewport}/{step}

# PM domain
GET    /api/v1/pm-tasks?business=televika
POST   /api/v1/pm-tasks
PATCH  /api/v1/pm-tasks/{display_id}
GET    /api/v1/pm-pipeline?business=televika
POST   /api/v1/pm-pipeline
PATCH  /api/v1/pm-pipeline/{display_id}

# Comments domain
GET    /api/v1/comments?business=filimo&theme=ads&from=2026-01-01&q=login&lang=fa
POST   /api/v1/comments/bulk                     (scraper inserts, batch)
GET    /api/v1/comment-insights?business=televika
POST   /api/v1/comment-insights/regenerate       (async job, returns job_id)

# Cross-domain
GET    /api/v1/entity-links/{display_id}
POST   /api/v1/entity-links
GET    /api/v1/audit-log?entity={display_id}

# galaxy-docs domain (notes / tags / attachments / search / Obsidian-compat)
GET    /api/v1/notes?business=televika&tag=engineering&folder=adr&q=playback
POST   /api/v1/notes                              { slug, title, title_fa?, content_md, frontmatter? }
GET    /api/v1/notes/{slug}?business=televika    (or by id)
PATCH  /api/v1/notes/{slug}                      (versioned: writes note_versions row)
DELETE /api/v1/notes/{slug}                      (soft-delete: archived=true)
GET    /api/v1/notes/{slug}/versions             (history)
GET    /api/v1/notes/{slug}/backlinks            (all notes/entities linking IN)
GET    /api/v1/notes/{slug}/outlinks             (everything this note links to)
GET    /api/v1/notes/search?q=…&lang=en|fa       (FTS across notes)
POST   /api/v1/notes/{slug}/attachments          (multipart)
GET    /api/v1/tags?business=televika
POST   /api/v1/tags
GET    /api/v1/tags/{slug}/notes                 (all notes with this tag)

# Obsidian-compatibility surface
GET    /api/v1/notes/vault/export?business=televika   (zip of .md files + attachments, vault-shaped)
POST   /api/v1/notes/vault/import                      (multipart zip; idempotent upsert by slug)
GET    /api/v1/notes/local-rest-api/                   (Obsidian Local REST API-compatible subset)

# Sharing
POST   /api/v1/share-links                       { entity_kind, entity_id, password?, expires_at?, max_uses? }
GET    /api/v1/share-links/{token}/resolve       (used by /s/[token] route)
DELETE /api/v1/share-links/{token}

# OAuth (one per app)
POST   /oauth/token                              (client_credentials + refresh_token grants)

OpenAPI 3.1 spec auto-generated from Drizzle schemas via Zod (zod-openapi). Published at /api/v1/openapi.json. Per-language clients:

• TypeScript: openapi-zod-client → @galaxy/audits-client, @galaxy/pm-client, @galaxy/abtests-client
• Python: openapi-python-client → galaxy_comments package for Televika team

---

galaxy-docs — the 5th product (Obsidian-compatible docs system)

Unlike the 4 legacy apps, galaxy-docs is a new product built inside Galaxy. There's no legacy UI to preserve — Galaxy ships the UI, the API, the MCP tools, and the Obsidian-compatibility layer. It uses the same Postgres, the same Better Auth, the same per-business RBAC, the same share_links system, the same entity_links table, and the same audit_log hash chain as the other domains.

Why it lives here: research, ADRs, runbooks, PRDs, meeting notes, and how-to docs are all cross-cutting. Today they're scattered across people's Obsidian vaults, Notion pages, and stray markdown in repos. Centralizing them in Galaxy gives them: per-business scoping, cross-domain entity-linking (an ADR can [[link to]] an audit issue or a PM task or an A/B test), MCP-driven semantic + FTS retrieval for Claude, and Obsidian-compatible export so writers keep their preferred editor.

Features (v1)

Feature	Implementation
Plain-text Markdown notes	notes.content_md (no proprietary format)
YAML frontmatter, queryable	notes.frontmatter jsonb + GIN(jsonb_path_ops); UI surfaces frontmatter as editable properties panel
Wikilinks [[Slug]] and aliased `[[Slug	Display]]`
Cross-domain wikilinks [[issue:TVK-AI-008]], [[task:TVK-PM-050]], [[abtest:TVK-AB-001]], [[insight:42]]	Custom prefix syntax; parser routes to right to_kind in entity_links
Backlinks panel	SQL: SELECT n.* FROM notes n JOIN entity_links e ON e.from_kind='note' AND e.from_id=n.id WHERE e.to_kind='note' AND e.to_id=?
Tags #engineering/backend (hierarchical)	Tags table + note_tags junction; tag tree rendered as nested list
Embeds ![[note]] (transcludes another note's content)	Render-time resolution via the same wikilink resolver
Aliases	notes.aliases TEXT[] participates in wikilink resolution
Folder organization (virtual)	notes.folder string (e.g. engineering/playback/adr); UI renders as tree
Version history	note_versions append-only; every save writes a row with optional commit_message; "diff vs version N" view
Bilingual (EN + FA)	title_fa + dual generated FTS columns + frontmatter lang: fa|en toggles which tsvector indexes that note
Tasks - [ ] in body	Frontmatter-promoted via parser: extracts task lines → frontmatter tasks: [{text, done, line}] for query
Attachments	note_attachments table + local disk storage under /data/attachments/{business_slug}/{note_slug}/ (or S3 if preferred)
Per-business scoping	notes.business_id (NULL = org-wide); RBAC checks against user_businesses
Per-note sharing	share_links polymorphic (entity_kind='note', entity_id=N); Argon2id password + expiry + max_uses + access log
Full-text search EN + FA	Generated content_fts + content_fts_fa columns + GIN; per-language tsquery routing
Semantic search (Claude/MCP)	Optional: embed each note via the same model used for comment_insights; pgvector HNSW on note_embedding vector(1536) (deferred to v1.5 if cost prohibitive)
Graph view	Web UI: D3/Reaflow visualization of entity_links subgraph for the current note's N-hop neighborhood

Obsidian compatibility (sync / interop, NOT replacement)

Strategy: galaxy-docs is the source of truth; Obsidian acts as an optional offline editor.

Surface	Behavior
Vault export	GET /api/v1/notes/vault/export?business=televika returns a zip whose layout matches an Obsidian vault: <folder>/<slug>.md with YAML frontmatter + wikilinks preserved + _attachments/ subfolder. Opens directly in Obsidian.
Vault import	POST /api/v1/notes/vault/import (multipart zip). Idempotent upsert on slug. Frontmatter id: field carries Galaxy note ID for round-trip.
Local REST API plugin shim	Galaxy implements a subset of the Obsidian Local REST API endpoint surface at /api/v1/notes/local-rest-api/*. Users install that Obsidian plugin pointed at Galaxy → notes round-trip live from Obsidian app to Galaxy.
Custom Obsidian plugin (optional, Phase 6)	A first-party Obsidian plugin authored by us: shows Galaxy notes in the Obsidian file tree, two-way sync, surfaces Galaxy backlinks (incl. cross-domain), exposes Galaxy share links from Obsidian command palette. Ships to the Obsidian Community Plugins registry.

We are not trying to replicate the entire Obsidian feature set inside Galaxy's web UI (no graph view physics, no canvas, no Dataview). The web UI is good enough for most editing; power users use Obsidian against the API.

Galaxy-docs has TWO surfaces

1. Authoring surface — admin editor at /admin/notes/ (inside Galaxy Next.js app)

• Tree view (folder + tag facets)
• Markdown editor (CodeMirror 6 + remark plugins for wikilinks, tags, frontmatter)
• Live preview pane
• Backlinks panel (right rail)
• Properties panel (frontmatter as form fields)
• History sidebar (versions)
• "New note from template" (templates stored as notes tagged #template)
• "Link to entity" picker (search across all domains, inserts [[issue:TVK-AI-008]] etc.)
• Share button (creates share_link for the note)

2. Reading surface — dedicated docs site apps/docs/ built with Astro 6 + Starlight

The authoring UI is good for writing; for reading + browsing + searching long docs, a dedicated documentation site is the right tool. Built with:

• Astro 6 + Starlight theme — static-first, zero JS by default, ships Lighthouse 100s out of the box
• Live content collections (src/live.config.ts) fetch notes from Galaxy's /api/v1/notes at request-time — no stale builds when a note changes
• Build-time pre-render of all notes for fastest first-paint; live collections handle delta updates (ISR-style via on-demand cache headers)
• Starlight features for free: sidebar nav (driven by notes.folder tree), table of contents per page, full-text search (client-side index built at build time + live FTS query passthrough to Galaxy for >50K notes), dark mode, i18n (EN + FA + RTL), responsive
• Wikilinks resolved server-side by Galaxy → arrive as already-resolved <a href="/notes/<slug>"> links in the rendered HTML
• Cross-domain wikilinks ([[issue:TVK-AI-008]]) render with deep-links: clicking opens the issue in Product-Audits (or in galaxy-docs's "linked issue preview" card)
• Backlinks panel rendered server-side from entity_links reverse query
• Auth: per-business RBAC enforced via JWT (Better Auth issues a reader JWT for logged-in users). Public/password-shared notes accessible via /s/{token} (using Galaxy's existing share-link system).
• Graph view: optional Phase 4.5b — D3 force-directed graph rendered as a Starlight custom component, queries entity_links for the local N-hop subgraph
• Hosted at: docs.galaxy.example.com (separate subdomain so docs are reachable independently of the admin shell)

docs.galaxy.example.com (Astro + Starlight)
       │ fetch notes + links at build + on-demand
       ▼
galaxy.example.com/api/v1/notes (Galaxy Hono routes)
       │ scoped by JWT business
       ▼
postgres (notes + entity_links + tags + share_links + share_access_log)

Why Astro over Docusaurus: Astro 6 has stable live content collections (March 2026) — Docusaurus has no equivalent, requires full rebuild on every change. Starlight ships zero JS by default; Docusaurus ships React hydration. Astro Tailwind integration is trivial (matches our stack); Docusaurus's Infima CSS is tightly coupled. Wikilinks via remark-wiki-link work in both (the same lib we use in Galaxy server-side parsing).

Auth + permissions

Same Better Auth + per-business RBAC as the rest of Galaxy. A note's business_id controls visibility; a user with viewer role on Televika can read but not edit Televika notes; pm can edit; org-wide notes (business_id=NULL) visible to all users with any business membership.

---

Admin UI scope (Galaxy's only UI)

Lives at /admin. Handles only what no single old app owns:

• Businesses: CRUD. Add new business at runtime → appears in all old apps via next API fetch.
• Users + RBAC: invite, assign role per business (managed by Better Auth org plugin)
• OAuth clients: issue/rotate/revoke per-app credentials with scopes + business list
• Taxonomies: edit shared dims (journeys, areas, themes, owner teams, phases)
• Share links: list all active tokens, revoke, view access log
• Audit log: filter by entity / user / source; verify hash chain integrity
• Importer / sync status: dashboards for each domain importer's last run, row counts, errors

No issue list, PM board, comment browser, or A/B test detail page — those live in the original apps.

---

MCP server scope

Separate Node process under /mcp. Connects to Postgres via dedicated role mcp_app (SELECT + INSERT + UPDATE on per-domain tables; no DROP, no DDL, no DELETE). Streamable HTTP transport (2026-07-28 spec, stateless). Identity passthrough: per-call JWT identifies the human user behind the LLM call.

Read tools (v1):

• list_businesses, get_business(slug)
• search_issues(business, status?, query?, limit), get_issue(display_id), list_issues_by_journey
• search_comments_fts(business, query, lang, date_from?, date_to?, sentiment?) — uses lang to route to text_fts_en vs text_fts_fa index
• search_insights_semantic(business, query, limit) — pgvector HNSW on comment_insights.embedding
• list_abtests(business, status?), get_abtest_results(display_id)
• search_pm_tasks(business, status?, owner?), get_pm_task(display_id)
• get_entity_links(display_id) — cross-domain
• get_audit_history(display_id) — read from hash-chained audit_log

Write tools (v1, all attributed + audit-logged):

• create_audit_issue, update_audit_issue_status
• create_pm_task, update_pm_task_status, link_task_to_issue
• create_share_link, revoke_share_link
• add_entity_link

galaxy-docs tools (read + write):

• notes_search(business, query, lang?, tag?, folder?, limit) — FTS across notes
• notes_get(slug, business?) — read full note incl. frontmatter + outlinks + backlinks
• notes_list_by_tag(tag_slug, business?)
• notes_create(business?, slug, title, title_fa?, content_md, frontmatter?, tags?) — wikilinks in content_md auto-parsed → entity_links rows
• notes_update(slug, content_md?, frontmatter?, tags?, commit_message?) — writes a note_versions row
• notes_link_to_entity(note_slug, entity_kind, entity_id, link_type) — explicit cross-domain link
• notes_get_backlinks(slug) — reverse-direction entity_links query
• notes_history(slug, limit) — version history
• tags_create(business?, slug, name, parent?) — new tag
• tags_list(business?, prefix?)

Out of scope (per research — single flexible tools make audit logs uninterpretable):

• No sql_query tool
• No DDL / DELETE / bulk writes
• No raw comments insert (scraper service only writer)

Per-tool guards:

• Each tool declares scope intent in its description (read/write, business required, side effects)
• Each write tool requires confirm: true parameter from caller for destructive-by-omission patterns
• Rate limited per JWT (60 reads/min, 10 writes/min)
• Every write writes an audit_log row with source='mcp' and the LLM-attributed user ID

Transport: behind Caddy + Bearer JWT + per-user rate limit.

---

Sharing (per-entity public/password links)

Every entity in any domain shareable from its source app via POST /api/v1/share-links. Resolver lives in Galaxy.

• share_links table polymorphic (entity_kind + entity_id)
• Token: 16-char URL-safe (~96 bits entropy)
• Password (optional): Argon2id (m=19456 KiB, t=2, p=1) per OWASP 2026 baseline
• Expiry (optional) + max_uses (optional, supports one-time links)
• Route: https://galaxy.example.com/s/[token] → if password set, prompt → render entity in read-only "share view"
• Every access writes share_access_log (when, IP, UA, password_ok) hash-chained to prior row
• Old apps can deep-link back to themselves for prettier rendering (?return_to=...); default is Galaxy's minimal share view so the link works even if the source app is down

This is a differentiator: research found Notion has no native password-protected links (only via third-party tools). The implementation pattern: Token → DB lookup → password verify → entity render is straightforward.

---

Minimum-change contract per product

This is the surgical-change contract Galaxy honors for each of the 4 apps. Anything outside the "changes" column is out of scope for the data-layer swap PR and will be rejected at review. The functionality + data + content of each app is preserved 1:1; only the persistence backend moves.

Product	What changes (data layer only)	What does NOT change	LOC estimate	3 verification agents (all must pass)
ABtest-dashboard	js/storage.js (Supabase SDK → fetch); add js/galaxy-auth.js for OAuth token refresh; remove js/supabase-config.js	js/app.js (UI), js/calculator.js (z-test math), index.html, test.html, detail-of-test-dashbboard.html, css/style.css, js/charts.js	~+200 / −150 net	abtest-functionality · abtest-data-parity · abtest-ui-parity
Product-Audits	src/data/*.json static imports replaced with @galaxy/audits-client calls (likely via existing hooks in src/hooks/ or page-level loaders); api/ serverless functions either delete or thin-proxy to Galaxy; AI insight skill writes via API instead of committing JSON; Playwright workflow POSTs run results to API	src/pages/*.jsx (UI), src/components/* (modals, charts, layout), src/lib/lang.tsx (bilingual), tailwind.config.js, RTL CSS, image assets, vercel.json for the SPA shell	~+400 / −300 net (mostly hook swaps)	audits-functionality · audits-data-parity · audits-content-parity
product-decisions	site/src/proposal/store.ts (localStorage overlay → @galaxy/pm-client); JSON loaders in site/src/data/ replaced with API fetch; remove hardcoded pms1405 credential	site/src/proposal/Now.tsx, Map.tsx, Pace.tsx, Intake.tsx (UIs), site/src/lib/lang.tsx (bilingual), site/src/lib/health.ts, status chips, RTL handling, Tailwind config	~+250 / −200 net	pm-functionality · pm-data-parity · pm-cross-link-parity
Televika Comment Analysis	src/web/app.py load_data() + _filter_rows() + _filter_filimo_rows() + all /api/* Flask handlers proxy to Galaxy via galaxy_comments; scrapers (src/scrape/*.py) json.dump → client.comments.bulk_insert; src/analyze/insights.py output step writes via API	src/web/templates/*.html (Jinja UIs for all 4 tabs), src/web/static/app.js (Alpine UI), src/web/static/style.css, scraper logic itself, insight rule engine, deep-translator + ZWNJ handling, Vercel deployment config	~+350 / −400 net	comments-functionality · comments-data-parity · comments-insights-parity

Cutover gate per product: the swap PR is reviewable, mergeable, and deployable ONLY when (a) the diff stays within the "changes" column above, (b) all 3 verification agents return green on staging pointed at Galaxy, and (c) a 7-day shadow-traffic period elapses with no incident reports from the product team. Production cutover happens with a feature-flag flip (env var USE_GALAXY=true) so rollback is one revert away.

Cross-cutting guarantees Galaxy commits to for every product:

• Every legacy ID is preserved as legacy_id in Galaxy — display IDs in each product's UI continue to read the same as before (e.g., Product-Audits keeps showing "Issue 8" even though Galaxy stores it as TVK-AI-008 internally)
• Every bilingual field (*_fa, *_fa variants) preserved byte-for-byte including ZWNJ and RTL marks
• Every JSONB blob round-trips deep-equal
• Every timestamp preserved in original timezone semantics (Tehran-aware where applicable)
• Every status enum value remains the same string (no case changes, no slugification)
• Importer is idempotent and re-runnable; a second import is a no-op
• Original data files / Supabase tables are not deleted at cutover — they're frozen as read-only archive for ≥90 days post-cutover so any verification-agent regression can compare against ground truth

---

Per-app data-layer swap (the team-by-team work — implementation detail)

1. ABtest-dashboard (Vanilla JS team)

• File to swap: /Users/parhumm/Projects/SabaIdea/product/ABtest-dashboard/js/storage.js
• Today: Supabase SDK calls. After: fetch('/api/v1/abtests/...', { headers: { Authorization: 'Bearer ' + jwt } }) with OAuth client-credentials refresh
• Statistical math in js/calculator.js unchanged
• UI in js/app.js, index.html, test.html unchanged
• Rotate Supabase anon key after cutover (currently leaked); decommission Supabase project
• Deliverable from Galaxy team: drop-in storage.js replacement + OAuth client + sample fetch helpers

2. Product-Audits (React/Vite team)

• Files to swap: any code importing src/data/*.json
• After: useQuery(['audit-issues', business], () => galaxyClient.auditIssues.list({business})) via generated TS client
• AI insight skill (currently commits JSON) → POST /api/v1/ai-insights
• Playwright health workflow → POST /api/v1/audit-health/runs
• UI, routes, bilingual rendering, RTL — unchanged
• Deliverable: @galaxy/audits-client (generated from OpenAPI) + example hooks

3. product-decisions (React/Vite team)

• File to swap: product-decisions/site/src/proposal/store.ts (localStorage)
• After: store reads/writes via @galaxy/pm-client
• Rotate hardcoded pms1405 credential; auth moves to Galaxy via OAuth
• Multi-business support: schema is multi-business from day 1 — existing business selector starts working for Filimo
• UI in site/src/proposal/*.tsx (Now / Map / Pace / Intake) unchanged
• Deliverable: @galaxy/pm-client + replacement store.ts

4. Televika Comment Analysis (Python/Flask team)

• Files to swap: src/web/app.py load_data(), _filter_rows(), _filter_filimo_rows() + all /api/* Flask handlers reading JSON
• After: Flask handlers proxy to Galaxy via galaxy_comments Python client
• Scrapers: src/scrape/*.py json.dump → client.comments.bulk_insert(rows)
• Insight gen: src/analyze/insights.py json.dump('insights.json') → client.comment_insights.regenerate()
• UI in src/web/templates/*.html, src/web/static/app.js unchanged
• Deliverable: galaxy_comments Python package + replacement app.py data module + example scraper migration

---

Cutover phasing

Galaxy's side ships in phases. Each old app's swap follows on its team's schedule once that domain's API is verified.

Phase 0 — Foundation + research + docs cache (2–3 weeks)

• Repo: /Users/parhumm/Projects/SabaIdea/productgalaxy/
• Git remote: git@github.com:parhumm/productgalaxy.git
• First commit seeds: CLAUDE.md (see content below), README.md, .gitignore, package.json, pnpm-workspace.yaml, docker-compose.yml, drizzle.config.ts, tsconfig.json, .env.example, .editorconfig
• Per-tech-layer research agents (8 agents, see "Per-tech-layer research" section) run in parallel and confirm or adjust stack
• Per-tech /jaan-to:dev-docs-fetch runs for every library in the stack (see "Per-tech docs cache" section). Cached docs land in jaan-to/outputs/docs-cache/. Implementation phases reference the cached versions so we don't drift onto outdated APIs.
• Docker Compose: postgres:17 (with pgvector + pg_trgm + Persian Hunspell pack mounted), Next.js app, MCP server, pgBackRest sidecar, Caddy
• Drizzle migrations: shared dims (businesses, users, organizations, user_organizations, user_businesses, oauth_clients, taxonomies, share_links, share_access_log, entity_links, audit_log with hash chain)
• Better Auth wired with Drizzle adapter + Argon2id password hashing + Organizations + RBAC plugins
• Seed: 12+ businesses + canonical taxonomies (sourced from Product-Audits issue enums + product-decisions schema.sql)
• Admin UI: businesses + users + OAuth clients + taxonomies + share links + audit log + importer status
• pgBackRest: WAL archiving on, full backup nightly, differential weekly, S3 target (Backblaze B2)

Phase 1 — Audit-issues API + importer (2 weeks)

• Before anything: git pull Product-Audits + product-decisions + Televika; re-run /jaan-to:detect-product + /jaan-to:detect-dev against fresh HEADs; record snapshot row counts as importer expected values
• Tables: audit_issues, audit_pages, audit_walkthroughs (new since drift check), audit_health_runs, audit_health_tests, ai_insights
• Importer (idempotent, upsert on legacy_id) reads all Product-Audits/src/data/{televika,filimo}/*.json AND walks Product-Audits/public/e2e-walkthroughs/<business>/<flow>/<viewport>/step-NN.png to populate audit_walkthroughs
• REST endpoints + Zod schemas + OpenAPI 3.1 spec (incl. walkthroughs endpoints)
• @galaxy/audits-client published (with walkthroughs API)
• Handoff to Product-Audits team for data-layer swap

Phase 2 — PM tasks API + importer (1–2 weeks)

• Tables: pm_tasks (with new merged_into_id + deleted_at columns from drift check), pm_pipeline
• Importer: master.json + pipeline.json + improvements.json + history.json (history → audit_log)
• Importer must handle: soft-delete semantics (W-012 etc. → deleted_at, not hard-delete), merge semantics (W-018 → W-100 → merged_into_id), parent/children relationships (W-145 with 6 children)
• New "Apps section" taxonomy entry seeded if present in master.json
• PM-001 / W-NNN preserved as legacy_id, namespace as TVK-PM-001
• @galaxy/pm-client published

Phase 3 — ABtest API + importer (1 week)

• Tables: abtests
• Importer reads exported JSON at /Users/parhumm/Downloads/tests_rows.json (provided 2026-05-24). Confirmed schema matches the planned abtests columns: id UUID, name, hypothesis (Persian text), traffic_split, test_type, start_date, end_date, period, inputs (JSON-as-string), metrics (JSON-as-string), analysis_document (HTML from document.execCommand rich-text editor), 5 screenshot/trend URLs, created_at, updated_at, product. Preserves UUIDs as legacy_id; sets business_id from product column lookup.
• Screenshot URLs in the export still point to Supabase Storage (https://orxtyrzmpztcjsocxkka.supabase.co/storage/v1/object/public/screenshots/<uuid>.png). Importer mirrors each PNG into Galaxy's attachment storage at attachments/abtests/{legacy_id}/{kind}.png, then rewrites URLs in abtests.screenshots jsonb to point to Galaxy. After mirroring is verified, the Supabase project + Storage bucket can be deleted entirely.
• analysis_document is HTML — imported verbatim into abtests.analysis_md (no markdown conversion; legacy rendering matches). Phase 3.5 can sanitize / migrate to markdown later if useful.
• inputs and metrics are JSON-encoded strings in the export — importer parses them to jsonb cleanly.
• No live Supabase connection needed — de-risks Phase 3 (no anon-key rotation race, no dependency on legacy infra still being up at import time).
• @galaxy/abtests-client published.

Phase 4 — Comments + insights API + importer (3 weeks, heaviest)

• Tables: comments (PARTITION BY RANGE on date, yearly, with text_fts_en + text_fts_fa generated columns), comment_insights (HNSW pgvector index)
• Streaming importer for comments.json.gz (220K) + filimo_comments.json + insights JSONs
• Persian Hunspell dictionary installed and persian_hunspell text search configuration created
• Embedding generation pipeline for comment_insights (model choice — Phase 0 research decides between OpenAI text-embedding-3-small at $0.02/1M tokens vs local bge-m3 sentence-transformer; embeddings refresh cadence documented)
• REST endpoints + galaxy_comments Python client
• Handoff to Televika team

Phase 4.5 — galaxy-docs MVP (2–3 weeks, in parallel with Phase 4)

• Tables: notes, note_versions, tags, note_tags, note_attachments
• Wikilink parser (remark-wiki-link + custom resolver) integrated into save path
• Cross-domain wikilink syntax ([[issue:TVK-AI-008]]) → entity_links row
• FTS: per-language content_fts + content_fts_fa generated columns + GIN indexes
• REST endpoints + OpenAPI + TS client + Python client
• Web UI: /admin/notes/ tree + CodeMirror 6 editor + live preview + backlinks panel + properties + history + share button + link-to-entity picker
• Seed: import any existing internal docs into Galaxy notes (one-time bulk-import)
• MCP tools: notes_search, notes_get, notes_create, notes_update, etc.

Phase 4.5b — Astro 6 + Starlight docs site (1–2 weeks, parallel with 4.5)

• New app at apps/docs/
• Astro 6 + Starlight + Tailwind (matches Galaxy admin styles)
• Live content collection at src/live.config.ts fetches /api/v1/notes (paginated, RBAC-aware via JWT)
• Build-time content collection pre-renders all notes from a snapshot fetch
• Wikilinks rendered as <a> with deep-link for cross-domain ([[issue:TVK-AI-008]] → https://product-audits.example.com/issues/TVK-AI-008)
• Sidebar nav built from notes.folder tree
• Per-business switcher in header (writes selected business to JWT)
• Search: client-side index + server-side FTS passthrough to /api/v1/notes/search
• Bilingual UI (EN + FA), RTL automatic per note's frontmatter.lang
• Hosted at docs.galaxy.example.com (Caddy adds subdomain + Let's Encrypt automatically)
• E2E tests: load a note, click a wikilink → resolves; click a [[issue:…]] cross-domain link → opens Product-Audits in new tab; FTS search returns expected matches; RTL renders correctly for FA notes

Phase 4.6 — galaxy-docs Obsidian compatibility (1 week)

• Vault export endpoint (zip of .md + frontmatter + _attachments/)
• Vault import endpoint (idempotent upsert by frontmatter.id then by slug)
• Obsidian Local REST API plugin-compatible endpoint subset
• Documentation for power users on pointing the Obsidian Local REST API plugin at Galaxy

Phase 6 (optional) — First-party Obsidian plugin

• Custom Obsidian plugin published to Community Plugins registry
• Two-way sync, Galaxy backlinks rendered in Obsidian's backlink pane, share-link command palette
• Defer until v1 of galaxy-docs is stable and user demand justifies it

Phase 5 — MCP server (parallel with 1–4, finalized after Phase 4)

• Read tools land per domain
• Write tools land last, only after that domain's UI swap is verified in production for ≥2 weeks
• Final pass: rate limits, audit log hash-chain verification job, share-link token entropy assertion, narrow-tool review per the 75-point MCP Security Audit checklist

---

Per-tech-layer research agents (Phase 0 — confirm or adjust the stack)

Eight research agents run in parallel. Each runs /jaan-to:pm-research-about for its layer, then commits a brief in jaan-to/outputs/pm-research/<layer>.md with an "adopt as planned / adjust to X / replace with Y" verdict. Phase 1 is hard-blocked until all 8 agents return.

#	Layer	Agent	/jaan-to:pm-research-about prompt	Provisional choice (will validate or adjust)
1	Database	db-research	"PostgreSQL 17 for long-lived self-hosted product-management workload: range-partitioning patterns for 1M+ append-only comment rows, autovacuum tuning per partition, vacuum memory improvements, partition-wise aggregate pushdown, 5-year upgrade path with logical replication, pgvector vs pgvectorscale; Persian (Farsi) full-text search via Hunspell dictionary"	PG17 + pgvector + pg_trgm + custom Hunspell FA
2	App framework	app-research	"Next.js 15 App Router with embedded Hono routes for /api vs pure Hono on Bun vs Remix for self-hosted single-VPS admin + REST API + SSR; OpenAPI auto-generation via zod-openapi; cold start; long-term maintenance burden; standalone Docker build"	Next.js 15 + Hono routes + Zod-OpenAPI
3	ORM	orm-research	"Drizzle vs Kysely vs Prisma for Postgres + TypeScript 2026: migration tooling (drizzle-kit check, never push in prod), bundle size, raw SQL escape hatches, runtime perf, JSONB ergonomics, generated-column support, 5+ year maintenance"	Drizzle ORM + drizzle-kit
4	Auth	auth-research	"Better Auth (post Lucia-deprecation March 2025) vs Auth.js vs Ory Kratos for self-hosted Drizzle-Postgres app: per-business RBAC via Organizations plugin, OAuth 2.0 Client Credentials Flow for per-app M2M tokens, Argon2id password hashing, session management, 5-year maintenance"	Better Auth + Organizations + RBAC plugin
5	API style	api-research	"REST + OpenAPI 3.1 vs GraphQL for shared backend consumed by polyglot clients (Flask, vanilla JS, React, Python scrapers); generated client experience per language via openapi-zod-client and openapi-python-client; versioning strategy; Zod as single source of truth"	REST + Zod-OpenAPI + per-language client gen
6	MCP server	mcp-research	"MCP server 2026-07-28 spec (stateless Streamable HTTP) best practices for production with writes: identity passthrough, narrow-tool patterns, audit log with cryptographic integrity, rate limiting, avoiding archived @modelcontextprotocol/server-postgres auth bypass; OAuth 2.1 token authorization"	Streamable HTTP + narrow split tools + hash-chained audit
7	Sharing	share-research	"Per-entity public + password-protected share links: 96-bit token entropy, Argon2id m=19456 t=2 p=1 password gate, expiry + max_uses + revocation patterns, anti-abuse (rate-limit, access log, hash chain), one-time vs reusable tokens; Notion/Linear/Figma comparison and gaps to close"	16-char token + Argon2id + scopes jsonb + hash-chained access log
8	Deploy	deploy-research	"Docker Compose vs Haloy vs Kamal vs Coolify on single Hetzner VPS for 5+ year-lived 4-service stack (postgres + app + mcp + caddy + pgBackRest sidecar): zero-downtime deploys, secret management (Coolify Jan 2026 CVE), pgBackRest S3 archival, observability (OTel + Grafana + Loki on-VPS)"	Docker Compose + Haloy CLI deploys + pgBackRest + Caddy + OTel/Grafana/Loki
9	galaxy-docs	docs-research	"Obsidian-compatible self-hosted docs system 2026: Markdown + YAML frontmatter storage in Postgres; wikilinks parser (remark-wiki-link vs markdown-it-wikilinks vs micromark-extension-wiki-link); bidirectional backlinks via entity_links table; hierarchical tags; Obsidian Local REST API plugin compatibility; vault export/import; Docmost reference architecture; CodeMirror 6 editor; CRDT collaborative editing tradeoffs; semantic search via pgvector on note embeddings"	Parser library (remark-wiki-link likely), editor (CodeMirror 6 + remark), Obsidian sync surface, optional first-party Obsidian plugin

If any agent recommends replacing a layer, the plan is updated and re-reviewed before Phase 1 starts.

---

Per-tech docs cache (Phase 0 — /jaan-to:dev-docs-fetch per library)

Run /jaan-to:dev-docs-fetch for every library in the chosen stack. Skill resolves the library via Context7 MCP and caches authoritative current docs locally so implementation phases reference today's API, not training-data snapshots. Outputs in jaan-to/outputs/docs-cache/<library>/.

#	Library	/jaan-to:dev-docs-fetch invocation	Used in
1	Next.js 15 (App Router)	/jaan-to:dev-docs-fetch "Next.js 15 App Router server actions standalone output"	Galaxy admin UI + API host
2	Hono	/jaan-to:dev-docs-fetch "Hono 4 routes Next.js integration zod-openapi middleware"	/api/v1/* routes
3	Drizzle ORM	/jaan-to:dev-docs-fetch "Drizzle ORM Postgres schema relations transactions partitioning generated columns"	All persistence
4	drizzle-kit	/jaan-to:dev-docs-fetch "drizzle-kit generate migrate check production workflow"	Migrations
5	Better Auth core	/jaan-to:dev-docs-fetch "Better Auth Drizzle adapter Postgres email password Argon2id sessions"	Human auth
6	Better Auth Organizations plugin	/jaan-to:dev-docs-fetch "Better Auth organizations plugin RBAC roles permissions multi-tenant"	Per-business membership
7	Better Auth OAuth Provider plugin	/jaan-to:dev-docs-fetch "Better Auth OAuth provider client credentials JWT issuance scopes"	Per-app M2M tokens
8	Zod	/jaan-to:dev-docs-fetch "Zod v4 schema validation discriminated unions transforms"	Request/response schemas
9	zod-openapi	/jaan-to:dev-docs-fetch "zod-openapi OpenAPI 3.1 generation from Zod schemas Hono integration"	OpenAPI generation
10	openapi-zod-client	/jaan-to:dev-docs-fetch "openapi-zod-client generate zodios TypeScript client from OpenAPI spec"	TS clients (audits/pm/abtests)
11	openapi-python-client	/jaan-to:dev-docs-fetch "openapi-python-client generate Python client from OpenAPI 3.1 spec"	Python client (galaxy_comments)
12	@modelcontextprotocol/sdk	/jaan-to:dev-docs-fetch "Model Context Protocol TypeScript SDK Streamable HTTP server tools resources 2026"	MCP server
13	pgvector	/jaan-to:dev-docs-fetch "pgvector HNSW index creation Postgres 17 cosine distance dimension 1536"	Insight embeddings
14	pg_trgm	/jaan-to:dev-docs-fetch "pg_trgm trigram similarity Postgres 17 GIN gist index operators"	Fuzzy search on names
15	PostgreSQL 17	/jaan-to:dev-docs-fetch "PostgreSQL 17 declarative partitioning range autovacuum logical replication"	Schema design + ops
16	PostgreSQL FTS	/jaan-to:dev-docs-fetch "PostgreSQL text search configuration dictionary hunspell ispell tsvector tsquery"	Persian + English FTS
17	pgBackRest	/jaan-to:dev-docs-fetch "pgBackRest configuration S3 archive WAL streaming PITR retention"	Backups
18	Tailwind CSS 4	/jaan-to:dev-docs-fetch "Tailwind CSS 4 @theme @import config v3-to-v4 migration"	Admin UI styles
19	shadcn/ui	/jaan-to:dev-docs-fetch "shadcn/ui Next.js App Router components.json CLI installation"	Admin UI primitives
20	Argon2 (node-argon2)	/jaan-to:dev-docs-fetch "node-argon2 Argon2id parameters m t p OWASP recommended 2026"	Share-link password hash
21	Caddy	/jaan-to:dev-docs-fetch "Caddy 2 reverse proxy Let's Encrypt Docker Compose Hono Next.js"	Reverse proxy + TLS
22	Docker Compose	/jaan-to:dev-docs-fetch "Docker Compose healthchecks restart unless-stopped depends_on condition profiles"	Orchestration
23	OpenTelemetry JS SDK	/jaan-to:dev-docs-fetch "OpenTelemetry Node.js auto-instrumentation logs traces Grafana Loki exporter"	Observability
24	Playwright	/jaan-to:dev-docs-fetch "Playwright TypeScript visual regression screenshot per-locale RTL"	Per-product verification agents
25	remark + remark-wiki-link	/jaan-to:dev-docs-fetch "remark remark-wiki-link unified processor Markdown AST wikilink alias resolution"	galaxy-docs wikilink parser
26	CodeMirror 6	/jaan-to:dev-docs-fetch "CodeMirror 6 Markdown editor extensions live preview React integration"	galaxy-docs editor
27	gray-matter (YAML frontmatter)	/jaan-to:dev-docs-fetch "gray-matter YAML frontmatter parser write-back round-trip preservation"	galaxy-docs frontmatter
28	Obsidian Local REST API	/jaan-to:dev-docs-fetch "Obsidian Local REST API plugin endpoints spec authentication compatibility"	galaxy-docs Obsidian shim
29	Astro 6 + live content collections	/jaan-to:dev-docs-fetch "Astro 6 live content collections src/live.config.ts API integration request-time fetching cache headers"	galaxy-docs reading surface
30	Starlight	/jaan-to:dev-docs-fetch "Astro Starlight theme sidebar nav search i18n RTL component overrides custom components"	docs site theme
31	Astro Cloudflare Pages adapter	/jaan-to:dev-docs-fetch "Astro Cloudflare Pages adapter ISR on-demand revalidation 2026"	docs site deploy (when ready for Cloudflare)

Rule for implementation phases: every code change that touches a library on this list must first reference the cached docs path (jaan-to/outputs/docs-cache/<lib>/). Stale assumptions from training data are the single biggest failure mode this avoids.

---

Per-product data-mapping agents (Phase 0.5 — produce importer specs)

Before any importer is written, one mapping agent per product scans the legacy data sources, documents every field with type + sample values, and produces a precise field-by-field mapping to the Galaxy schema. The output of each is a markdown spec file the importer code is generated against. They are read-only Explore-type Claude Code agents (subagent_type: Explore); their output blocks the start of each domain's Phase.

Each mapping agent's output (jaan-to/outputs/data-mapping/<product>.md) has the same structure:

1. Source inventory — every file/table scanned, sample row, field count
2. Field-by-field mapping table — source path → galaxy table.column → type coercion → notes
3. ID strategy — how legacy_id is set, how display_id is composed
4. FK resolution rules — how string values (e.g. "televika") resolve to FK ids (e.g. businesses.slug='televika')
5. Bilingual fields — which fields are EN-only, which are FA-only, which are paired
6. Edge cases — nulls, missing keys, format quirks, encoding gotchas
7. Galaxy schema changes required — if any source field needs a Galaxy column not yet in the plan, flag and stop
8. Importer pseudocode — step-by-step that the eventual importer will implement
9. Verification queries — SQL the importer's smoke test should run after a successful import
10. Open questions — anything the agent couldn't determine from data alone (e.g. "what should we do with rows where frontmatter.lang is missing?")

The 5 mapping agents

#	Agent	Source(s) to scan	Target Galaxy tables	Phase blocked
1	abtest-mapping	/Users/parhumm/Downloads/tests_rows.json (~10 rows, exported from Supabase 2026-05-24)	abtests	Phase 3
2	audits-mapping	Product-Audits/src/data/products.json + Product-Audits/src/data/{televika,filimo}/{issues,pages,health,ai-issues-insights,user-feedback,docs}.json + walkthrough images at Product-Audits/public/e2e-walkthroughs/<business>/<flow>/<viewport>/step-NN.png	audit_issues, audit_pages, audit_walkthroughs, audit_health_runs, audit_health_tests, ai_insights, comments (user-feedback flows here), businesses (seed from products.json)	Phase 1
3	pm-mapping	product-decisions/site/src/data/master.json + pipeline.json + improvements.json + history.json + product-decisions/site/db/schema.sql + product-decisions/site/db/seed.sql + the product-decisions/televika/<topic>-<date>/v2-flow-redesign.md doc folders	pm_tasks (incl. merged_into_id, deleted_at), pm_pipeline, taxonomies (goals/areas/journeys/phases), audit_log (from history.json), notes + note_attachments (for the v2 cancellation flow doc + images)	Phase 2
4	comments-mapping	Televika-Foreign-Comment-Analysis-Dashboard/data/comments.json.gz (220K rows; use `zcat	head -200to sample without loading full file) +data/filimo_comments.json(3.7K) +data/insights.json+data/filimo_insights.json+data/foreign_issue_translations.json+data/filimo_issue_translations.json+ scraper code insrc/scrape/*.py` (for fields the JSON doesn't make obvious)	comments (partitioned), comment_insights, taxonomies (themes), businesses (seed Iranian + foreign VOD competitor list)
5	docs-mapping	Sweep all 4 legacy repos for committed .md doc folders: Product-Audits/src/data/{televika,filimo}/docs.json + raw .md files in Product-Audits/, product-decisions/televika/<topic>-<date>/*.md + sibling images/ folders, product-decisions/operations/, product-decisions/shared/, plus any markdown in the Televika.../ repo's repo root	notes, note_versions (initial v1), note_attachments, tags (auto-extract from frontmatter), entity_links (wikilinks + cross-domain references)	Phase 4.5

Mapping-agent prompt template

Each agent is invoked with this prompt shape (parametrized per product):

You are mapping a legacy product's data sources into the new productgalaxy Postgres schema.

PRODUCT: <name>
SOURCES TO SCAN: <list of files / directories>
TARGET GALAXY TABLES: <list of tables from the schema section of the plan>
PLAN FILE (read for schema reference): /Users/parhumm/.claude/plans/now-plan-to-new-spicy-dragonfly.md

PRODUCE: jaan-to/outputs/data-mapping/<product>.md with the 10-section structure
described in the "Per-product data-mapping agents" plan section.

CONSTRAINTS:
- Read-only. Do not write to any source file.
- Sample, don't dump: for large JSON (like comments.json.gz), use `zcat | head -200` and
  `wc -l` to estimate size; never load full file into memory.
- For every Persian string you see, copy a sample to the spec with ZWNJ chars preserved
  (so the implementer can verify byte-identical handling).
- If you find a source field that has no obvious Galaxy column, STOP and add it to
  "Galaxy schema changes required". Don't invent a mapping that loses data.
- For wikilinks in markdown docs, list every distinct link target found so the importer
  can pre-resolve them when populating entity_links.
- Cite line numbers / byte offsets when referring to specific data.

Output: a single markdown file at jaan-to/outputs/data-mapping/<product>.md.

Run order

Phase 0           — Foundation + research + docs cache
Phase 0.5         — All 5 mapping agents run in parallel (each blocks one downstream phase)
Phase 1 (Audits)  — starts only after audits-mapping returns
Phase 2 (PM)      — starts only after pm-mapping returns
Phase 3 (ABtest)  — starts only after abtest-mapping returns
Phase 4 (Comments)— starts only after comments-mapping returns
Phase 4.5 (Docs)  — starts only after docs-mapping returns

If any agent's "Galaxy schema changes required" section is non-empty, the schema is updated in packages/db/schema/*.ts and a fresh Drizzle migration generated BEFORE the importer is written. That way, importers never have to deal with "field exists in source but not in DB" — every source field has a documented destination.

Why this matters for the non-tech operator

This is the single highest-leverage gate against silent data loss during migration. Without it, an importer written from memory or training data will likely:

• Drop fields that look "optional" but actually carry meaning (e.g. developer_reply: null vs missing key)
• Corrupt Persian text by normalizing ZWNJ or RTL marks
• Mis-resolve business strings ("Televika" vs "televika" vs "TVK")
• Miss the soft-delete + merge semantics in pm_tasks
• Skip the walkthroughs that only landed after the original audit

The mapping agent's output makes every importer decision reviewable in plain English by the non-tech operator before any data is touched.

---

Per-product verification agents (3 per app, 12 total)

For each of the 4 old apps, three dedicated agents verify the data-layer swap on a staging deploy pointed at Galaxy, before production cutover. Implemented as Claude Code subagents (subagent_type: Explore for data-parity work; subagent_type: general-purpose for UI/functional work that drives Playwright via the mcp__plugin_jaan-to_playwright__* MCP tools you already have).

ABtest-dashboard — 3 agents

Agent	Purpose	Pass criteria
abtest-functionality	Drive every UI action via Playwright (create, edit, delete, switch product tab, upload screenshot, export, recompute stats); assert every action hits Galaxy and returns 2xx	All flows in js/app.js work end-to-end with zero references to Supabase remaining
abtest-data-parity	Read every row from the exported JSON at /Users/parhumm/Downloads/tests_rows.json (no live Supabase needed); compare field-by-field with imported Galaxy abtests. Assert: same count as JSON file, JSONB inputs/metrics deep-equal after parse, every original Supabase screenshot URL has been mirrored to a Galaxy URL (HEAD-able), z-score recomputed on Galaxy data == stored z-score, Persian text in hypothesis and analysis_document byte-identical	100% row match vs export file, 0 drift, 0 character corruption
abtest-ui-parity	Visual + behavioral diff on 10 representative pages at 1440×900 pre- vs post-swap; pixel-diff <1%; text content identical	<1% visual delta, identical text

Product-Audits — 3 (now 4) agents

Agent	Purpose	Pass criteria
audits-functionality	Drive every page (Overview, Issues, Health, User Feedback, AI Insights, Matrix, Walkthrough modal) in EN + FA via Playwright; verify filters, modals, URL state sync, language toggle, RTL rendering, image-zoom modals, walkthrough step navigation, tier-aware Filimo assertions	Every route + every interactive component works
audits-data-parity	For each of televika/{issues,pages,health,ai-issues-insights,user-feedback,docs}.json and same for filimo/: snapshot legacy state immediately before import (count + sha256-of-each-record), compare vs Galaxy. Assert counts match the snapshot (not a fixed number — the repo is live), all _fa bilingual fields preserved, issue IDs unchanged, health-run history complete, insight evidence arrays intact	100% record match in both locales vs pre-import snapshot
audits-content-parity	Load every issue/insight/doc page via swapped UI; assert rendered HTML contains exact original title, title_fa, problem, problem_fa, solution, solution_fa, summary, summary_fa, recommendation, recommendation_fa (no escaping bugs, no ZWNJ loss)	Byte-identical text in rendered DOM vs source JSON
audits-walkthrough-parity (NEW, added after 2026-05-24 drift check)	For every public/e2e-walkthroughs/<business>/<flow>/<viewport>/step-NN.png in the repo: assert a matching row exists in audit_walkthroughs; assert PNG sha256 round-trips; assert step-ordering preserved; drive the Walkthrough modal in UI and verify step-N+1 advances correctly	100% PNG round-trip; modal step nav matches source

product-decisions — 3 agents

Agent	Purpose	Pass criteria
pm-functionality	Drive Now/Map/Pace/Intake via Playwright; quick-add, item-drawer edit, status change, reset, JSON export, EN/FA toggle, dictionary lookups, history, "done items sink to bottom" sort behavior (post-2026-05-24 UI change), Apps section (new), Telegram-published children rendering	Every flow works against Galaxy; zero localStorage data-layer writes; done items render at bottom in Now
pm-data-parity	Snapshot master.json + pipeline.json + improvements.json + history.json immediately before import; compare counts + per-record sha256 vs Galaxy pm_tasks + pm_pipeline + audit_log. Verify W-NNN/PM-NNN→TVK-PM-NNN mapping documented, blocked_by FK links intact, bilingual fields preserved, status enum mapped, **soft-deleted items (e.g. [[task:TVK-PM-012	W-012]]) preserved with deleted_at not hard-deleted**, **merged items (e.g. [[task:TVK-PM-018
pm-cross-link-parity	Verify pre-existing implicit cross-references survive ([[task:TVK-PM-145	W-145]] with 6 children, [[task:TVK-PM-018

Televika Comment Analysis — 3 agents

Agent	Purpose	Pass criteria
comments-functionality	Drive all 4 tabs via Playwright; date/sentiment/theme/platform filters, pagination, CSV export, charts (sentiment trend, doughnut, distribution), Analyze trigger; Flask handlers proxy to Galaxy	All UI controls produce same data; CSV exports byte-identical
comments-data-parity	Stream-compare every row of comments.json.gz (220K) + filimo_comments.json vs Galaxy comments; assert: counts match exactly, theme/area/sentiment/lang/confidence preserved, Persian text uncorrupted (ZWNJ + RTL marks intact), dates parsed, null-vs-empty developer_reply distinction preserved	100% row match, 0 char corruption
comments-insights-parity	Compare insights.json + filimo_insights.json vs Galaxy comment_insights; assert executive_summary verbatim, each opportunity's evidence_quote byte-identical (user-quoted strings), stats deep-equal, regenerate-insights stable	Byte-identical insight content; regen produces stable output

All 3 of an app's agents run in parallel on staging. All three must pass before production cutover.

---

End-to-end + integration test matrix (per functionality, per product)

Every user-facing functionality in every product gets two tests in Galaxy production:

• E2E — Playwright drives the product's UI pointed at Galaxy; assert the user-visible outcome
• Integration — direct API call to Galaxy + SQL assertion against Postgres; assert the contract and DB state

E2E tests live in productgalaxy/tests/e2e/<product>/<functionality>.spec.ts. Integration tests live in productgalaxy/tests/integration/<domain>/<functionality>.test.ts. CI runs both suites against an ephemeral PG17 + seeded data on every PR. Production cutover for each product is blocked until 100% of that product's row passes both columns.

ABtest-dashboard — 12 functionalities

#	Functionality	E2E (Playwright)	Integration (API + SQL)
1	Create A/B test	Fill /index.html form (name, hypothesis, traffic split, dates, period, inputs); click Create; assert detail page renders; assert test appears in list on reload	POST /api/v1/abtests with payload; assert 201 + display_id returned; SELECT * FROM abtests WHERE legacy_id=… matches input verbatim (JSONB deep-equal)
2	Edit A/B test	Open existing test; change hypothesis + traffic split; Save; reload; assert new values persisted	PATCH /api/v1/abtests/{id}; assert 200; audit_log has row with field=hypothesis, correct before/after
3	Delete A/B test	Click Delete; confirm; assert removed from list; reload; assert still removed	DELETE /api/v1/abtests/{id}; assert 204; SELECT returns 0 rows; audit_log has deletion entry
4	Compute z-score & p-value	Open test with known inputs; assert UI shows z=X, p=Y (matching js/calculator.js fixtures)	GET /api/v1/abtests/{id} returns metrics.zScore and metrics.pValue; compare to TypeScript port of calculator.js to 4 decimals
5	Switch product tab	Click Filimo tab; assert only Filimo tests shown; click Aparat; same	GET /api/v1/abtests?business=filimo returns only Filimo; cross-business JWT call returns 403
6	Upload origin screenshot	Pick image; assert preview; Save; reload; assert image displayed	POST /api/v1/abtests/{id}/screenshots (multipart); assert presigned URL returned; abtests.screenshots.origin populated; URL is HEAD-able
7	Upload variant + significance + trend screenshots	Same as 6 for each image slot	Same as 6 per slot; assert all 5 keys (origin, variant, significance, usersTrend, purchasedTrend) persist independently
8	Rich-text analysis doc	Type/format markdown via toolbar; Save; reload; assert HTML preserved	PATCH /api/v1/abtests/{id} with analysis_md; assert round-trip preserves all markdown including code blocks
9	View test modal	Click View; assert modal shows full inputs, metrics, screenshots, analysis	GET /api/v1/abtests/{id} returns full record; UI renders all fields without extra fetches
10	Custom test type CRUD	Add custom type "Onboarding flow"; assert appears in dropdown; reload; assert persisted	POST /api/v1/taxonomies kind=test_type; appears in GET /api/v1/taxonomies?kind=test_type
11	Export test as detail-of-test PDF/HTML	Click Export; assert downloaded file contains all test data	API returns rendered HTML; render preserves all bilingual fields
12	Re-render after Supabase decommission	After cutover, page loads with USE_GALAXY=true; assert zero references to supabase.co in network tab	All /api/v1/abtests/* calls hit Galaxy domain; no Supabase requests in 10-minute trace

Product-Audits — 16 functionalities

#	Functionality	E2E (Playwright)	Integration (API + SQL)
1	Overview dashboard (stats cards)	Load /; assert all stat tiles render with correct counts	GET /api/v1/audit-issues?business=televika count matches stat tile
2	Overview AI insights grid	Assert insights cards render with title_fa + impact + recommendation	GET /api/v1/ai-insights?business=televika returns N insights; each has all bilingual fields
3	Overview journey heatmap	Assert heatmap cells render with issue counts per (journey, severity)	SQL: SELECT journey_id, priority, count(*) FROM audit_issues GROUP BY 1,2; matches heatmap data
4	Issues list + status filter	Open Issues; filter by "To Do"; assert only matching rows	GET /api/v1/audit-issues?status=to_do; assert all returned rows have status='to_do'
5	Issues list + journey + type + platform + priority + owner filters	Apply each filter independently and in combination	Combined query asserts WHERE clause AND semantics
6	Issue detail modal (IssueModal)	Click an issue; assert modal opens with all bilingual fields, evidence, problem_fa, solution_fa	GET /api/v1/audit-issues/{display_id} returns complete record
7	Edit issue status from detail	Change status in modal; Save; assert list updates without refresh	PATCH /api/v1/audit-issues/{id} {status: 'in_progress'}; audit_log row written
8	Pages browser	Open Pages; assert page cards with name, name_fa, capture thumbnails	GET /api/v1/audit-pages?business=televika; captures jsonb deep-equal source
9	Health runs view	Open Health; assert last run shows per-test results with screenshots	GET /api/v1/audit-health/runs returns runs in date desc; latest matches last_run
10	Per-test viewport variants	Click into a test; assert desktop + mobile viewport results both render	audit_health_tests.viewports jsonb contains both viewport keys
11	User feedback view + filters	Open User Feedback; filter by sentiment + platform; assert correct rows	GET /api/v1/comments?business=televika&sentiment=negative&platform=cafebazaar matches
12	AI Insights detail modal (InsightModal)	Click insight; assert evidence references resolve to actual issues	Each evidence: ["8","12"] resolves to existing audit_issues.display_id
13	Matrix view (goal × area)	Open Matrix; assert cells with issue counts; click cell drills into Issues filtered	GET /api/v1/audit-issues?goal=X&area=Y returns same rows as the cell drill-down
14	Language toggle EN ↔ FA	Toggle; assert all visible text switches; assert dir=rtl on Persian	API returns both title and title_fa always; client renders based on locale state
15	RTL rendering	In FA mode, assert mirrored chevrons, right-aligned text, ZWNJ preserved	Source data ZWNJ chars present in DB byte-for-byte; rendered DOM contains them
16	Image-zoom modal	Click any screenshot; assert lightbox opens with focus trap, Esc closes	n/a (UI-only); E2E sufficient

product-decisions — 14 functionalities

#	Functionality	E2E (Playwright)	Integration (API + SQL)
1	Now view — table render	Open /now; assert all active tasks render with status chips and health dots	GET /api/v1/pm-tasks?business=televika&status=active matches table
2	Now view — group by goal/area/owner	Toggle groupBy; assert grouped rows	n/a (UI-only); E2E sufficient
3	Now view — multi-filter	Apply goal + area + status + health + platform; assert rows match	GET /api/v1/pm-tasks with each query param; AND semantics
4	Map view — coverage matrix	Open /map; assert (goal × area) cells with task counts	SELECT goal_id, area_id, count(*) FROM pm_tasks GROUP BY 1,2; matches cells
5	Map view — dependencies table	Assert blocked_by relationships rendered as table rows	SELECT * FROM pm_tasks WHERE blocked_by_id IS NOT NULL matches
6	Pace view — velocity + cycle time	Open /pace; assert metrics rendered	API-computed metrics match SQL aggregates over pm_tasks.done_date - start_date
7	Pace view — stale items	Assert items with last_updated > 30 days ago listed	Same SQL filter matches
8	Pace view — history log	Assert history entries with date + description + items	GET /api/v1/audit-log?entity_kind=pm_task returns matching entries
9	Intake view — docs-to-items matching	Open /intake; pick a doc; create item; assert created	POST /api/v1/pm-tasks from intake; entity_links row written linking doc→task
10	Quick Add modal	Open QuickAdd; create task; close; assert appears in Now	POST /api/v1/pm-tasks returns task; appears in next GET
11	Item drawer edit	Open ItemDrawer for a task; edit fields; Save; assert UI updates	PATCH /api/v1/pm-tasks/{display_id}; audit_log per field
12	Status change inline	Change status select inline; assert immediate update; reload; persisted	Same PATCH as above
13	Reset overlay	Click Reset (with confirmation); assert localStorage no longer touched; UI shows server state	n/a — Galaxy version has no localStorage; reset is a no-op or removed
14	JSON export to clipboard	Click Export; assert clipboard contains valid JSON of current view	n/a (UI-only); E2E sufficient
15	EN/FA language toggle + RTL	Toggle; assert all visible text + chevrons + spacing flip	All *_fa fields preserved; <html lang> + dir sync correctly

Televika Comment Analysis — 15 functionalities

#	Functionality	E2E (Playwright)	Integration (API + SQL)
1	Tab — Foreign VOD comments	Click tab; assert table of foreign-platform comments	GET /api/v1/comments?primary_business_id=televika&kind=foreign matches
2	Tab — Iranian competitors (Filimo)	Click tab; assert Filimo competitor comments	GET /api/v1/comments?primary_business_id=filimo&kind=competitor matches
3	Tab — Insights	Click tab; assert executive summary + opportunity cards	GET /api/v1/comment-insights?business=televika returns latest
4	Tab — Detail	Click a comment; assert full detail with developer reply, theme, sentiment, lang, confidence	GET /api/v1/comments/{id} returns full row
5	Date range filter	Pick from + to dates; assert table filtered	GET /api/v1/comments?from=…&to=…; SQL: WHERE date BETWEEN matches
6	Sentiment filter	Select Negative; assert only negative rows	?sentiment=negative matches
7	Theme filter	Select theme; assert filtered	?theme={id} matches; theme_id correctly joined
8	Platform filter	Select Netflix; assert only Netflix rows	?business={netflix.id} matches
9	Pagination	Click next page; assert page 2 of results	?page=2&per_page=N; correct offset
10	CSV export	Click Export; assert CSV downloaded; assert byte-identical row count + content vs API	GET /api/v1/comments?format=csv returns CSV with all expected columns
11	Sentiment trend chart (weekly)	Assert Chart.js renders weekly sentiment line	API returns weekly aggregated counts; data matches chart
12	Doughnut chart per platform	Assert doughnut renders with platform slices	API returns per-platform sentiment counts
13	Distribution bar	Assert bar with positive/negative/mixed proportions	API returns total sentiment distribution
14	Analyze trigger (regenerate insights)	Click Analyze; assert progress; insights refresh; failure case → no operator command leak in error UI	POST /api/v1/comment-insights/regenerate; assert job completes; new comment_insights.generated_at
15	Persian text rendering (RTL, ZWNJ)	Load page with Persian comments; assert no character corruption, no missing ZWNJ, correct RTL bidi	Source rows in DB byte-identical to original JSON; rendered DOM preserves them

galaxy-docs — 18 functionalities

#	Functionality	E2E (Playwright)	Integration (API + SQL)
1	Create note from UI	Open /admin/notes/new; fill slug, title, content, frontmatter; Save; assert visible in tree	POST /api/v1/notes; assert 201 + row in notes; note_versions row written
2	Edit note (versioned)	Open existing note; change body; Save with commit_message; assert version 2 in history	PATCH /api/v1/notes/{slug}; note_versions count increments; audit_log entry
3	Soft-delete (archive)	Click Archive; assert removed from tree; reload; still gone	DELETE /api/v1/notes/{slug}; archived=true; still SELECTable with ?include_archived=true
4	Wikilink resolves by slug	Write [[other-note]] in body; Save; assert link rendered as clickable	entity_links has row from this note → other-note; backlinks API returns it
5	Wikilink with alias	Write [[other-note|Display Text]]; Save; assert rendered text = "Display Text"	Parser stores both display text and target slug
6	Wikilink resolves by alias	Note A has alias "AH"; in note B write [[AH]]; Save; assert resolves to A	notes.aliases array participates in resolution; integration test confirms
7	Cross-domain wikilink to audit issue	Write [[issue:TVK-AI-008]]; Save; assert rendered, click opens audit issue in Product-Audits	entity_links row: from_kind=note, to_kind=audit_issue, link_type=wikilink
8	Cross-domain wikilink to PM task	Write [[task:TVK-PM-050]]; assert resolves + renders	Same as 7 with to_kind=pm_task
9	Embed ![[note]] (transclude)	Write ![[other-note]]; assert other note's body renders inline	Resolver fetches embedded content; recursion-depth-limited
10	Backlinks panel	Open a note that's wikilinked from 3 others; assert all 3 listed in backlinks rail	GET /api/v1/notes/{slug}/backlinks returns 3 rows
11	Tag note (hierarchical)	Add tag #engineering/backend; assert tag chip + appears in tag tree	note_tags row + auto-create tags row with parent_tag_id for engineering
12	Filter notes by tag	Click tag in sidebar; assert tree filters to tagged notes only	GET /api/v1/tags/{slug}/notes returns same set
13	YAML frontmatter editor	Edit frontmatter via properties panel; Save; assert frontmatter parsed + queryable	notes.frontmatter jsonb updated; WHERE frontmatter->>'status' = 'draft' works
14	FTS search EN	In search box, query "playback"; assert ranked results	GET /api/v1/notes/search?q=playback&lang=en; uses content_fts GIN; weighted by title vs body
15	FTS search FA	Query Persian term; assert Persian-tokenized matches	Uses content_fts_fa via persian_hunspell config
16	Version history diff	Open history; pick v2 vs v5; assert diff view	GET /api/v1/notes/{slug}/versions returns ordered; UI uses jsdiff
17	Restore previous version	From history view, click Restore on v3; assert current = v3 + a new v6 row	POST /api/v1/notes/{slug}/restore?version=3; new note_versions row written
18	Share note (password + expiry)	Click Share; set password + 7-day expiry; copy link; open in incognito; enter password	share_links row with Argon2id password_hash; /s/{token} renders read-only

galaxy-docs reading site (Astro Starlight) — 8 functionalities

#	Functionality	E2E (Playwright)	Integration
D1	Home page renders all sections	Visit docs.galaxy.example.com; assert sidebar shows folder tree from Galaxy	Live content collection fetched /api/v1/notes at build + on revalidation
D2	Per-note rendering	Click a note in sidebar; assert MD rendered, frontmatter properties shown, ToC sidebar, backlinks pane	GET /api/v1/notes/{slug} returns note + outlinks; backlinks resolved server-side
D3	Wikilink click navigates	Click [[other-note]]; assert URL changes to /notes/other-note, content updates without full reload	Astro's view transitions; entity_links resolved at render-time
D4	Cross-domain wikilink deep-link	Click [[issue:TVK-AI-008]]; assert opens Product-Audits at issue detail (new tab)	Renders as <a href="…product-audits…/issues/TVK-AI-008" target="_blank">
D5	Embed ![[note]] transcludes	Open a note that embeds another; assert embedded content renders inline	Server-side resolution during build/render; recursion-depth-limited
D6	Bilingual + RTL	Switch language to FA in header; assert UI flips + Persian notes render with dir="rtl"	frontmatter.lang drives per-note dir; Starlight i18n handles UI strings
D7	Search across all notes	Type query; assert results list with snippets; click result opens note	Client-side index for fast; >50K notes fall through to /api/v1/notes/search
D8	Live-update without rebuild	Edit note in admin → 5s later refresh docs site → updated content shown	Live content collection revalidates via cache headers; no Astro rebuild needed

galaxy-docs Obsidian-compat — 6 functionalities

#	Functionality	E2E (manual + scripted)	Integration
19	Vault export	GET /api/v1/notes/vault/export?business=televika; unzip; open folder in Obsidian	Zip structure: <folder>/<slug>.md + _attachments/; all wikilinks preserved as Obsidian syntax
20	Vault import (round-trip)	Export → edit a few notes in Obsidian → re-zip → import	Idempotent: same notes update by frontmatter.id; new notes create; assert no duplicates
21	Obsidian Local REST API compat	Install Obsidian Local REST API plugin pointed at Galaxy; from Obsidian command palette, list notes	Galaxy's /local-rest-api/notes subset responds with same shape; auth via API token
22	Two-way edit from Obsidian (via Local REST API)	Edit a note in Obsidian → Galaxy UI shows new version next refresh	Same note_versions write path; audit_log.source='api'
23	Bilingual round-trip	Note with FA title and FA body → export → reopen in Obsidian → re-import	Persian chars + ZWNJ preserved; lang: fa frontmatter survives
24	Cross-domain wikilink survives export	Note with [[issue:TVK-AI-008]] → export → re-import	Round-trip preserves the prefix syntax; entity_links re-resolves on import

Cross-product E2E suite (5 scenarios)

#	Scenario	Steps	Pass criteria
1	Cross-domain trace	In Product-Audits create issue TVK-AI-200 → in product-decisions create TVK-PM-050 linked to it → in ABtest-dashboard create test linked to PM task → in Product-Audits open issue detail	"Related work" section shows both linked entities; clicking deep-links to source app
2	MCP write + UI read	Claude MCP create_audit_issue for Televika → open Product-Audits Issues list	New issue appears within 5s; audit_log.source='mcp'
3	Share link with password	Product-Audits "Share" on an issue → set Argon2id password + 7d expiry → open /s/{token} incognito → enter password	Entity renders read-only; share_access_log written; wrong password rejected; after 7d returns 410 Gone
4	Multi-business RBAC	User in televika only opens product-decisions → switch business selector to Filimo	API returns 403; UI shows "no access" empty state with clear message
5	Disaster recovery	Drop all DB tables on staging → pgBackRest restore latest → restart all 4 apps	All apps boot; row counts match pre-disaster; CSV export from Televika app produces identical bytes

Integration test categories (Galaxy side)

Category	Coverage	Example test
Schema integrity	Every Drizzle migration up/down on empty + populated DB	Migrate up, insert sample row in every table, migrate down to baseline
OpenAPI conformance	Every endpoint's request/response matches generated spec	openapi-test harness asserts handler return shape == Zod schema
JWT scoping	Per-business JWT can/can't access cross-business endpoints	Issue Televika-scoped JWT; assert 403 on Filimo data
Audit hash chain	Every audit_log write extends the chain correctly	Insert 1000 entries; verify row_hash(N) = hash(prev_hash(N) + payload(N)) for all N
Importer idempotency	Re-running importer produces 0 deltas	Run importer twice; assert second run logs 0 inserts
Bilingual round-trip	Every _fa field preserves ZWNJ + RTL marks	Insert known Persian string with ZWNJ; read; assert byte-equal
JSONB round-trip	Every JSONB column preserves field order + null vs missing	Insert known object; read; assert deep-equal including null/missing distinction
FTS dictionary	Persian Hunspell config recognizes stems	to_tsvector('persian_hunspell', 'تبلیغات') returns expected lexemes
pgvector recall	Known query returns expected top-K	Seed 1000 insights, query for known semantic match; assert in top-5
Rate limits	Per-JWT and per-MCP-user limits trigger 429 at threshold	Burst 100 reads in 1s; assert at least one 429 returned with Retry-After
Backup/restore	pgBackRest full + diff + WAL restore to point-in-time	Take backup, perform writes, restore to before-writes, assert state matches
Hot partition writes	Comments inserted concurrently across years don't deadlock	Insert 10K rows in parallel across 5 partitions; assert all committed

CI runs full integration suite on every PR (target <8 min). E2E suite runs nightly + on main merges (target <25 min). Both suites must be green for production cutover of any product.

---

Development workflow + safety guardrails (non-technical builder + Claude Code)

Context: this entire project is built by a non-technical user driving Claude Code, with no in-house engineers reviewing PRs. The same agent class has already caused a production-deletion incident in the wild — PocketOS lost their entire production database and all backups on April 25, 2026 when a Cursor + Claude Opus 4.6 agent found a broadly-scoped Railway CLI token in an unrelated file and used it across environments. The guardrails below are designed so that incident class is structurally impossible here, not just discouraged.

Six structural defenses (ordered by blast-radius reduction)

#	Defense	Implementation
1	No prod credentials in any file	Production secrets live ONLY in the prod VPS's /etc/galaxy/.env (file mode 600, root-owned). Claude Code working in /Users/parhumm/Projects/SabaIdea/productgalaxy/ has NO ambient access to prod. The repo's .env.example documents shape only, never values. git-secrets pre-commit hook + a deny permission rule on any path matching **/secrets/**.
2	Per-environment token scope	Three distinct token sets: local-dev, staging, prod. Each scoped to its own DB / S3 / VPS. No token can address a different environment. CI runs with staging only. Prod deploys never use Claude Code's ambient credentials — they run via a signed-commit-only GitHub Actions workflow that pulls prod secrets from GitHub Encrypted Secrets and never echoes them.
3	Plan mode is the default for risky operations	A .claude/settings.json hook (UserPromptSubmit) detects keywords like migrate, deploy, drop, delete, rm, production, prod, force-push, truncate, restore, seed, rotate-key in the user prompt and auto-enables plan mode for that turn. The agent cannot bypass plan mode for those operations.
4	Auto-backup before every DB change	A PreToolUse(Bash) hook intercepts any command matching drizzle-kit migrate, psql, pg_*, docker compose exec postgres * and runs pgbackrest backup --type=full --stanza=galaxy to S3 first, then tags the git repo with pre-{operation}-{utc-timestamp}. Rollback is pgbackrest restore --set={tag} + git checkout {tag}.
5	CI is the only path to merge	main is branch-protected, signed-commits-required. Every PR must pass: pnpm typecheck + pnpm lint + drizzle-kit check + integration tests + Playwright E2E + /security-review skill output + /jaan-to:backend-pr-review skill output. Failure on any of these blocks merge — no admin override.
6	Auto-revert on production smoke failure	After every prod deploy, a smoke-test job runs the 5 cross-product E2E scenarios + a synthetic write+read across each domain. If any fails, GitHub Actions automatically reverts to the prior tag and posts a Slack/email alert. The non-tech user does not have to detect or trigger the rollback.

.claude/settings.json (committed at repo root)

{
  "permissions": {
    "deny": [
      "Bash(rm -rf:*)",
      "Bash(*--force*)",
      "Bash(git push --force*)",
      "Bash(git reset --hard origin/*)",
      "Bash(git checkout --:*)",
      "Bash(git clean -f*)",
      "Bash(git branch -D*)",
      "Bash(* drop * database*)",
      "Bash(* drop schema*)",
      "Bash(* truncate *)",
      "Bash(* delete from *)",
      "Bash(pg_dropcluster*)",
      "Bash(docker compose down -v*)",
      "Bash(docker volume rm*)",
      "Bash(docker system prune*)",
      "Edit(**/secrets/**)",
      "Edit(**/.env)",
      "Edit(**/.env.production*)",
      "Edit(.github/workflows/deploy-prod.yml)",
      "Write(**/secrets/**)",
      "Write(**/.env)",
      "Write(.github/workflows/deploy-prod.yml)"
    ],
    "ask": [
      "Bash(drizzle-kit migrate*)",
      "Bash(drizzle-kit drop*)",
      "Bash(pnpm db:seed*)",
      "Bash(pnpm db:reset*)",
      "Bash(docker compose down*)",
      "Bash(docker compose up*)",
      "Bash(git push:*)",
      "Bash(gh pr merge*)",
      "Bash(gh release create*)",
      "Bash(pgbackrest restore*)",
      "Bash(npm publish*)",
      "Bash(pnpm publish*)"
    ],
    "allow": [
      "Bash(pnpm typecheck)",
      "Bash(pnpm lint*)",
      "Bash(pnpm test*)",
      "Bash(drizzle-kit check)",
      "Bash(drizzle-kit generate*)",
      "Bash(git status)",
      "Bash(git diff*)",
      "Bash(git log*)",
      "Bash(git add*)",
      "Bash(git commit*)",
      "Bash(git pull --rebase)",
      "Bash(git switch*)",
      "Bash(gh pr view*)",
      "Bash(gh pr list*)",
      "Bash(gh pr create*)",
      "Bash(docker compose ps)",
      "Bash(docker compose logs*)",
      "Bash(pgbackrest info*)"
    ]
  },
  "hooks": {
    "UserPromptSubmit": [
      { "command": ".claude/hooks/auto-plan-mode-for-risky-ops.sh" }
    ],
    "PreToolUse": [
      { "matcher": "Bash", "command": ".claude/hooks/auto-backup-before-db-change.sh" },
      { "matcher": "Bash", "command": ".claude/hooks/block-cross-env-token-use.sh" },
      { "matcher": "Edit|Write", "command": ".claude/hooks/block-secrets-write.sh" }
    ],
    "PostToolUse": [
      { "matcher": "Edit|Write", "command": ".claude/hooks/run-formatter.sh" }
    ],
    "SessionStart": [
      { "command": ".claude/hooks/print-current-environment-banner.sh" }
    ]
  }
}

Hook scripts (committed at .claude/hooks/)

Script	Behavior
auto-plan-mode-for-risky-ops.sh	scans the prompt for risky keywords; if matched, prepends a plan-mode directive so Claude can't take any action without a written plan first
auto-backup-before-db-change.sh	matches drizzle-kit migrate, psql, pg_* etc.; runs pgbackrest backup + git tag pre-{op}-{ts} first; exits 0 on backup success, exit code 2 (blocks the tool call) on backup failure
block-cross-env-token-use.sh	reads the current env from .env; if the operation references a different environment (string match prod while in local, etc.) blocks with exit 2
block-secrets-write.sh	greps the new file content for known secret patterns (Supabase URL/anon-key, Argon2id hash, Better Auth secret, OAuth client_secret, GitHub PAT); blocks with exit 2
run-formatter.sh	runs pnpm prettier --write on the changed file
print-current-environment-banner.sh	prints `ENV: local

Plain-language slash commands for the non-tech user

Stored in .claude/commands/ (markdown files with a single command). Each is one button-press for a common workflow.

Slash command	What it does	Why it's safe
/galaxy:status	Runs git status, pnpm typecheck, pgbackrest info, lists last 5 audit_log entries; explains all in plain English	Read-only
/galaxy:test	Runs pnpm test + Playwright E2E; explains pass/fail in plain language; if any fail, suggests next action	Read-only
/galaxy:explain <file>	Reads the file; explains in plain English what it does and why it exists	Read-only
/galaxy:safe-changes	Runs git diff, summarizes proposed changes in plain English, flags any that look risky	Read-only
/galaxy:make-pr	Stages changes, generates a PR title + body via /jaan-to:backend-pr-review, opens PR via gh pr create; never force-pushes	Write but reversible; CI gates the merge
/galaxy:deploy-staging	Pushes to main; triggers staging deploy via GitHub Actions; waits for smoke tests	Write; auto-revert if smoke fails
/galaxy:deploy-prod	Hard-confirms (typed DEPLOY PRODUCTION required); triggers signed prod workflow; waits for smoke tests; auto-rollback on failure	Write; requires explicit confirmation phrase; auto-revert
/galaxy:rollback	Picks last green tag; runs deploy of that tag to current env	Write but explicitly reversing
/galaxy:backup-now	Runs pgbackrest backup --type=full; reports URL + timestamp	Write but additive only
/galaxy:list-backups	pgbackrest info formatted plain-language	Read-only
/galaxy:restore-from <tag>	Plan-mode-only; produces a restore plan, requires explicit "yes restore from " confirmation	Write; double-gated

CI workflow (every PR — .github/workflows/ci.yml)

on: [pull_request]
jobs:
  gate:
    steps:
      - checkout
      - pnpm install --frozen-lockfile
      - pnpm typecheck
      - pnpm lint
      - drizzle-kit check       # drift detection vs schema
      - pnpm test:integration   # API + SQL contract tests
      - pnpm test:e2e           # Playwright per-product flows
      - run /security-review skill on diff (block on critical findings)
      - run /jaan-to:backend-pr-review skill on diff (block on confidence>0.8 issues)
      - npm audit --audit-level high (block on highs)
      - codeql analyze --languages typescript,javascript (block on critical)
      - check no new packages outside packages-allowlist.json

Best jaan-to skills for this workflow (mapped per phase)

Phase	Skill	When to use
Planning	/jaan-to:pm-prd-write	At start of any new feature — generates PRD before code
Planning	/jaan-to:pm-story-write	Decomposes PRD into INVEST stories with acceptance criteria
Planning	/jaan-to:pm-research-about	Deep research before tech decisions (we're using this)
Architecture	/jaan-to:backend-data-model	New tables — generates schema + migration + ERD
Architecture	/jaan-to:backend-api-contract	New endpoints — generates OpenAPI 3.1 + error schemas
Architecture	/jaan-to:backend-task-breakdown	Breaks PRD into ordered backend tasks
Architecture	/jaan-to:frontend-task-breakdown	Same for the admin UI + galaxy-docs UI
Implementation	/jaan-to:backend-scaffold	Routes + service stubs + validation
Implementation	/jaan-to:backend-service-implement	Business logic from spec
Implementation	/jaan-to:frontend-scaffold	React components from designs
Implementation	/jaan-to:frontend-design	UI for the admin shell + notes editor
Implementation	/jaan-to:frontend-component-fix	When a generated component needs polishing
Implementation	/jaan-to:devops-infra-scaffold	Docker Compose + GitHub Actions + Caddy + pgBackRest
Quality	/jaan-to:qa-test-cases	BDD/Gherkin test cases from acceptance criteria
Quality	/jaan-to:qa-test-generate	Runnable test files from BDD cases
Quality	/jaan-to:qa-test-run	Execute + diagnose + auto-fix
Quality	/jaan-to:dev-verify	Smoke tests against a running stack
Quality	/jaan-to:backend-pr-review	Required check on every PR
Quality	/security-review	Required check on every PR
Quality	/jaan-to:sec-audit-remediate	Fix flagged security issues
Quality	/simplify	After each phase — find duplication + dead code
Ops	/jaan-to:devops-deploy-activate	First prod deploy + secrets bootstrapping
Ops	/jaan-to:detect-dev	Quarterly engineering audit; same skill we used here
Ops	/jaan-to:detect-pack	Quarterly consolidated audit
Learning	/jaan-to:learn-add	After every surprise/fix — capture lesson for future sessions
Learning	/jaan-to:learn-report	Monthly review of accumulated lessons
Discovery	/jaan-to:pm-skill-discover	Detects repeated patterns Claude does; suggests new skills to automate them

Mandatory pre-flight reads at every session start

SessionStart hook also calls /jaan-to:learn-report if jaan-to/learn/ has new entries since last session. This ensures lessons learned (e.g. "don't try drizzle-kit push", "Persian search needs persian_hunspell config", "comments table is partitioned — don't insert into parent") are loaded into context before Claude does anything.

Package-supply-chain protection

After the January 2026 react-codeshift hallucination incident (an AI-hallucinated npm package infiltrated 237 repos):

• packages-allowlist.json at repo root lists every approved npm package + version range
• A CI step diffs pnpm-lock.yaml against the allowlist; new packages require an explicit PR adding them to the allowlist
• pnpm install runs with --ignore-scripts by default; package lifecycle scripts require explicit --allow-scripts=<pkg>
• Dependabot enabled for security updates only (not feature updates) so the allowlist stays tight
• npm audit --audit-level high blocks PRs on high/critical vulns

Defer-to-research: the same /jaan-to:pm-research-about for this workflow

Phase 0 includes one more research run on top of the 9 layer-specific ones:

/jaan-to:pm-research-about "Claude Code workflow + guardrails for non-technical builder running a 5+ year self-hosted production project: hook patterns, settings.json deny rules, auto-backup-before-DB-change patterns, slash-command UX for safety, eval gates in CI, package supply-chain protection, post-PocketOS-incident best practices, single-developer rollback patterns, learning-loop with /jaan-to:learn-add"

Output: jaan-to/outputs/pm-research/dev-workflow-guardrails.md. If the research surfaces a guardrail not in the table above, the plan is updated before Phase 1 starts.

---

Drift since the audit (2026-05-20 → 2026-05-24)

Re-checked all 4 legacy repos against origin/main right before plan finalization. ABtest-dashboard (07ee629) and Televika Comment Analysis (3840e27) are unchanged. Two have drifted:

Product-Audits (88aba18 → ee903dd)

~300 new commits, mostly noisy chore health-screenshot churn (~280 of them), but with several real shape-changing features:

Change	Plan implication
E2E walkthrough modal feature + 90+ new screenshots under public/e2e-walkthroughs/{televika,filimo}/<flow>/{desktop,mobile}/step-NN.png	New domain concept: walkthroughs = ordered screenshot sequences per (business, flow, viewport). Add audit_walkthroughs table to Galaxy schema.
feat: Update Filimo payment tooltip to reflect logged-out test behavior	Tooltip copy lives in client; no schema change
feat(televika-health): humanize signup timeout error to flag CAPTCHA	audit_health_tests.error field; the importer must preserve humanized error strings byte-equal
feat(docs): auto-detect RTL/LTR direction on blockquotes	Client-side renderer feature; informs galaxy-docs renderer (auto-detect lang per blockquote, not just per note)
feat(docs): supporting infra + assets for Promote Watching on TV doc + docs(televika): add service outage & internet issues feedback analysis	New audit docs added → galaxy-docs scope expanded; importer must handle whatever doc-folder convention they're using
Tier-aware Filimo assertions in E2E + sketch iframes + IMAP race fix	Test-level changes; no schema impact
audit_issues row count for televika may have shifted slightly (a few issues likely added/closed)	Per-product verification agents must use "as of commit X" snapshots, not fixed counts

New table to add to Galaxy schema:

audit_walkthroughs  (id, business_id FK, flow_slug TEXT,  -- 'payment'|'player'|'login'|...
                     viewport TEXT,                         -- 'desktop'|'mobile'
                     step_number INT,
                     screenshot_url TEXT, caption TEXT NULL,
                     captured_at, captured_by)
                    -- UNIQUE(business_id, flow_slug, viewport, step_number)
                    -- index btree(business_id, flow_slug, viewport, step_number)

New API endpoints:

GET    /api/v1/audit-walkthroughs?business=televika&flow=payment
POST   /api/v1/audit-walkthroughs/bulk         (Playwright workflow inserts new captures)
GET    /api/v1/audit-walkthroughs/{business}/{flow}/{viewport}/{step}

Per-product verification agent addition (audits-walkthrough-parity): Compare every public/e2e-walkthroughs/<business>/<flow>/<viewport>/step-NN.png filename to a row in audit_walkthroughs; assert PNGs round-trip (sha256 match) and ordering preserved.

product-decisions (cc2b9f2 → bdd640d)

25 commits, all substantive:

Change	Plan implication
Work item churn: [[task:TVK-PM-012	W-012]] deleted, [[task:TVK-PM-083
New "Apps section" + [[task:TVK-PM-144	W-144]] embed-data + [[task:TVK-PM-117
Now.tsx UI change: done items sink to bottom	Pure client-side sort change; but the underlying ordering may need a server-supported sort key (done_date already in schema; client-side sort works); no Galaxy schema change
Done items sink to bottom + W-140 retitled + W-121 validated	Same as above
New v2 cancellation flow doc at televika/televika-cancellation-flow-improvements-1405-02-27/v2-flow-redesign.md + 30+ PNGs under images/v2/ (bench-step-, step-)	Perfect fit for galaxy-docs. This is exactly the doc + image-bundle pattern galaxy-docs is designed for. The importer for galaxy-docs Phase 4.5 should ingest this whole folder as: 1 note (the .md), N attachments (the PNGs), tag #televika/cancellation-flow.
merge W-018 into W-100 + W-005 v2 doc	Suggests the team uses item-merge semantics — Galaxy should support a merged_into_id FK NULL on pm_tasks to preserve history

Schema additions to capture:

pm_tasks            ADD COLUMN merged_into_id FK NULL  -- ref to the task this was merged into
                    ADD COLUMN deleted_at TIMESTAMP NULL  -- soft-delete like W-012
                    -- mark legacy "deleted" tasks as soft-deleted (not actually deleted) for audit

Note about the v2 cancellation flow doc: it's stored in product-decisions today as a markdown file with images in a sibling images/v2/ folder. galaxy-docs's vault-import endpoint should handle this convention natively (folder = 'televika/cancellation-flow-improvements-1405-02-27', frontmatter.date = '1405-02-27', attachments under that note). Add a one-off importer for this kind of doc in Phase 4.5.

Action: re-audit immediately before Phase 1

The current plan's row counts (159 audit issues, 220K comments, etc.) come from the May 20 audit. By the time Phase 1 starts, those will be stale. Add to Phase 1's first task:

# Phase 1 — first task
1. git pull each legacy repo
2. re-run detect-product + detect-dev on Product-Audits and product-decisions
3. update the per-product verification agent expected counts from the fresh audit output
4. THEN run the importer

This is a Drizzle migration: no schema change is needed beyond the additions in this section, but the importer needs to handle the new walkthroughs + the new audit docs + the merged-tasks + the soft-delete semantics.

---

Repository setup + CLAUDE.md content

Location: /Users/parhumm/Projects/SabaIdea/productgalaxy/ Remote: git@github.com:parhumm/productgalaxy.git Default branch: main Bootstrap commands (Phase 0 first hour):

mkdir -p /Users/parhumm/Projects/SabaIdea/productgalaxy
cd /Users/parhumm/Projects/SabaIdea/productgalaxy
git init -b main
git remote add origin git@github.com:parhumm/productgalaxy.git
# write CLAUDE.md, README.md, .gitignore, package.json, docker-compose.yml, etc.
git add -A && git commit -m "chore: bootstrap productgalaxy repo"
git push -u origin main

Monorepo layout (pnpm workspaces):

productgalaxy/
├── CLAUDE.md                       ← critical rules (full content below)
├── README.md
├── docker-compose.yml              ← postgres + app + mcp + caddy + pgbackrest
├── pnpm-workspace.yaml
├── package.json
├── drizzle.config.ts
├── .env.example
├── .github/workflows/              ← CI: lint, typecheck, integration tests
├── apps/
│   ├── app/                        ← Next.js 15 admin UI + Hono /api/v1 routes
│   └── mcp/                        ← Node MCP server (Streamable HTTP)
├── packages/
│   ├── db/                         ← Drizzle schemas + migrations + seed
│   ├── auth/                       ← Better Auth config + Drizzle adapter
│   ├── shared/                     ← Zod schemas (source of truth for OpenAPI)
│   ├── clients-ts/                 ← generated TS clients per domain
│   └── clients-py/                 ← generated Python client (galaxy_comments, galaxy_notes)
├── importers/
│   ├── abtests/                    ← from Supabase
│   ├── audits/                     ← from Product-Audits JSON
│   ├── pm/                         ← from product-decisions JSON
│   └── comments/                   ← from Televika data/*.json
├── tests/
│   ├── integration/                ← API + SQL contract tests
│   └── e2e/                        ← Playwright per-product flows
└── jaan-to/                        ← skill outputs (research, docs cache)
    └── outputs/
        ├── pm-research/            ← 9 research briefs (Phase 0)
        └── docs-cache/             ← 28 dev-docs-fetch outputs (Phase 0)

CLAUDE.md (proposed content — copy into the repo at first commit)

# productgalaxy — Critical rules for development

This file is loaded automatically by Claude Code in this repo. Treat every rule here as a hard
constraint. When in doubt, ask before deviating.

---

## 1. The minimum-change contract for the 4 legacy apps

We are the shared backend behind 4 existing apps (ABtest-dashboard, Product-Audits,
product-decisions, Televika-Foreign-Comment-Analysis-Dashboard) and one new product
(galaxy-docs). The 4 legacy apps' UIs do NOT change. Their teams own those repos. Our only
deliverable to them is:

- A versioned REST API client (TS or Python) generated from our OpenAPI spec.
- An OAuth 2.0 client credential pair scoped to their business(es).
- A drop-in replacement for their data-layer module (e.g. their `storage.js`, `store.ts`,
  `load_data()`).

If a PR for a legacy app touches anything besides its data layer, REJECT IT and explain that
the contract is data-layer-only.

## 2. Source of truth conventions

- **Schemas** live in `packages/db/schema/*.ts` (Drizzle).
- **Validation + API contracts** live in `packages/shared/*.ts` (Zod). These generate OpenAPI 3.1
  via `zod-openapi`. The TS + Python clients are GENERATED, not hand-written.
- **Auth** is Better Auth in `packages/auth/`. Do NOT add a second auth path or skip the auth
  middleware on any /api/v1 route.
- **Per-business scoping** is mandatory on every query that touches per-domain tables. The
  scope comes from the JWT (`business_id` array). Cross-business reads return 403.

## 3. ID preservation (legacy_id is sacred)

Every imported record carries its original ID as `legacy_id`. The legacy apps continue to
display these as before (PM-001, TVK-008, UUID-… for abtests). NEVER mutate legacy_id values.
New display IDs (TVK-PM-001, TVK-AI-001, TVK-AB-001) are additional, not replacements.

## 4. Bilingual fields are byte-preserved

Every `*_fa` field, every Persian character, every ZWNJ (`‌`), every RTL mark
(`‏`/`‎`) MUST round-trip the import + API + render path byte-identical to the source.
Verification agents will catch this; do not paper over a failure by normalizing the text.

## 5. JSONB round-trip

Every JSONB column preserves field order, null vs missing distinction, nested array order.
Use `jsonb` not `json`. Don't reshape JSONB during import; preserve the source under `raw jsonb`
and extract specific keys into typed columns ONLY when a query needs them.

## 6. Database rules

- **Postgres 17** required. Don't downgrade.
- **`drizzle-kit push` is FORBIDDEN in any environment except a one-off local prototype.**
  Always `drizzle-kit generate` + `drizzle-kit migrate`. CI runs `drizzle-kit check` and fails
  on drift.
- **Migrations are append-only.** Never delete a migration file. To revert: write a forward
  migration that undoes it, or restore from pgBackRest PITR.
- **Comments table is partitioned by date (yearly).** New partitions created annually by a
  migration; never insert into the parent table directly.
- **Persian FTS** uses the `persian_hunspell` text search configuration. Don't fall back to
  `simple` without explicit reason in the PR description.
- Every per-domain table has a `business_id` FK. No exceptions.

## 7. Auth rules

- **Argon2id** for every password hash (human auth via Better Auth; share-link passwords).
  Params: m=19456 KiB, t=2, p=1. Never bcrypt or scrypt or PBKDF2.
- **Per-app M2M** uses OAuth 2.0 Client Credentials Flow issuing short-lived (15 min) scoped
  JWTs + 7-day refresh tokens. No long-lived API keys.
- **Per-business RBAC** via `user_businesses` and Better Auth Organizations plugin. Roles:
  `owner | pm | analyst | viewer`.

## 8. Audit log rules

Every write to a per-domain table writes a row to `audit_log` with `prev_hash` + `row_hash`
forming a hash chain. CI includes a job that verifies the chain integrity. Do not write to
`audit_log` directly from app code — use the `auditedWrite()` helper which handles hashing.

## 9. MCP server rules

- Use `@modelcontextprotocol/sdk` (NOT the archived `@modelcontextprotocol/server-postgres`,
  which has a known SQL auth-bypass).
- Transport: **Streamable HTTP** per the 2026-07-28 spec (stateless).
- Postgres role: `mcp_app` with SELECT/INSERT/UPDATE on per-domain tables — NO DROP, NO DDL,
  NO DELETE.
- Tools must be **narrow** (one operation each). NO `sql_query` tool. NO bulk writes from MCP.
- Every write tool requires `confirm: true` parameter from the caller and writes an audit_log
  row with `source='mcp'` + the identified user's ID.

## 10. galaxy-docs rules

- Markdown body is plain text + standard CommonMark. YAML frontmatter is parsed by `gray-matter`
  with round-trip preservation (don't reorder keys).
- Wikilinks `[[Slug]]` and aliased `[[Slug|Display]]` are parsed via `remark-wiki-link` at
  write-time and stored in `entity_links`. Don't compute wikilinks at read-time.
- Cross-domain wikilink syntax `[[issue:TVK-AI-008]]`, `[[task:TVK-PM-050]]`,
  `[[abtest:TVK-AB-001]]`, `[[insight:42]]`, `[[comment:CM-…]]` is mandatory — don't invent
  alternative syntaxes.
- Tag slugs use `/` separator for hierarchy (`engineering/backend`).
- Vault export produces files at `<folder>/<slug>.md` with Obsidian-compatible frontmatter
  (`id:` carries Galaxy note ID for round-trip).

## 11. API rules

- REST + OpenAPI 3.1, versioned at `/api/v1`. Breaking changes go to `/api/v2` — never break
  v1 once a legacy app's data-layer swap is in production.
- Zod schema is the single source of truth — TS types, runtime validation, and OpenAPI spec
  all derive from it.
- Every endpoint requires JWT auth (via Better Auth). Public endpoints (`/oauth/token`,
  `/s/[token]`, `/health`) are explicitly allowlisted in middleware.

## 12. Testing rules

- Every API endpoint has an integration test (`tests/integration/`) asserting status code,
  response shape (vs Zod schema), and DB state.
- Every user-facing functionality in every product has a Playwright E2E test
  (`tests/e2e/<product>/`).
- The 12 per-product verification agents must pass on staging before any product's cutover
  to USE_GALAXY=true in production. Staging burn-in: 7 days minimum.
- Importer tests assert idempotency: running an importer twice produces 0 deltas on the second
  run.

## 13. Docs cache discipline

Before writing code that touches a library on our stack, READ the cached docs at
`jaan-to/outputs/docs-cache/<library>/`. The cache is the ground truth for that library's
API in this codebase. If the cache is missing, run `/jaan-to:dev-docs-fetch` to populate
it BEFORE coding. Don't rely on training-data assumptions for any library version.

## 14. Secrets

- Never commit `.env` files (only `.env.example`).
- The leaked credentials in the legacy apps (`pms1405` from product-decisions; the Supabase
  anon key in ABtest-dashboard) MUST be rotated before any importer is run against them.
- New secrets live in `docker-compose.override.yml` (gitignored) for local dev, in CI env
  for CI, and in the VPS's `.env` for production.

## 15. Deployment

- Docker Compose on a single Hetzner VPS from day 1. Postgres has to live on a VPS regardless
  (Cloudflare Containers can't host it). No two-topology split.
- pgBackRest sidecar with WAL archiving to Backblaze B2 (S3-compatible).
- Caddy 2 as reverse proxy + automatic Let's Encrypt.
- Zero-downtime deploys via Haloy CLI (layer-only image push + atomic swap) OR Watchtower
  for auto-update. Never raw `docker compose down && up` in production.

## 16. Pull request checklist (paste into every PR description)

- [ ] Touches only data-layer files (for legacy app PRs)
- [ ] References cached docs from `jaan-to/outputs/docs-cache/` for any library used
- [ ] Drizzle migrations are append-only; `drizzle-kit check` passes
- [ ] New endpoint has Zod schema + OpenAPI spec entry + integration test
- [ ] New user-facing functionality has Playwright E2E test
- [ ] Every per-domain query is per-business scoped
- [ ] Every write goes through `auditedWrite()`
- [ ] If touching bilingual fields, manually verified ZWNJ + RTL marks preserved
- [ ] No long-lived API keys introduced
- [ ] No secrets committed
- [ ] For galaxy-docs PRs: wikilinks parsed at write-time, NOT read-time
- [ ] For MCP PRs: new tools are narrow + audit-logged + scoped to JWT business

## 17. When to ask the human

- Schema changes that touch `audit_log` or `entity_links` or `share_links` (cross-cutting)
- Anything that changes a legacy app's UI
- New write tools in MCP
- Adding a new tech-stack layer not in the recommended stack
- Anything that bypasses an item on this list

## 18. Non-technical operator rules (you are working for one)

The human running this project is non-technical. Always:

- Explain in plain English what you are about to do BEFORE doing it. No jargon.
- Refuse to take destructive actions without explicit typed confirmation (e.g. "yes delete TVK-AI-008" — not just "y").
- Show the diff in plain English before any commit, in a 3-line max summary: what changed, why, what could break.
- When tests fail, explain the failure in plain English + propose the smallest fix + ask before applying.
- Never use jargon-only error messages. Translate "TypeError: Cannot read properties of undefined" into "the data we expected to be there is missing — here's the line, here's what we expected."
- Before any production deploy, list the exact changes that will go live + the expected outcome + the rollback step (`/galaxy:rollback`) in plain English.
- When a `/jaan-to:learn-add` entry would prevent a future repeat of a mistake we just made, suggest it and ask if the user wants to save it.
- If you are uncertain about ANY production-affecting action, switch to plan mode and write the plan first.

## 19. Hard prohibitions (settings.json enforces these — never try to bypass)

- `rm -rf` (any flavor)
- `git push --force` to any branch
- `drizzle-kit push` in any environment
- Direct SQL DROP / TRUNCATE / DELETE FROM (always go through Drizzle migrations)
- Writing or reading `.env` files in the repo (production secrets live only on the VPS)
- Editing `.github/workflows/deploy-prod.yml` without explicit human approval
- Installing npm packages not in `packages-allowlist.json`
- Running with `--no-verify` or `--ignore-scripts` removed
- Using long-lived API keys instead of OAuth client credentials
- Storing any credential in source code or version-controlled config

If you find yourself wanting to bypass one of these because it's "blocking you", stop and ask the human. The block exists because the alternative caused a production incident somewhere in 2026.

---

Critical files to read before implementing

Source schemas to mirror in Drizzle:

• /Users/parhumm/Projects/SabaIdea/product/ABtest-dashboard/js/storage.js — Supabase tests + custom_test_types
• /Users/parhumm/Projects/SabaIdea/product/ABtest-dashboard/js/calculator.js — z-test math (stays in ABtest-dashboard; reference only)
• /Users/parhumm/Projects/SabaIdea/product/Product-Audits/src/data/products.json — business registry seed
• /Users/parhumm/Projects/SabaIdea/product/Product-Audits/src/data/televika/{issues,pages,health,ai-issues-insights,user-feedback}.json — schemas + taxonomy enums (journeys, types, statuses, platforms, owners, phases)
• /Users/parhumm/Projects/SabaIdea/product/product-decisions/site/db/schema.sql — closest existing analogue
• /Users/parhumm/Projects/SabaIdea/product/product-decisions/site/db/seed.sql — initial roles (rotate pms1405 before reuse)
• /Users/parhumm/Projects/SabaIdea/product/product-decisions/site/src/data/master.json — pm_tasks source
• /Users/parhumm/Projects/SabaIdea/product/product-decisions/site/src/proposal/store.ts — localStorage shape
• /Users/parhumm/Projects/SabaIdea/product/Televika-Foreign-Comment-Analysis-Dashboard/src/scrape/{runner,filimo_runner}.py — scraper output row shape
• /Users/parhumm/Projects/SabaIdea/product/Televika-Foreign-Comment-Analysis-Dashboard/src/web/app.py — load_data() + _filter_rows() to replace
• /Users/parhumm/Projects/SabaIdea/product/Televika-Foreign-Comment-Analysis-Dashboard/src/analyze/insights.py — insight rule engine

Existing audit context (already produced):

• All 24 jaan-to/outputs/detect/**/*.md files per subproject

---

Verification

Foundation (Phase 0)

• docker compose up brings postgres + app + mcp + caddy + pgBackRest healthy
• pnpm db:migrate applies cleanly on empty PG17
• psql -c "CREATE TEXT SEARCH CONFIGURATION persian_hunspell ..." succeeds with custom dict
• pnpm db:seed produces ≥12 businesses, ≥40 taxonomies, 1 admin user, 1 OAuth client per app
• Admin UI: log in, switch active business, RBAC blocks cross-business reads, add a new business at runtime → appears in API
• Issue per-app OAuth client, exchange for JWT, call GET /api/v1/audit-issues?business=televika → 200
• Same JWT against ?business=filimo → 403
• pgBackRest: full backup completes, WAL archiving active, restore-to-point-in-time dry-run works against a scratch DB

Per phase (per domain) — importer + swap

• Phase 1 (Audits): SELECT count(*) FROM audit_issues WHERE business_id=televika ≈ 159, =filimo ≈ 3. Spot-check 3 random issues. Product-Audits pointed at Galaxy → all pages render identically; UI edit updates Postgres + audit_log; audit_log.row_hash verifies against prev_hash chain.
• Phase 2 (PM): count(*) matches master.json. product-decisions pointed at Galaxy → Now/Map/Pace/Intake identical; localStorage no longer used; edit hits API + persists.
• Phase 3 (ABtest): count(*) = Supabase rowcount. Re-run one test's z-score in ABtest-dashboard pointed at Galaxy → identical result.
• Phase 4 (Comments): count(*) ≈ 220K. FTS query: WHERE text_fts_fa @@ websearch_to_tsquery('persian_hunspell','تبلیغات') returns Persian matches; text_fts_en @@ websearch_to_tsquery('english','ads') returns English. Flask app pointed at Galaxy → all 4 tabs render same; scraper bulk insert succeeds.

Cross-cutting

• Entity links: create TVK-AI-200 in Product-Audits → create TVK-PM-050 linked to it in product-decisions → create A/B test linked to the PM task → GET /api/v1/entity-links/TVK-AI-200 returns both; MCP get_entity_links returns both
• MCP smoke: Claude Desktop → list_businesses returns seeded list; search_comments_fts(business='filimo', query='ads', lang='en') returns Filimo English comments; create_audit_issue succeeds, appears in Product-Audits UI, audit_log shows source='mcp' + correct user
• Share link: any app POSTs /api/v1/share-links → token returned → /s/[token] in incognito → Argon2id password gate → entity renders → share_access_log row written + hash-chained to prior. Expiry honored. Revoke takes effect immediately.
• Backup/restore drill: every quarter, restore last backup to a scratch container, verify row counts match, verify FTS queries return same results

---

What this plan defers (not v1)

• Galaxy UI for any domain — admin UI only
• Realtime collaboration (websockets, CRDT) — last-write-wins + audit log is sufficient
• Mobile apps
• Public marketing surface — Galaxy is internal-only
• Auto-sync back to old data stores — old stores freeze at cutover
• Webhook/event bus for external integrations — direct API + MCP only in v1
• Code-level monorepo merge of the four old apps — they stay in own repos, on own pipelines

---

Research findings (synthesizing ~200 sources from this session)

Auth — Lucia is officially deprecated

The Lucia maintainer sunset the library in March 2025, redirecting it to be a learning resource on implementing auth from scratch. Better Auth has surpassed it and is now the 2026 default for self-hosted Drizzle + Postgres apps, with built-in Organizations/RBAC/passkeys/magic-links. ⇒ Better Auth, not Lucia.

Database — PostgreSQL 17 over 16

PG17 vacuum memory usage dropped up to 20×; high-concurrency write throughput up to 2× from WAL improvements; partition-wise aggregate pushdown for reporting; partition pruning even when partition key is wrapped in immutable functions. Logical replication in 17 makes future zero-downtime in-place upgrades smoother. ⇒ Start on PG17.

Comment FTS — Persian needs a custom dictionary

PG ships ~20 FTS dictionaries; Persian/Farsi is NOT among them. Trigram (pg_trgm) underperforms on complex script + diacritics. The standard pattern: install Hunspell fa_IR files into tsearch_data, CREATE TEXT SEARCH DICTIONARY persian_hunspell (Template = ispell, DictFile = fa_IR, AffFile = fa_IR), CREATE TEXT SEARCH CONFIGURATION persian_hunspell. Use a separate generated text_fts_fa column routed by lang. Use text_fts_en (built-in english config) for English. pg_trgm reserved for fuzzy name search on shorter strings. ⇒ Hunspell FA + generated columns + per-lang GIN indexes.

Comment storage — FTS for raw, pgvector for derived only

For <10M rows, native PG FTS (tsvector + GIN) is faster to implement, cheaper to operate, simpler to maintain. pgvector overhead only justified when semantic search is the actual use case — and "semantic comments search" is rarely needed; "semantic insights search" is. ⇒ FTS on comments, HNSW pgvector on comment_insights.embedding only.

Partitioning — yearly RANGE on date, prune+vacuum per partition

GIN write amplification is a real cost on high-write tables; partitioning by date amortizes it. Hot partitions get aggressive autovacuum settings; cold partitions get freeze scheduling. ANALYZE must be scheduled on the parent table (autovacuum doesn't analyze it). ⇒ PARTITION BY RANGE (date) yearly + per-partition autovacuum tuning.

Backups — pgBackRest, not pg_basebackup alone

pg_basebackup has no catalogue, no parallel backup, no incremental. pgBackRest gives full + differential + incremental + parallel + S3 + retention policies + PITR — what you actually need for a 5-year-lived database. Cheapest off-site target: Backblaze B2 (~$5/TB/mo, S3-compatible). ⇒ pgBackRest sidecar in Compose + B2 archive.

Deploy — single VPS from day 1

Cloudflare Containers reached GA April 2026 but Postgres still can't run there (containers are ephemeral, cold-start, no persistence). Postgres has to live on a VPS anyway, so the "Cloudflare-first → VPS-later" two-topology approach adds zero value. Hetzner ax-line VPS at ~€20/mo (4 vCPU / 16 GB / 512 GB NVMe) handles years of growth at this comment volume. ⇒ Docker Compose on a single Hetzner VPS from day 1.

Compose vs PaaS — Coolify has a recent CVE

Coolify is feature-rich but its dashboard consumes meaningful resources and had a January 2026 plaintext-secret-retrieval vulnerability disclosed. Kamal is excellent but Rails-flavored (less idiomatic for TS/Node). Raw Docker Compose is the most boring and most-future-proof. For deploy ergonomics add Haloy (CLI, yaml config, zero-downtime layer-only image push). ⇒ Compose + optional Haloy CLI for deploys.

ORM — Drizzle for size + transparency

Drizzle is 33KB vs Prisma's ~800KB engine; SQL-first; readable migrations; first-party Better Auth adapter. Critical: never drizzle-kit push in production (silently skips changes) — always generate + migrate; commit migration files; drizzle-kit check in CI for drift. Drizzle does not have built-in rollback — use forward-fix migrations or PITR restore. ⇒ Drizzle + drizzle-kit generate/migrate + drift CI check.

API style — REST + OpenAPI 3.1 (NOT tRPC for polyglot)

tRPC is TypeScript-only — unusable from Flask + vanilla JS. For polyglot clients, REST + OpenAPI 3.1 is the only universal choice. Use Zod as single source of truth: define schema in Zod → zod-openapi generates spec → openapi-zod-client generates TS client → openapi-python-client generates Python client. ⇒ REST + Zod-OpenAPI + per-language client generation.

MCP — Streamable HTTP stateless + narrow tools + hash-chained audit

The archived @modelcontextprotocol/server-postgres has a known SQL auth-bypass (attackers can "COMMIT; DROP TABLE users;" past read-only mode). Do not use it. The 2026-07-28 spec moves to stateless Streamable HTTP — no required Mcp-Session-Id, with Mcp-Method/Mcp-Name headers for load-balancer routing. Production patterns: never use admin DB account (use scoped role like mcp_app), split into many narrow tools (single flexible tools make audit logs uninterpretable), append-only audit log with cryptographic integrity (each row hashes prior row + own payload). ⇒ Custom Node MCP server + stateless Streamable HTTP + narrow split tools + hash-chained audit_log.

M2M auth — OAuth 2.0 Client Credentials, not long-lived API keys

The 2026 pattern is per-app OAuth 2.0 Client Credentials Flow issuing short-lived (15 min) scoped JWTs + 7-day refresh tokens. Leaked tokens have limited blast radius. Scopes carry business + operation permissions. ⇒ OAuth 2.0 Client Credentials per old app.

Password hashing — Argon2id over bcrypt

OWASP/NIST 2026 recommend Argon2id as the gold standard. Minimum config: m=19456 KiB, t=2, p=1. Memory-hard → resists GPU attacks. Better Auth uses Argon2id by default. Also used for share-link passwords. ⇒ Argon2id everywhere a password is hashed.

JSONB — hybrid pattern, generated columns, expand-and-contract

Stripe/GitHub/Netflix-class pattern: typed columns for fixed/queried fields, JSONB for variable. Never schemaless-JSONB without validation/versioning — silent data corruption. Use PG12+ generated columns to extract specific JSONB paths into typed columns when querying patterns solidify. For schema evolution: expand-and-contract (add new shape next to old, migrate traffic gradually, drop old shape after burn-in). ⇒ Hybrid JSONB + generated columns + expand-and-contract migrations.

Sharing — we're a differentiator

Notion has no native password-protected share links as of March 2026 — only via third-party tools like Papermark/Sotion/HelpKit. Our built-in share-link system (token + Argon2id password + expiry + max_uses + access log + revoke) is a clear UX win vs. competitors. ⇒ Build it well; this is leverage.

Docs rendering — Astro 6 Starlight + live content collections (NOT Docusaurus)

Astro 6 went stable March 2026 with live content collections (src/live.config.ts) — fetch from APIs/databases at request-time, no rebuild needed when content changes. Docusaurus has no equivalent; every change requires a full rebuild. Starlight ships zero JavaScript by default with Lighthouse-100 out of the box; Docusaurus ships React hydration on every page. Tailwind integration is a one-line command on Astro; Docusaurus's Infima CSS is tightly coupled and harder to customize. Both ecosystems support wikilinks via remark-wiki-link, but Starlight's content-collection model is the natural fit for pulling notes from Galaxy's API on demand. The Cloudflare-acquisition of Astro (Dec 2025 / 2026 deepening) is bonus — first-class Cloudflare Containers / Pages adapter when we want to move from VPS-only. ⇒ Astro 6 + Starlight + live content collections at apps/docs/, hosted at docs.galaxy.example.com.

galaxy-docs — build, don't fork Docmost

Docmost (NestJS + React + Postgres + Redis) is the closest existing self-hosted Obsidian-alternative with a Postgres backend, but it's an enterprise wiki — its block-based editor + CRDT collab adds complexity we explicitly don't want for "simple". Building lean on top of Galaxy's existing Postgres + Better Auth + entity_links + share_links costs less code, no Redis dependency, and gives us the cross-domain wikilinks ([[issue:TVK-AI-008]]) that no off-the-shelf tool offers. ⇒ Build galaxy-docs from scratch on Galaxy's existing primitives; reserve Docmost as the fallback if dev time exceeds 3 weeks.

Obsidian wikilinks — remark-wiki-link for the parser

Obsidian wikilinks resolve by filename without extension, support aliases [[Slug|Display]], and live in both body and frontmatter (since Obsidian 1.4+). On the server side, remark-wiki-link (unified ecosystem) is the most mature JS parser; emits a wikiLink AST node with value (target) + data.alias (display) that we can route into entity_links. ⇒ remark-wiki-link + custom resolver that supports cross-domain prefix syntax + alias-aware resolution against notes.slug ∪ notes.aliases.

Non-tech-builder safety — structural, not advisory

The April 2026 PocketOS incident (Claude Opus 4.6 in Cursor deleted prod DB + all backups by finding a broadly-scoped Railway CLI token in an unrelated file) is the cautionary tale we design against. Claude Code's tiered permission model + hooks (which can deterministically block with exit code 2, unlike advisory CLAUDE.md) + auto mode (reasons over rules before risky actions) are necessary but not sufficient. Add: no prod creds in any repo file, per-environment token scope, plan-mode-by-default for risky keywords, auto-pgBackRest-before-DB-change hook, CI as only merge path, auto-revert on prod smoke failure. Plus package-supply-chain protection after the January 2026 react-codeshift hallucinated-package incident. ⇒ Six structural defenses + permissioning + hooks + slash-commands designed for a non-tech operator.

Obsidian sync — Local REST API plugin compat, not full sync server

Self-hosted Obsidian sync today centers on CouchDB + LiveSync plugin (real-time replication). That's overkill for our use case (we want Galaxy as SoT, not a peer in a sync mesh). Simpler: implement a subset of the Obsidian Local REST API plugin endpoint contract so users who install that plugin can point it at Galaxy and get reads/writes from the Obsidian app. Plus vault export/import for bulk operations. A first-party Obsidian plugin is a Phase 6 option, not v1. ⇒ Local REST API compat + vault export/import in v1; defer custom plugin.

---

Sources informing the stack recommendation (subset of ~200 reviewed)

Database / partitioning / FTS

• PG17 partition-wise aggregate pushdown — Stormatics
• PG17 vacuum memory + WAL improvements — Medium DevBoost
• Postgres FTS 2026 — Viprasol
• Persian FTS via Hunspell — blogspot
• Multilingual FTS — Sling Academy
• GIN write-amplification + partitioning — pganalyze
• Autovacuum tuning for billion-row tables — Chaos and Order
• Hatchet — pitfalls of partitioning Postgres yourself

pgvector / embeddings

• Case Against pgvector — Alex Jacobs
• pgvector HNSW PG18 tuning 2026 — Nerd Level Tech
• pgvector HNSW vs IVFFlat — dbi-services

Backups

• pgBackRest PITR Docker demo — Data Egret
• pgBackRest S3 backups — Uptrace
• pgBackRest setup — DCHost

Auth

• Lucia deprecated, Better Auth the choice — HonoGear
• Better Auth + Drizzle + Postgres — Makerkit
• Better Auth vs Lucia vs NextAuth — PkgPulse
• M2M OAuth 2.0 Client Credentials — Authgear
• Password hashing Argon2id 2026 — guptadeepak.com
• OWASP password hashing 2026 — MojoAuth

App / ORM / API

• TypeScript ORMs deep dive 2026 — Chaos and Order
• Drizzle zero-downtime migrations — DEV
• REST vs GraphQL vs tRPC 2026 — Pockit
• Hono OpenAPI integration — DEV
• Zod → OpenAPI generation — Speakeasy
• openapi-zod-client GitHub

MCP

• Auth0: MCP Streamable HTTP simplifies security
• 2026-07-28 MCP spec release candidate — MCP blog
• MCP server security best practices 2026 — Digital Applied
• MCP Security 75-point checklist — Digital Applied
• MCP audit logs — Maxim
• Crystal DBA postgres-mcp — GitHub

Deploy / hosting

• Self-hosted deploy tools 2026 — Haloy
• Coolify vs Dokploy 2026 — Introserv
• Cloudflare Containers GA — Cloudflare changelog
• Cloudflare Containers overview (Postgres not suitable) — Cloudflare docs
• Self-hosting in 2026 — DEV

Sharing

• Notion no native password protection — Papermark
• Notion expiration rules — Metomic

Schema evolution

• JSONB anti-patterns — DanLevy
• JSONB hybrid + generated columns — Heap.io
• Zero-downtime JSONB migration — Medium Jai Garg

Claude Code guardrails + non-tech workflow

• Claude Code auto mode launch — TechBuzz
• Configure safety guardrails for Claude Code — NextWork
• Rulebricks Claude Code guardrails — GitHub
• Dwarvesf Claude guardrails — GitHub
• AI Agent Production Guardrails (incl. PocketOS post-mortem) — Lushbinary
• Securing Claude Code in Production — Maxim AI
• Vibe coding security debt crisis (react-codeshift hallucination) — Towards Data Science
• Secure vibe coding 2026 — Medium Sneh Bavarva
• Addy Osmani LLM workflow 2026 — Substack

galaxy-docs (Obsidian-compatible)

• Obsidian internal links + wikilinks spec — DeepWiki
• Obsidian linking + backlinks — Obsibrain
• Wikilinks in YAML frontmatter — Obsidian Forum
• Docmost self-hosted wiki on Postgres — Railway
• 10+ Open Source Obsidian alternatives — OpenAlternative
• remark-wiki-link — GitHub landakram
• Goldmark wikilinks (Go reference impl) — GitHub dangoor
• Obsidian Local REST API plugin — GitHub coddingtonbear
• Self-hosted Obsidian sync via CouchDB LiveSync — Medium abhirajsinghtomar
• Obsidian vs Logseq vs Foam vs Dendron 2026 — Glukhov