RPApos Gateway — Database Schema

PostgreSQL objects used by the RPApos gateway integration.

Document version: 1.0 · Last updated: 2026-05

Migrations

File	Purpose
`packages/backend/database/src/migrations/2026-05-09t10-00-00-add-rpapos-integration.mjs`	Core tables + enum + `product.is_raw_material`
`packages/backend/database/src/migrations/2026-05-10t11-00-00-add-rpapos-phase2-entities.mjs`	Enum extension: `mesa`, `persona`, `precio`, `produccion_grupo`
`packages/backend/database/src/migrations/2026-05-17t10-00-00-rpapos-soap-token.mjs`	SOAP device token + SAT connection metadata on `rpapos_connection`
`packages/backend/database/src/migrations/2026-05-17t12-00-00-rpapos-sat-credentials.mjs`	Optional `sat_usuario` / `sat_password_encrypted`
`packages/backend/database/src/migrations/2026-05-18t14-00-00-rpapos-modifier-entities.mjs`	Enum extension: `plu_option_group`, `plu_option_item`, `plu_option_group_assignment`

Target FlowPOS tables for those enum values: product_modifier_group, product_modifier, product_modifier_group_assignment. See Menus & Modifier Groups — All related tables.

Apply locally:

pnpm run migration:local:push
pnpm run generate:types

Enum: `rpapos_entity_type`

Phase 1 + Phase 2 + modifier values (planner uses the catalog subset):

plu, plu_grupo, sku, sku_grupo, batch_maestro, proveedor, unidad_medida,
moneda, tipo_gestion, area, bodega, usuario, estacion_trabajo, dispositivo_io,
compra, traslado, conteo_fisico, merma, salida_sku, produccion,
mesa, persona, precio, produccion_grupo,
plu_option_group, plu_option_item, plu_option_group_assignment

TypeScript mirror: RpaposEntityType in packages/global/enums/rpapos.enums.ts.

Table: `rpapos_connection`

One row per business + subdomain pair.

Column	Type	Notes
`id`	uuid PK
`business_id`	uuid	Multi-tenant scope
`subdomain`	text	Unique per business
`usuario`	text	RPApos login user
`password_encrypted`	text	`CryptoService`
`api_key_encrypted`	text	Optional; default `''`
`timezone`	text	Default `America/Guatemala`
`currency_code`	text	Default `GTQ`
`status`	text	`active`, `disconnected`, `error`
`sync_interval_minutes`	int	Nullable; scheduled sync hook
`tipo_gestion_map`	jsonb	RPA order type → FlowPOS order type
`last_synced_at`	timestamptz
`last_full_sync_at`	timestamptz
`last_error`	text	Set on sync failure
`sat_usuario`	text	Optional SAT login user (defaults to `usuario` when SAT password is set)
`sat_password_encrypted`	text	Optional SAT password
`soap_token_encrypted`	text	Device token from `Dispositivo_Registra_2` (`pToken` on db4 SOAP)
`localizacion_id`	smallint	From SAT registration
`conexion_db`	integer	From SAT registration
`conexion_mainframe`	integer	From SAT registration
`estacion_trabajo`	smallint	Workstation id — required for `Catalogo_4` / `Producto_U_M`
`created_by`	uuid
`created_at` / `updated_at`	timestamptz

Constraint: UNIQUE (business_id, subdomain)

API exposes hasSoapToken: boolean (derived from soap_token_encrypted IS NOT NULL); never returns token plaintext.

Table: `rpapos_id_map`

Idempotency for all entity types.

Column	Type	Notes
`id`	uuid PK
`business_id`	uuid
`entity_type`	`rpapos_entity_type`
`external_id`	text	RPApos primary key / code as string
`flowpos_entity_id`	uuid	Target row in FlowPOS
`import_job_id`	uuid	Nullable; link to CSV/import job when applicable
`source_payload_hash`	text	Optional change detection
`last_synced_at`	timestamptz

Constraint: UNIQUE (business_id, entity_type, external_id)

Index: (business_id, entity_type)

Table: `rpapos_sync_run`

One row per migration/sync execution.

Column	Type	Notes
`id`	uuid PK	Returned as `syncRunId`
`business_id`	uuid
`connection_id`	uuid FK → `rpapos_connection` ON DELETE CASCADE
`mode`	text	`catalog`, `historical`, `incremental`, `custom`
`status`	text	`running`, `paused`, `completed`, `failed`, `cancelled`
`paused`	boolean	Pause flag (polled every 5s during run)
`per_entity_stats`	jsonb	Map of entity → `{ extracted, loaded, failed, status }`. May include synthetic key `product_images` when image import ran (not a PostgreSQL enum value)
`date_from` / `date_to`	timestamptz	Historical sync window (optional)
`started_at`	timestamptz
`completed_at`	timestamptz
`created_by`	uuid	Firebase user

Indexes: (connection_id, status), (business_id)

Table: `rpapos_extraction_archive`

Raw JSON snapshots for audit and replay.

Column	Type	Notes
`id`	uuid PK
`business_id`	uuid
`sync_run_id`	uuid
`entity_type`	`rpapos_entity_type`
`record_count`	integer
`payload`	jsonb	Full extracted array
`extracted_at`	timestamptz

Index: (business_id, sync_run_id)

ALTER TABLE product
  ADD COLUMN is_raw_material boolean NOT NULL DEFAULT false;

PLU imports → is_raw_material = false
SKU imports → is_raw_material = true
Product image import → updates product.image_url (GCS path) for rows matched via rpapos_id_map (entity_type = plu)

Import job linkage (product images)

Image runs create rows in existing import tables:

Table	Usage
`import_job`	`import_type = 'product-image'`, `options.syncRunId` links to `rpapos_sync_run.id`
`import_row`	Per-PLU outcomes (success / error, `Codigo_PLU`, `objeto_archivo`)

Resolve from a sync run: GET /rpapos/sync-runs/:id/product-image-import-job?businessId=...

Kysely types

After migration, regenerate:

pnpm run generate:types

Generated table names use camelCase in TypeScript (e.g. rpaposConnection, rpaposIdMap).

Repositories

Table	Repository
`rpapos_connection`	`rpapos-connection.repository.ts`
`rpapos_sync_run`	`sync-run.repository.ts`
`rpapos_id_map`	`id-map.repository.ts`
`rpapos_extraction_archive`	`extraction-archive.repository.ts`

Migrations​

Enum: rpapos_entity_type​

Table: rpapos_connection​

Table: rpapos_id_map​

Table: rpapos_sync_run​

Table: rpapos_extraction_archive​

Related product schema​

Import job linkage (product images)​

Kysely types​

Repositories​