kwenter/TECH_ARCHITECTURE.md

Technical Architecture Blueprint

A modern, AI-native low-code platform that can take over from Axpert. It can adopt or migrate existing Axpert customers.

• Status: Draft v1 (2026-09-25)
• Related: AI_LAYER_DESIGN_NOTES.md covers the event/action model, the AI agent and the audit engine.
• Test customer: krishtech (PostgreSQL 16, about 1,194 tables, 358 forms, 378 reports).

---

0. Decisions made

#	Decision	Choice
D1	Main language	TypeScript for the platform, UI, API, runtime and AI agent
D2	Second language	Go for high-volume data work: migration data mover, attachment extraction, test-data anonymizer, large exports
D3	Database	PostgreSQL 16+, one schema per tenant
D4	Business data model	Real relational tables, never JSON blobs. Existing Axpert tables are adopted in place first.
D5	Axpert compatibility	A core feature: importer, expression parity and migration verification are built from phase 1
D6	Hosting	VPS running Docker Compose. Split onto more servers as load grows.
D7	Architecture style	Modular monolith (one TS API + one TS worker + Go services). No microservices yet.
D8	Clean room	We rebuild the concepts only. No Axpert code, SQL, CSS or assets are copied.

1. System overview

                         ┌────────────── Browser ───────────────┐
                         │ React app: shell · form runtime ·    │
                         │ reports · designer · AI chat · admin │
                         └───────────────┬──────────────────────┘
                                         │ HTTPS
                                   ┌─────▼─────┐
                                   │   Caddy   │  TLS, reverse proxy
                                   └─────┬─────┘
             ┌───────────────┬───────────┼────────────────┬──────────────┐
             ▼               ▼           ▼                ▼              ▼
        ┌─────────┐    ┌──────────┐  ┌────────┐     ┌──────────┐   ┌──────────┐
        │ web     │    │ api (TS) │  │Keycloak│     │ worker   │   │ migrator │
        │ static  │    │ Fastify  │  │  SSO   │     │ (TS)     │   │ (Go)     │
        └─────────┘    └────┬─────┘  └────────┘     │ pg-boss  │   │ internal │
                            │                       └────┬─────┘   └────┬─────┘
             ┌──────────────┼──────────────────────────┬─┴──────────────┘
             ▼              ▼                          ▼
      ┌────────────┐  ┌───────────┐             ┌────────────┐
      │ PostgreSQL │  │  Valkey   │             │   MinIO    │
      │ (all data) │  │ (cache)   │             │ (files/S3) │
      └────────────┘  └───────────┘             └────────────┘
                   ▲
       (read-only) │  source Axpert DB during migration (Postgres / Oracle / MSSQL)

2. Tech stack

Area	Choice	Notes
Monorepo	pnpm workspaces + Turborepo; Go modules under /go	One repo, shared CI
Frontend	React 19 + Vite, TanStack Router and Query, Tailwind + shadcn/ui	Single-page app; no iframes
Form state	Zustand store per open form, keyed by {dc, row, field}	Replaces Axpert's DOM-ID data model
Grids	AG Grid Community (inline editing), TanStack Table (read-only lists)
Designer	dnd-kit / gridstack (layout), Monaco (expressions/SQL), React Flow (workflow)
Charts	ECharts
i18n	i18next, including RTL	Axpert supports RTL and multiple languages
API	Fastify + Zod + zod-to-openapi	REST and OpenAPI, which also serve as the public integration API
DB access (TS)	Kysely + pg	Typed, dynamic SQL for tables created at run time
DB access (Go)	pgx (COPY protocol for bulk)
Validation	Zod, exported to JSON Schema	The same schema validates AI agent output
Expression engine	Hand-written Pratt parser → syntax tree → evaluator (TS)	Runs in both browser and server
Jobs	pg-boss (inside Postgres)	Email, print, import/export, scheduled jobs, workflow deadlines
Auth	Keycloak (OIDC and SAML, LDAP/AD, Google, O365, Okta)	Custom user-storage plugin for Axpert password hashes
Files	MinIO (S3-compatible)	Swappable for any S3 provider
PDF	Playwright (HTML → PDF)	Print formats are HTML templates
AI	Claude API through the Anthropic TS SDK, with tool use	Tools: read_metadata, propose_definition, dry_run_query, …
SQL translation	sqlglot (Python helper, migration tool only)	Converts Oracle/MSSQL SQL to Postgres
Observability	OpenTelemetry → Grafana stack (Loki, Tempo, Prometheus), Sentry
Testing	Vitest, Playwright end-to-end, Go test, Axpert parity suite (§8)
CI/CD	GitHub Actions → build images → push to registry → deploy over SSH

