Platform Overview & Role Architecture

This page is the single authoritative reference for how the platform is structured. It covers business context, the full role and permission model, every column in the users and agent_types tables, how auth tokens are built, known gaps in the current implementation, and what has been planned but not yet built. If you’re starting fresh or onboarding, read this before touching any code.

---

What Shanvi Travels is

Shanvi Travels is a B2B travel and visa operations platform — not a consumer booking site. External partner agents (travel agencies, educational consultancies) use it to serve their own customers. Shanvi’s internal staff use it to process those requests.

The three current services:

Service	Status	Description
Fixed Departure (FD) Ticketing	Live	Shanvi-managed flight inventory. Agents search, book, and reserve seats.
VFS Document Processing	Live	8-stage document pipeline from agent submission through VFS office and back.
Thai Visa Submissions	Live	End-to-end visa application management with payment and document tracking.

---

Platform vision and phases

The product is designed to evolve across three phases. The codebase is currently in Phase 1, with Phase 2 architecture being planned.

Phase 1 — Current: Shanvi as sole operator Shanvi manages all inventory and processing. Agents submit requests through the portal. No external seller access.

Phase 2 — Marketplace: FD inventory opens to external sellers Verified partner agencies can upload their own FD ticket inventory. Shanvi becomes the distribution layer. Multiple sellers compete; agents book from any seller. Shanvi earns a commission on each transaction.

Phase 3 — Ecosystem Commission management, agent digital wallets, credit systems, performance analytics, regional expansion.

---

Tech stack

Layer	Technology
Frontend	React (Vite), TypeScript
Backend	Node.js + Express, deployed as a Cloudflare Worker via @cloudflare/workers-types
Database (both environments)	Cloudflare D1 (SQLite dialect) — local dev via wrangler dev, production via Cloudflare
File storage (local dev)	Local disk (/uploads/) via multer
File storage (production)	Cloudflare R2 (S3-compatible)
Auth	JWT (access token) + JWT (refresh token), jsonwebtoken + bcryptjs
Hosting	Cloudflare Pages (frontend) + Cloudflare Workers (backend)
Docs	Astro Starlight SSG (/docs subdirectory in same repo)

D1 is used in both local development and production. Run wrangler dev locally — it provisions a local D1 SQLite file under .wrangler/state/v3/d1/ and injects D1_DB into globalThis exactly as it is in production. There is no MySQL dependency.

Note on config/db.js: The file still contains a MySQL pool fallback (for when globalThis.D1_DB is not set). This code is now dead — it will never run if you always use wrangler dev. It should be cleaned up, but it is harmless to leave in place for now.

---

Database abstraction (config/db.js)

db.run(sql, params, callback)   — INSERT / UPDATE / DELETE
db.get(sql, params, callback)   — SELECT returning one row
db.all(sql, params, callback)   — SELECT returning array

If globalThis.D1_DB exists → uses D1 bindings (Cloudflare Worker)
Otherwise → uses MySQL connection pool (local dev / traditional server)

Callback receives (err, result). For run, this.lastID and this.changes are available in the callback context (mirrored from sqlite3 convention for D1 compatibility).

Known issue: The db.run/get/all promise wrapper is copy-pasted into several route files rather than imported from a shared utility. See Build Backlog.

---

Users table

The users table is created by the database on first startup. Additional columns are added via ALTER TABLE in ensureUserColumnsExist() (called by index.js on every startup).

Core columns (created at table init)

Column	Type	Values / Notes
id	INT, PK, AUTO_INCREMENT	—
username	VARCHAR, UNIQUE	3–20 alphanumeric + underscore
email	VARCHAR, UNIQUE	lowercase-normalized on save
password	VARCHAR	bcrypt hash, 10 rounds
role	ENUM/TEXT	USER | AGENT | ADMIN
agentType	TEXT	Name string (FK bug — see below)
status	TEXT	ACTIVE | PENDING | SUSPENDED | DEACTIVATED
createdAt	TIMESTAMP	Auto-set
kyc_status	TEXT	NOT_SUBMITTED | SUBMITTED | APPROVED | REJECTED (inferred from admin usage)
kyc_location	TEXT	Location text from KYC step 1
kyc_registration_file	TEXT	URL to uploaded business registration doc
kyc_pan_file	TEXT	URL to uploaded PAN/tax doc
consultancy_name	TEXT	Business name from KYC step 1
consultancy_address	TEXT	Business address
consultancy_phone	TEXT	Primary mobile (required in KYC)
consultancy_tel	TEXT	Landline (optional)
consultancy_email	TEXT	Business email (optional, validated)

