Context
PulseBox is introducing Automata — the platform's automation and integration layer. This issue covers the data model foundation and navigation updates required before any screens can be built.
Navigation Decision
Counter-proposal on merging Applications + Automata:
Keep them separate — they serve opposite directions:
- Applications (renamed to API Access) = inbound — external systems calling PulseBox API (app_id/secret credentials)
- Automata = outbound — PulseBox calling external systems on schedule/trigger
Rename "Applications" → "API Access" in label only (no URL change, no data migration). Add Automata as a new entry.
New Integration section order:
```
Integration
├── API Access /dashboard/apps (renamed, same route)
├── App Store /dashboard/studio/app-store
├── Automata /dashboard/studio/automata ← NEW
└── Webhooks /dashboard/webhooks
```
DB Migrations Required
1. `platform_apps` (Next Novas publishes)
```sql
create table platform_apps (
id uuid primary key default gen_random_uuid(),
slug text not null unique, -- e.g. "kfc.legacy-emp-import"
name text not null, -- e.g. "KFC EMP Import"
description text,
icon text,
type text not null default 'n8n_workflow', -- n8n_workflow | collection_bundle
visibility text not null default 'public', -- public | tenant_specific
allowed_tenant_ids uuid[], -- only for tenant_specific
config_schema jsonb not null default '{}', -- field definitions for install wizard
n8n_workflow_json jsonb, -- canonical workflow definition (source of truth)
n8n_template_workflow_id text, -- reference in n8n (for duplication)
version text not null default '1.0.0',
published_at timestamptz,
published_by uuid references auth.users(id),
created_at timestamptz default now(),
updated_at timestamptz default now()
);
```
2. `tenant_installed_apps` (tenant installs)
```sql
create table tenant_installed_apps (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references tenants(id) on delete cascade,
app_id uuid not null references platform_apps(id),
installed_at timestamptz default now(),
installed_by_user_id uuid references auth.users(id),
config jsonb not null default '{}', -- non-sensitive config values
n8n_workflow_id text, -- cloned workflow ID in n8n
access_policy jsonb not null default '{}', -- { view_definition: [...], view_logs: [...] }
enabled boolean not null default true,
unique(tenant_id, app_id)
);
```
3. `tenant_app_credentials` (credential pointers — never stores values)
```sql
create table tenant_app_credentials (
id uuid primary key default gen_random_uuid(),
tenant_installed_app_id uuid not null references tenant_installed_apps(id) on delete cascade,
credential_key text not null, -- e.g. "sftp", "api_secret"
n8n_credential_id text not null, -- ID in n8n vault — actual value never stored here
last_updated_at timestamptz default now(),
unique(tenant_installed_app_id, credential_key)
);
```
4. `integration_job_runs` (execution summary — lightweight)
```sql
create table integration_job_runs (
id uuid primary key default gen_random_uuid(),
tenant_installed_app_id uuid not null references tenant_installed_apps(id) on delete cascade,
n8n_execution_id text,
triggered_at timestamptz not null,
completed_at timestamptz,
status text not null, -- success | partial | failed | aborted | running
summary jsonb, -- { total_rows, success_count, error_count, ... }
created_at timestamptz default now()
);
```
5. `integration_job_errors` (row-level errors for traceability)
```sql
create table integration_job_errors (
id uuid primary key default gen_random_uuid(),
run_id uuid not null references integration_job_runs(id) on delete cascade,
row_number int,
source_data jsonb, -- raw input row
error_code text,
error_message text,
resolved_at timestamptz,
resolved_by uuid references auth.users(id),
resolution_note text,
created_at timestamptz default now()
);
```
Tasks
Security Notes
- `N8N_API_KEY` is Railway env var, server-side only, never `NEXT_PUBLIC_`
- All n8n API calls go through server actions / API routes
- Tenant users never receive n8n credential IDs or workflow IDs directly from client
- `tenant_app_credentials` stores only the n8n credential ID — actual secret lives in n8n vault
Acceptance Criteria
Context
PulseBox is introducing Automata — the platform's automation and integration layer. This issue covers the data model foundation and navigation updates required before any screens can be built.
Navigation Decision
Counter-proposal on merging Applications + Automata:
Keep them separate — they serve opposite directions:
Rename "Applications" → "API Access" in label only (no URL change, no data migration). Add Automata as a new entry.
New Integration section order:
```
Integration
├── API Access /dashboard/apps (renamed, same route)
├── App Store /dashboard/studio/app-store
├── Automata /dashboard/studio/automata ← NEW
└── Webhooks /dashboard/webhooks
```
DB Migrations Required
1. `platform_apps` (Next Novas publishes)
```sql
create table platform_apps (
id uuid primary key default gen_random_uuid(),
slug text not null unique, -- e.g. "kfc.legacy-emp-import"
name text not null, -- e.g. "KFC EMP Import"
description text,
icon text,
type text not null default 'n8n_workflow', -- n8n_workflow | collection_bundle
visibility text not null default 'public', -- public | tenant_specific
allowed_tenant_ids uuid[], -- only for tenant_specific
config_schema jsonb not null default '{}', -- field definitions for install wizard
n8n_workflow_json jsonb, -- canonical workflow definition (source of truth)
n8n_template_workflow_id text, -- reference in n8n (for duplication)
version text not null default '1.0.0',
published_at timestamptz,
published_by uuid references auth.users(id),
created_at timestamptz default now(),
updated_at timestamptz default now()
);
```
2. `tenant_installed_apps` (tenant installs)
```sql
create table tenant_installed_apps (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references tenants(id) on delete cascade,
app_id uuid not null references platform_apps(id),
installed_at timestamptz default now(),
installed_by_user_id uuid references auth.users(id),
config jsonb not null default '{}', -- non-sensitive config values
n8n_workflow_id text, -- cloned workflow ID in n8n
access_policy jsonb not null default '{}', -- { view_definition: [...], view_logs: [...] }
enabled boolean not null default true,
unique(tenant_id, app_id)
);
```
3. `tenant_app_credentials` (credential pointers — never stores values)
```sql
create table tenant_app_credentials (
id uuid primary key default gen_random_uuid(),
tenant_installed_app_id uuid not null references tenant_installed_apps(id) on delete cascade,
credential_key text not null, -- e.g. "sftp", "api_secret"
n8n_credential_id text not null, -- ID in n8n vault — actual value never stored here
last_updated_at timestamptz default now(),
unique(tenant_installed_app_id, credential_key)
);
```
4. `integration_job_runs` (execution summary — lightweight)
```sql
create table integration_job_runs (
id uuid primary key default gen_random_uuid(),
tenant_installed_app_id uuid not null references tenant_installed_apps(id) on delete cascade,
n8n_execution_id text,
triggered_at timestamptz not null,
completed_at timestamptz,
status text not null, -- success | partial | failed | aborted | running
summary jsonb, -- { total_rows, success_count, error_count, ... }
created_at timestamptz default now()
);
```
5. `integration_job_errors` (row-level errors for traceability)
```sql
create table integration_job_errors (
id uuid primary key default gen_random_uuid(),
run_id uuid not null references integration_job_runs(id) on delete cascade,
row_number int,
source_data jsonb, -- raw input row
error_code text,
error_message text,
resolved_at timestamptz,
resolved_by uuid references auth.users(id),
resolution_note text,
created_at timestamptz default now()
);
```
Tasks
Security Notes
Acceptance Criteria