3. Repository layout

/apps
  web/                 React app (runtime + designer + admin + AI chat)
  api/                 Fastify API (modular monolith)
  worker/              pg-boss job runner (same modules as api, different entrypoint)
  cli/                 `plat` CLI: migrate, tenant, publish, parity
/packages
  schema/              Zod metadata schema (forms, reports, pages, workflow, events)
  expr/                expression engine: lexer, parser, evaluator, function library
  actions/             event/action model + action runner interface
  depgraph/            field dependency graph builder and resolver
  query/               query compiler (LOOKUP → SQL), SQL safety checker
  axpert-compat/       Axpert XML/JSON decoders, converters, compatibility report
  ui/                  shared React components (field widgets, grid, layout)
  sdk/                 typed API client (used by web + external integrations)
/go
  migrator/            bulk data mover, attachment extractor, row verifier
  anonymizer/          produce safe dev copies of customer databases
/infra
  compose/             docker-compose.yml (+ overrides per environment)
  caddy/  keycloak/  grafana/  backups/
/docs

4. Runtime modules (inside apps/api)

Module	Responsibility
metadata	Store definitions (draft/published), validate, compile (resolve dependencies, pre-parse expressions), cache in Valkey
forms	Load a new or existing record, handle dependency requests, save/delete/cancel, record locking (optimistic, via a version column)
query	LOOKUP compiler, allow-listed named SQL, parameter binding (:field, {field*}), dialect adapter
reports	Report execution (parameters, paging, totals, grouping, pivot), export (large exports go to the Go migrator/exporter)
workflow	State machine per form: approve/reject/return/review/forward, delegation, deadlines
security	Access per role, responsibility, form, field and button; sets the context for row-level security (RLS)
audit	Captures changes inside the same transaction; policy per form
files	Upload/download via presigned URLs, attachment metadata
ddl	Definition → table/column changes (preview + apply) for native forms
agent	AI conversation, tools, proposal → validate → preview → publish
migration	Discover/convert/report/verify/cutover; drives the Go migrator
admin	Tenants, users, settings, licences

How modules talk: they call each other in-process through typed interfaces. Side effects are sent as pg-boss jobs using the outbox pattern: the job is written in the same transaction as the business data, so nothing is lost.

5. Database design, layer by layer

PostgreSQL cluster
├── platform        tenants, apps, plans/licences, global settings
├── identity        users, roles, responsibilities, permissions, row rules, api keys
└── <tenant>  (e.g. krishtech — maps to Axpert "project")
    ├── meta        form_def, report_def, page_def, workflow_def, publish_log, customtypes
    ├── data        business tables (adopted Axpert tables OR generated native tables)
    ├── wf          wf_instance, wf_task, wf_comment, wf_delegation
    ├── audit       audit_transaction, audit_change (monthly partitions)
    ├── files       file (id, bucket, key, size, mime, owner form/record/field, …)
    ├── jobs        pg-boss tables
    └── mig         migration runs, mapping tables, verification results

Adopted mode: the customer's existing Axpert tables stay in their current schema, for example krishtech, and meta.form_def points at them. We add our schemas next to them and never change their tables without a migration step that has been previewed first.

5.1 Metadata (meta)

form_def (
  id uuid pk, key text, version int, status text check (status in ('draft','published','archived')),
  definition jsonb not null,            -- validated by @pkg/schema
  compiled jsonb,                       -- dependency graph, parsed expressions (cache source)
  source text,                          -- 'native' | 'axpert-import' | 'agent'
  source_ref text,                      -- e.g. Axpert transid
  checksum text, created_by, created_at, published_by, published_at,
  unique (key, version)
)
-- report_def, page_def, workflow_def follow the same pattern

5.2 Business data (data or the adopted schema)

• Native tables get these system columns: id uuid, created_at/by, updated_at/by, version int (optimistic lock), status, wf_state, tenant_id (only if tables are shared across tenants).
• Grid DC = a child table with parent_id, row_no and row_key uuid.
• Adopted Axpert tables keep Axpert's standard columns, which are mapped in the definition's columnMap:
  • <table>id numeric(16)
  • cancel, sourceid, mapname, username, modifiedon, createdby, createdon
  • wkid, app_level, app_desc, app_slevel, cancelremarks, wfroles
  • Grid tables also have <parent>id and <table>row.