Extended columns (added by ensureUserColumnsExist migration)

These are added via ALTER TABLE … ADD COLUMN on startup. Safe to run repeatedly — errors for “duplicate column” are silently ignored.

Column	Type	Notes
mobileNumber	TEXT	Operational contact (separate from KYC phone)
mobileNumber2	TEXT	Secondary contact
landlineNumber	TEXT	Office landline
location	TEXT	Geographic location / branch
outletId	TEXT	Outlet or branch identifier
name	TEXT	Display name (separate from username)
maxCommission	INTEGER DEFAULT 0	Max commission percentage, 0–100
avatar	TEXT DEFAULT NULL	'boy' | 'girl' — two static avatar options

Columns planned but not yet added

Column	Type	Purpose
userTier	TEXT	INTERNAL | EXTERNAL — separates Shanvi staff from partner agents
agentTypeId	INT FK	Replaces agentType name string with FK to agent_types.id
trustLevel	TEXT	STANDARD | HIGH — controls FD upload approval workflow

---

Agent types table (agent_types)

Agent types are the primary RBAC mechanism for agents. Each agent is assigned one agent type; their permissions derive entirely from that type.

Schema

Column	Type	Notes
id	INT, PK, AUTO_INCREMENT	—
name	VARCHAR, UNIQUE	Display name — also used as lookup key in users.agentType
description	TEXT	Human-readable description
permissions	TEXT (JSON array)	Serialized array of permission strings
category	TEXT	Used as fallback when systems is empty
systems	TEXT (JSON array)	Added via ensureAgentTypeColumnsExist migration on startup
isActive	INTEGER (0/1)	Default 1 (active)

The systems column was added after initial table creation. It is added via ALTER TABLE agent_types ADD COLUMN systems TEXT DEFAULT NULL on startup, silently skipped if already present.

Current agent types in production

Name	Tier	Systems	Purpose
HEAD_OFFICE	Internal	VFS, TICKETING	Shanvi management — full access to all operations
DOCUMENT_RECEIVER	Internal	VFS	Receives incoming physical documents from couriers
DOCUMENT_VERIFIER	Internal	VFS	Additional document verification step
VFS Agent	Internal	VFS	Manages VFS pipeline stage transitions
Consultancy	External	VFS, TICKETING	Educational consultancies submitting visa applications for students
Travel Agent	External	TICKETING	Agencies booking FD tickets and travel services

Important: users.agentType stores the name string, not the ID. This means if an admin renames an agent type from Consultancy to Education Partner, all users assigned to Consultancy silently lose their permissions because the lookup by name returns nothing. Fix tracked in Build Backlog.

---

Permission strings

Permissions are stored as a JSON array in agent_types.permissions. At login, enrichUserData() fetches them and embeds them into the user response and token. Frontend uses them to conditionally render UI. Backend route files guard operations by checking req.user.permissions.

Known permissions

Permission	Used For
MANAGE_TICKETS	Create, edit, and manage FD tickets (Shanvi staff)
VIEW_ALL_TICKETS	View all FD tickets across all agents
CREATE_TASK	Create new VFS tracking tasks / submissions
VIEW_ALL_DOCUMENTS	View all documents in the VFS pipeline
DOCUMENT_RECEIVER	Mark a document as received at the Shanvi office (VFS Stage 2)
DOCUMENT_AT_SHANVI	Mark a document as physically at Shanvi (VFS Stage 3)
VFS_RECEIVED	Mark a document as received by VFS office (VFS Stage 4)
VFS_AFTER_SHANVI	Mark a document as back at Shanvi after VFS (VFS Stage 6)
CONSULTANCY_RECEIVED	Mark a document as received back by the consultancy (VFS Stage 7)
TASK_CLOSE	Close a VFS task after it completes all stages
REJECT_TASK	Reject / fail a VFS task

