Building a Conflict-Safe AI Booking Platform with Gemini and PostgreSQL

1// Advisory lock: only one booking operation runs at a time
2// Lock ID 778723 is arbitrary but consistent across all instances
3async function createBookingWithLock(details: BookingDetails) {
4  const client = await pool.connect()
5  try {
6    await client.query('SELECT pg_advisory_lock(778723)')
7
8    // Now we're the only process that can create bookings
9    const conflict = await client.query(
10      `SELECT ref FROM bookings
11       WHERE date = $1 AND cancelled = false
12       AND (
13         (time >= $2 AND time < $3) OR
14         (time < $2 AND time::time + interval '3 hours' > $2::time)
15       )`,
16      [details.date, details.time, endTime]
17    )
18
19    if (conflict.rows.length > 0) {
20      throw new Error(`Slot unavailable — conflict with booking ${conflict.rows[0].ref}`)
21    }
22
23    const ref = `GT-${crypto.randomBytes(4).toString('hex').toUpperCase()}`
24    await client.query(INSERT_BOOKING_SQL, [...details, ref])
25    return ref
26
27  } finally {
28    await client.query('SELECT pg_advisory_unlock(778723)')
29    client.release()
30  }
31}

1// Synchronous fare calculation — no async, no database round-trip
2function calculateFare(pickup: string, dropoff: string): number {
3  const iata = dropoff.match(/\([A-Z]{3}\)/)?.[1]
4  if (!iata) throw new Error('No IATA code found in dropoff address')
5
6  const base = faresCache.baseFares[iata] ?? 0
7  const pickupLower = pickup.toLowerCase()
8
9  // Zone adjustments sorted longest-first to avoid "kirby" matching before "west kirby"
10  const adjustment = faresCache.zoneAdjustments
11    .sort((a, b) => b[0].length - a[0].length)
12    .find(([zone]) => pickupLower.includes(zone))?.[1] ?? 0
13
14  return base + adjustment
15}

1// Strip thought parts before persisting to chat_sessions
2function sanitizeHistory(contents: Content[]): Content[] {
3  return contents.map(c => ({
4    ...c,
5    parts: c.parts.filter(p => !('thoughtSignature' in p && p.thoughtSignature))
6  }))
7}
8
9// In the agentic loop:
10for (let i = 0; i < 5; i++) {
11  const stream = await ai.models.generateContentStream({
12    model: 'gemini-3.5-flash',
13    contents,  // already sanitized history
14    tools: TOOLS,
15    systemInstruction: buildSystemPrompt(),
16  })
17
18  let assistantParts: Part[] = []
19  for await (const chunk of stream) {
20    // Stream text to client via SSE
21    const text = chunk.candidates?.[0]?.content?.parts
22      ?.filter(p => p.text && !p.thoughtSignature)
23      ?.map(p => p.text).join('') ?? ''
24    if (text) res.write(`data: ${JSON.stringify({ text })}\n\n`)
25    assistantParts.push(...(chunk.candidates?.[0]?.content?.parts ?? []))
26  }
27
28  // Add to history (with sanitization)
29  contents.push({ role: 'model', parts: assistantParts })
30
31  const toolCalls = assistantParts.filter(p => p.functionCall)
32  if (toolCalls.length === 0) break  // No more tool calls — done
33
34  // Execute tools and add results
35  const toolResults = await Promise.all(toolCalls.map(executeToolCall))
36  contents.push({ role: 'user', parts: toolResults })
37}

1// /.well-known/agent.json — A2A agent card
2{
3  "name": "George Travel Booking Agent",
4  "description": "Books airport transfers for UK private driver service",
5  "url": "https://georgetravel.co.uk/a2a",
6  "version": "1.0",
7  "capabilities": {
8    "streaming": true,
9    "pushNotifications": false
10  },
11  "skills": [
12    {
13      "id": "book_transfer",
14      "name": "Book Airport Transfer",
15      "description": "Book a private driver transfer to/from UK airports",
16      "inputModes": ["text"],
17      "outputModes": ["text"]
18    }
19  ]
20}