5.3 Security

• Keycloak authenticates the user. The API issues a short-lived token.
• Every database transaction starts with SET LOCAL app.user_id = …, app.roles = ….
• Row-level rules (Axpert axpermissions view/edit conditions) become Postgres RLS policies, generated from the definition.
• Field-, button- and DC-level access is enforced in the forms module and hidden in the UI.
• Separate database roles:

Role	Used for
app_rw	The runtime
app_ro_agent	AI read queries
migrator	Migration, with source access read-only
owner	Running DDL; used only by the ddl module

5.4 Audit, workflow, files

See AI_LAYER_DESIGN_NOTES.md §5 for audit. Workflow deadlines are scheduled as pg-boss jobs. Files are stored in MinIO; files.file holds the metadata.

5.5 Cache (Valkey)

Compiled definitions (key includes the version), sessions, rate limits and lookup caches. Never rendered HTML and never open-form state, so any API instance can serve any request.

6. Key runtime flows

Open form → GET the compiled definition (from cache) → the client builds the Zustand store → form-load events run on the client, with server calls only for actions that need data.

Field change → @pkg/expr recalculates dependents instantly on the client. Server-backed dependents (LOOKUP/SQL/fill grid) go to POST /forms/:key/resolve with a delta, and the server returns the field values.

Save

auth → permission check → load compiled def → re-run expressions + validations (same engine)
BEGIN
  SET LOCAL app.* (RLS context)
  BEFORE_SAVE actions
  write header + grid rows (parameterized; optimistic lock on version)
  audit rows
  workflow transition
  outbox jobs (email, notifications, post-to-other-form)
  AFTER_SAVE actions
COMMIT → invalidate caches → return record + new version

7. Axpert migration and compatibility

7.1 Object mapping

Axpert object	Target	Automatic?
tstructs.props (zlib XML)	meta.form_def	Yes
ax_layoutdesign JSON	Layout section of the form definition	Yes
iviews / lviews XML	meta.report_def	Yes; SQL dialect checked
axpages	meta.page_def (menu)	Yes
Expressions and validations	@pkg/expr (same function names)	Yes; checked by the parity suite
<actions>, <formcontrol>	Event/action JSON	Mostly; server-only functions are re-implemented
Genmap / mdmap / fill grid	POST_TO_FORM / UPDATE_MASTER / FILL_GRID actions	Yes
Business tables	Adopted in place, or copied	Yes
Users, roles, permissions	identity + RLS	Yes; passwords via the legacy-hash plugin
Workflow definitions	meta.workflow_def	Classic yes; PEG needs review
Pending approvals (axactivetasks, *workflow)	wf.wf_task	Yes, at cutover
*history tables	audit.audit_change	Yes
*attach (bytea) + disk files	MinIO + files.file	Yes (Go migrator)
Stored functions (212 plpgsql in krishtech)	Kept unchanged	Yes
Drafts (Redis)	—	No; users finish drafts before cutover
Custom DLLs, JS hooks, custom HTML	—	No; flagged for manual rework

7.2 Modes

• A. Adopt in place: a Postgres source; no data copy. This is the first mode we build, with krishtech.
• B. Copy (ETL): Oracle/MSSQL sources, cleanup, or moving the customer onto our hosting. The Go migrator streams the data with COPY.

7.3 Pipeline

1. Discover: a read-only scan that produces the inventory.
2. Convert: definitions are created as drafts.
3. Compatibility report: converted/partial/manual per object, unsupported functions, SQL issues.
4. Dry run: load into a staging tenant, render every form, run every report.
5. Verify:
   • row counts and checksums
   • expression parity against real records
   • report output compared between Axpert and us
6. Cutover: freeze Axpert, final sync of changes, move pending tasks, switch users.
7. Rollback: take a snapshot before the cutover. In mode B the source is never modified.

It runs as a CLI (plat migrate discover|convert|report|verify|cutover) and as an admin wizard.

7.4 Passwords

Axpert appears to store MD5-based password hashes; this must be confirmed against axusers. Import the hash, check it on the first login through the Keycloak user-storage plugin, then re-hash with Argon2. No forced reset.

8. Testing strategy

• Axpert parity suite:
  • Extract every expression, validation and form-control rule from krishtech's 358 forms.
  • Evaluate them with @pkg/expr against real records.
  • Compare the results with Axpert's stored or computed values.
  • Runs in CI on every change to expr.