HEAD_OFFICE hardcoded fallback: If agent_types lookup fails for a user whose agentType is HEAD_OFFICE or HEAD OFFICE (case/underscore insensitive), the system falls back to a hardcoded full-permission set rather than returning empty permissions. This is a legacy safety net in enrichUserData(). External agents that fail lookup get empty permissions with no fallback.

Planned permissions

Permission	Purpose
UPLOAD_FD_INVENTORY	Grants FD seller capabilities to an agent — unlocks inventory management section

---

How permissions reach the frontend (enrichUserData)

enrichUserData(userData) in routes/auth.js is called every time a token is verified or a user logs in. It looks up agent_types by the user’s agentType name string and attaches permissions[] and systems[] to the user object.

Login / Verify flow:
  1. User authenticates (login or /verify)
  2. DB row fetched: id, username, email, role, agentType, status, etc.
  3. enrichUserData(rawUser) called
     a. If agentType is null → permissions: [], systems: []
     b. Else → SELECT permissions, systems, category FROM agent_types WHERE name = ?
        - If found → parse JSON from row, attach to user object
        - If not found AND agentType is HEAD_OFFICE → hardcoded fallback permissions
        - If not found AND agentType is anything else → permissions: [], systems: []
  4. Enriched user object returned in response body

Permissions are not embedded in the JWT itself — only id, username, email, role, and agentType are signed into the token. Permissions are always fetched live from the DB on each /verify call. This means permission changes take effect immediately on the next request without requiring a re-login.

---

JWT token system

Access token

Field	Value
Payload	{ id, username, email, role, agentType }
Secret	SECRET_KEY (from middleware/auth.js, sourced from JWT_SECRET env var)
Expiry	6h for AGENT and ADMIN roles; 30m for USER role

Refresh token

Field	Value
Payload	{ id, type: 'refresh' }
Secret	REFRESH_SECRET_KEY (from REFRESH_SECRET_KEY env var — must differ from JWT_SECRET in production)
Expiry	7d

Refresh tokens are used via POST /api/auth/refresh. They return a new access token. There is no token blacklist — logout is client-side only (delete tokens from storage). If a refresh token is compromised, there is no server-side revocation mechanism. This is a known gap.

Critical issue: Both JWT_SECRET and REFRESH_SECRET_KEY are currently hardcoded in backend/wrangler.toml in plaintext. These values are burned and must be rotated by moving them to Cloudflare Secrets (wrangler secret put). See Build Backlog.

---

Role system

Three roles

Role	Who	Access Level
USER	Anyone who registers	Default role. Can browse public content, track their own submissions, complete KYC.
AGENT	Verified business partners	Promoted by admin. Full B2B access: book tickets, submit visa applications, view B2B pricing. Permissions further defined by their assigned agent type. Also used for all Shanvi internal staff — this conflation is a known architectural problem (see below).
ADMIN	Shanvi admin staff	Full platform management. User CRUD, agent type management, KYC review, role assignment.

Admin guards (currently enforced)

• An admin cannot demote themselves (PUT /admin/users/:id/role blocks self-demotion)
• The last active admin cannot be demoted (query checks COUNT(*) WHERE role='ADMIN' AND status='ACTIVE')
• The last active admin cannot be deleted (same count check)
• The admin username account cannot be deleted (hardcoded guard on username=‘admin’)
• An admin cannot suspend or deactivate themselves (PUT /admin/users/:id/status)

---

User tiers — current vs. planned

Current (problematic): Both Shanvi internal staff and external partner agents share role = AGENT. The only distinction is agentType — internal staff have types like HEAD_OFFICE, DOCUMENT_RECEIVER, etc. This creates confusing permission checks throughout the codebase that have to string-match on agentType to know whether they’re dealing with a Shanvi employee or an external partner.

Planned fix: Add userTier TEXT column to users:

Tier	Who	Assigned to
INTERNAL	Shanvi employees	Staff accounts: HEAD_OFFICE, DOCUMENT_RECEIVER, VFS Agent, etc.
EXTERNAL	Partner businesses	Consultancy, Travel Agent, FD sellers

Until this column is added, code that needs to distinguish internal from external agents must check agentType name strings.

---

Registration and KYC flow

Step 1: Registration → USER

