FD Ticket System

What it does

“FD” stands for Fixed Departure — these are pre-purchased airline tickets with set departure dates and prices that Shanvi Travels makes available for purchase. Agents upload ticket inventory; registered users search and book; admin/HQ approves the listings.

Lifecycle

1. Agent uploads ticket → status: PENDING
   └── POST /api/tickets
   └── Requires: TICKETING system permission or ADMIN/HEAD_OFFICE

2. Admin/HQ reviews and approves → status: APPROVED
   └── PUT /api/tickets/:id/status { status: "APPROVED" }
   └── Auto-adds airport entries to DB
   └── Auto-adds date to available_dates table (legacy)

3. Approved tickets appear in public search
   └── GET /api/tickets/search?from=KTM&to=BKK&date=2025-06-15

4. Registered user books seats
   └── POST /api/tickets/:id/book
   └── Seat count decremented atomically
   └── Booking created with status: PENDING

5. Admin/HQ confirms or cancels booking
   └── PUT /api/tickets/bookings/:id/status
   └── If cancelled → seats restored atomically

6. User submits payment receipt
   └── PUT /api/tickets/bookings/:id/payment { transactionId }

Ticket ID obfuscation

Ticket IDs are not exposed as raw integers. The id column is encrypted using an RC4 cipher before being sent to the frontend, and decrypted on every inbound request. This prevents sequential enumeration of tickets.

// Encrypt before sending to client
formatTicketResponse(ticket) → ticket.id = encryptId(ticket.id)

// Decrypt when receiving from client
const ticketId = decryptId(req.params.id);

The encryption key is derived from JWT_SECRET. Since that secret is currently compromised (hardcoded in wrangler.toml), the obfuscation is also ineffective until the secret is rotated.

Ticket types

saleType	Description
Direct	Non-stop flight
Transit	One or more layovers, each with its own flight leg

Transit tickets carry a transitDetails JSON array describing each leg:

[
  { "from": "Kathmandu (KTM)", "to": "Doha (DOH)", "fromAirport": "KTM", "toAirport": "DOH", ... },
  { "from": "Doha (DOH)", "to": "Bangkok (BKK)", "fromAirport": "DOH", "toAirport": "BKK", ... }
]

Pricing: commission model

When an agent uploads a ticket, their maxCommission (percentage) is applied:

baseFare    = originalFare (agent's price)
commission  = baseFare × (maxCommission / 100)
finalFare   = baseFare + commission  ← stored as originalFare in DB

The baseFare column stores the pre-commission price for reference. The displayed price to users is originalFare (which includes commission).

Booking seat atomicity

The booking flow uses an atomic SQL update to prevent overbooking under concurrent requests:

UPDATE fd_tickets
SET availableTickets = availableTickets - ?
WHERE id = ? AND status = "APPROVED" AND availableTickets >= ?

If changes === 0, the seats are gone and the booking is rejected. If the booking DB insert fails after the decrement, the seats are restored in a rollback step.

4-hour departure deadline

Tickets cannot be booked less than 4 hours before the scheduled departure. This is enforced server-side:

const diffHrs = (flightDate - now) / (1000 * 60 * 60);
if (diffHrs < 4) return 400 error;

Tickets more than 4 hours away are also filtered out of search results.

Trip types

tripType	Description
One Way	Single direction flight
Round Trip	Return flight stored in return* columns

API endpoints

Method	Path	Auth	Description
GET	/api/tickets	Token	List tickets (scoped by role)
GET	/api/tickets/search	None	Public flight search
GET	/api/tickets/destinations	None	Active airport list
GET	/api/tickets/:id	None/Token	Get single ticket
POST	/api/tickets	Token + permission	Upload new ticket
PUT	/api/tickets/:id	Token + permission	Edit ticket
PUT	/api/tickets/:id/status	Token + permission	Approve/reject
POST	/api/tickets/:id/book	Token (USER)	Book seats
GET	/api/tickets/bookings/my	Token	User’s own bookings
GET	/api/tickets/bookings/all	Token	All bookings (admin/agent)
PUT	/api/tickets/bookings/:id/status	ADMIN/HQ	Confirm/cancel booking
PUT	/api/tickets/bookings/:id/payment	Token	Submit payment receipt

Known issues

• Transit detail normalization is duplicated — identical airport lookup + JSON normalization logic appears in both POST / and PUT /:id. Extract to a shared helper.
• INSERT IGNORE INTO available_dates on approval is a MySQL-ism that silently fails on D1/SQLite. This table is also not actively used by the search logic.