• Report parity: run each of the 378 reports with fixed parameters on both systems and compare the rows.
• Unit and property tests for the parser/evaluator.
• Playwright end-to-end: open, edit, save and approve on key krishtech forms.
• Migration verification (§7.3 step 5) also runs as a test in staging.

9. Customer data rules (krishtech)

krishtech is real customer data.

• Confirm in writing that the customer allows it to be used for development and testing.
• Developer machines get only anonymized copies, made by go/anonymizer. It masks names, phone numbers, emails, GSTIN/PAN, addresses and bank details, and keeps the data realistic.
• The real copy lives only on the staging VPS:
  • encrypted disk and backups
  • access over VPN/SSH only
  • no public demo URLs
• Host in India for Indian customers, to meet data-residency expectations under the DPDP Act.

10. VPS deployment

10.1 Environments

Env	Where	Data
dev	Laptop, docker compose	Anonymized krishtech
staging	VPS #1	Real krishtech copy (restricted access)
prod	VPS #2 (later a separate DB VPS)	Customers

10.2 Sizing

Stage	Setup
Staging / first customer	1 VPS: 8 vCPU, 32 GB RAM, 300+ GB NVMe
Growth	App VPS (API/worker/Keycloak/MinIO) + dedicated DB VPS (16–32 GB RAM, NVMe) + a read replica for reports
Scale	Several app VPSs behind a load balancer (the design allows it), managed or clustered Postgres, external S3

Providers with Indian regions: DigitalOcean (Bangalore), AWS Lightsail (Mumbai), E2E Networks, Hetzner (outside India, cheaper).

10.3 Services (docker compose)

caddy        TLS (auto Let's Encrypt), reverse proxy, static web files
api          Node 22, Fastify           (2 replicas possible)
worker       Node 22, pg-boss jobs
migrator     Go (internal only)
keycloak     + its own DB schema
postgres     16/17, tuned; pgBackRest for backups
valkey       cache
minio        files
grafana-stack  (optional on staging; Grafana Cloud free tier is an alternative)

10.4 Operations

• Backups:
  • pgBackRest keeps full and incremental backups plus the transaction log (WAL), so any point in time can be restored.
  • Copies go to off-server S3 storage (Backblaze B2 / Wasabi / another region).
  • MinIO is mirrored off-server.
  • Restore drills every month.
• Security:
  • SSH keys only, firewall (only 80/443 public), fail2ban.
  • Postgres not exposed publicly.
  • Admin tools only through WireGuard VPN.
  • Automatic security updates.
  • Secrets kept in .env files encrypted with SOPS.
• Deploy: GitHub Actions builds the images and pushes them to GHCR. It then deploys over SSH (docker compose pull && up -d) and runs DB migrations as a separate step.
• Monitoring: uptime check, disk/CPU/memory alerts, Postgres metrics, error alerts (Sentry).

11. Delivery phases

Phase	Deliverable	Done when
0. Foundations	Monorepo, CI, docker compose, VPS staging, anonymizer, krishtech restored on staging	Team can run everything locally with anonymized data
1. Core engine	schema, expr (Axpert function set), depgraph, axpert-compat decoders + form converter, parity suite	About 90% of krishtech forms convert, and the parity suite passes on their expressions
2. Form runtime	Read-only render → edit → save on adopted tables, grids, lookups, fill grid, locking, audit	Key krishtech transactions (e.g. PO, GRN, invoice) can be created and edited end-to-end
3. Reports + menu	Report engine, report converter, menu/pages, exports	Report parity passes for most krishtech reports
4. Security + workflow	Keycloak, legacy passwords, roles/permissions/RLS, workflow + migration of pending tasks	krishtech users log in with their existing passwords and approvals work
5. Migration toolkit	Full pipeline + wizard, Go migrator (copy mode, attachments), cutover runbook	A dry-run migration of krishtech with a clean verification report
6. Designer + AI	Form/report/workflow designer, AI agent (build app, lookups, reports, document-to-form)	A new form built from a sentence is published and works
7. Hardening	Performance, mobile/PWA, printing, jobs, i18n, docs	First production cutover

12. Open items

• Confirm the Axpert password hash format in axusers.
• List the Axpert server-only functions krishtech actually uses (from the parity extraction). That list sets the phase-2 scope.
• Decide the licensing and pricing model (it affects multi-tenant features in platform).
• Get written permission from krishtech to use its data for testing.