POST /api/auth/register
  Body: { username, email, password }

  Validates:
    - username: 3-20 alphanumeric + underscore, not reserved
      Reserved: admin, administrator, root, superuser, system, support
    - email: standard format
    - password: min 8 chars, at least one uppercase, lowercase, digit

  Creates: users row with role='USER', status='ACTIVE', kyc_status='NOT_SUBMITTED'
  Returns: JWT access token (30m), refresh token (7d), user object

Step 2: KYC submission → status SUBMITTED

KYC is a two-step process. Step 1 submits text info; Step 2 uploads files.

POST /api/auth/kyc-submit-info  (authenticated)
  Body: { location, consultancy_name, consultancy_address,
          consultancy_phone, consultancy_tel, consultancy_email }
  Effect: Updates users row with consultancy fields. kyc_status stays as-is.

POST /api/auth/kyc-submit-files  (authenticated, multipart)
  Files: registration_file, pan_file
  Effect: Saves files to storage, updates kyc_status='SUBMITTED',
          sets kyc_registration_file and kyc_pan_file URLs.

Step 3: Admin reviews KYC

Admin dashboard shows users with kyc_status='SUBMITTED'
Admin can:
  - Approve: PUT /api/admin/users/:id/role
      { role: 'AGENT', agentType: '<type>', kyc_status: 'APPROVED' }
  - Reject: POST /api/admin/users/:id/kyc-reject
      Resets kyc_status='NOT_SUBMITTED' so user can resubmit

Step 4 (optional): Grant FD seller permission

Not yet implemented in the codebase. Planned mechanism: admin grants UPLOAD_FD_INVENTORY permission via the agent type’s permission set or a dedicated user-level permission override.

Step 5 (optional): Trust level upgrade

Not yet implemented. Will be controlled by trustLevel column on users: STANDARD (uploads need admin approval) vs HIGH (uploads go live immediately).

---

Admin API routes

All admin routes require Authorization: Bearer <token> with role = ADMIN. The adminOnly middleware rejects any other role with 403.

Method	Path	Description
GET	/api/admin/users	List all users with full profile, KYC status and files
PUT	/api/admin/users/:id/status	Set user status: ACTIVE, PENDING, SUSPENDED, DEACTIVATED
PUT	/api/admin/users/:id/role	Promote / demote role, assign agentType, optionally set status and kyc_status
PUT	/api/admin/users/:id/reset-password	Force-reset a user’s password (same strength rules as registration)
POST	/api/admin/users/:id/kyc-reject	Reject KYC, reset to NOT_SUBMITTED
DELETE	/api/admin/users/:id	Delete user (guards: can’t delete self, last admin, or username=‘admin’)
GET	/api/admin/agent-types	List all agent types
POST	/api/admin/agent-types	Create new agent type
PUT	/api/admin/agent-types/:id	Update agent type (name, description, permissions, systems, isActive)
DELETE	/api/admin/agent-types/:id	Delete agent type

---

Auth API routes

Method	Path	Auth required	Description
POST	/api/auth/register	No	Create new USER account
POST	/api/auth/login	No	Authenticate, returns access + refresh tokens
POST	/api/auth/refresh	No (refresh token in body)	Exchange refresh token for new access token
GET	/api/auth/verify	Bearer token	Verify token, return enriched user object (permissions included)
GET	/api/auth/profile	Bearer token	Fetch current user’s full profile
PUT	/api/auth/profile	Bearer token	Update profile fields (email, password, contacts, avatar, etc.)
POST	/api/auth/kyc-submit-info	Bearer token	Submit KYC text fields
POST	/api/auth/kyc-submit-files	Bearer token	Upload KYC documents (registration + PAN)
POST	/api/auth/logout	—	No-op (client deletes tokens; no server blacklist)

---

FD seller model — permission, not role

An FD seller is an agent with the UPLOAD_FD_INVENTORY permission — not a separate top-level role. This is deliberate: an FD seller almost always also books tickets and submits visa applications. A separate role would require dual-account support.

How it works (planned — not yet in code)

1. User registers → USER
2. Admin promotes to AGENT with an appropriate agent type
3. Admin separately grants UPLOAD_FD_INVENTORY permission (via agent type or direct user-level override)
4. Agent’s dashboard shows an Inventory Management section

FD upload trust levels

Trust Level	Who	Upload behavior
STANDARD	New / unproven sellers	Upload → PENDING_REVIEW → Admin approves → Live
HIGH	Established, verified sellers	Upload → Live immediately

Admin promotes a seller from STANDARD to HIGH after a track record is established.

FD upload controls (planned)

Even HIGH trust sellers will be subject to:

• Duplicate detection — same route + date + airline check before insert (returns 409 on conflict)
• uploadedBy FK — fd_tickets.uploadedBy references users.id for full audit trail
• Expiry enforcement — tickets past departure date auto-delist
• Admin approval queue — admin view of all PENDING_REVIEW tickets from STANDARD sellers

---

Known architectural gaps

These are real problems in the current codebase, not just planned improvements. Be aware of them when writing new code.

1. agentType stored as name string

users.agentType contains a plain text name like "Consultancy". The lookup in enrichUserData() does WHERE name = ?. Renaming an agent type via the admin UI updates agent_types.name but does not update all the user rows pointing to that name. Those users silently lose all permissions on next login. Fix: migrate to agentTypeId INT FK.

2. Internal and external agents share role = AGENT

Shanvi staff (HEAD_OFFICE, DOCUMENT_RECEIVER, etc.) have the same database role as external partner consultancies. Any code that needs to distinguish them must do string matching on agentType, which is fragile. Fix: add userTier: INTERNAL | EXTERNAL column.

3. VFS status updates have no server-side permission enforcement

PUT /api/documents/:id/status accepts any authenticated agent setting any status. The frontend only shows relevant buttons per role, but the backend doesn’t enforce which agent types can set which statuses. The tickets.js file has a checkCanManageTickets() pattern that should be mirrored for VFS. Fix: add checkCanUpdateVfsStatus().

4. JWT secrets exposed in wrangler.toml

JWT_SECRET and REFRESH_SECRET_KEY are in plaintext in backend/wrangler.toml. Anyone with read access to the repo can forge tokens. Must move to wrangler secret put and regenerate both values immediately.

5. Rate limiting installed but not applied

express-rate-limit is a dependency but is not applied to /api/auth/login or /api/auth/register. These endpoints are open to brute-force attacks. The submit-payment fix already shows how to wire it up — the same pattern applies to auth endpoints.

6. File URLs broken in production

getFileUrl() returns /uploads/filename. This path works in local dev (Express serves the uploads/ directory as static). In the Cloudflare Worker environment, no local filesystem exists — all files are in R2. The URL must point to the R2 public domain. Fix: set bucket to public, configure custom domain, update getFileUrl.

7. Schema migrations scattered across route files

ALTER TABLE guards are embedded in ensureUserColumnsExist(), ensureAgentTypeColumnsExist(), initializeTicketsDb(), initializeSubmissionsDb() — each called from index.js on startup. There is no migration history, no rollback capability, and no way to know what version of the schema is deployed. Fix: adopt Drizzle ORM + Drizzle Kit for versioned migrations.

8. No staging environment

All changes deploy directly to production. There is no staging Worker, no staging D1 database, and no Cloudflare Pages preview-branch setup. Any bad deploy goes live immediately.

---

What needs to be built (summary)

Full details, file references, and status in the Build Backlog. Summary for reference:

Critical — fix before anything else:

1. Rotate JWT secrets — move from wrangler.toml to Cloudflare Secrets
2. Add server-side VFS permission enforcement on PUT /api/documents/:id/status
3. Apply express-rate-limit to /api/auth/login and /api/auth/register

Role architecture foundation (must ship together): 4. userTier column on users — INTERNAL | EXTERNAL 5. agentTypeId INT FK on users — replace name string 6. trustLevel column on users — STANDARD | HIGH 7. UPLOAD_FD_INVENTORY permission — new value in agent_types permission set 8. uploadedBy FK on fd_tickets — track which seller uploaded which ticket 9. SUPER_ADMIN role — manage other admins, full audit log access 10. FD upload approval queue — admin view for PENDING_REVIEW tickets 11. Duplicate detection on FD upload — check route + date + airline before insert

VFS tracking: 12. dispatch_batches + batch_documents tables and batch creation endpoint 13. Batch UI for Shanvi staff 14. vfsOutcome column on documents — APPROVED | REJECTED separate from pipeline status 15. Fix duplicate stage labels in VFS tracking UI

Infrastructure: 16. Fix R2 file URLs in production 17. Set up staging environment 18. Adopt Drizzle ORM for versioned migrations