nilesh.pandClaude Opus 4.8
4d3eb18a6c
Angular + Go + PostgreSQL sample with strict per-tenant isolation. - Backend: Go API (basic auth, tenant-scoped repository layer, pgx pool) - Frontend: Angular 22 SPA (search, checkout, bookings, dashboard) - Database: schema, seed data and demo bookings migrations - Tests: 311 across Go (integration), Angular (unit) and Playwright (e2e), plus a generated evidence report with PDF export - Security: whitebox VAPT report under security/ Secrets (.env, tokens, DB credentials) are git-ignored. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
122 lines
6.3 KiB
SQL
122 lines
6.3 KiB
SQL
-- Demo data for TravelHub. Safe to re-run: every insert is idempotent.
|
|
-- Flight departures are generated relative to now(), so seeded inventory is
|
|
-- always in the future no matter when you run this.
|
|
|
|
-- ---------------------------------------------------------------- tenants --
|
|
INSERT INTO tenants (slug, name) VALUES
|
|
('skyline', 'Skyline Travel'),
|
|
('globetrotter', 'Globetrotter Tours')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
-- ------------------------------------------------------------------ users --
|
|
-- Passwords are bcrypt hashes produced by pgcrypto's crypt()/gen_salt('bf'),
|
|
-- which golang.org/x/crypto/bcrypt verifies directly. Demo password for every
|
|
-- seeded account is: demo1234
|
|
INSERT INTO users (tenant_id, email, password_hash, full_name, role)
|
|
SELECT t.id, v.email, crypt('demo1234', gen_salt('bf')), v.full_name, v.role
|
|
FROM tenants t
|
|
JOIN (VALUES
|
|
('skyline', 'admin@skyline.test', 'Ava Chen', 'admin'),
|
|
('skyline', 'agent@skyline.test', 'Marcus Reid', 'agent'),
|
|
('globetrotter', 'admin@globetrotter.test', 'Priya Nair', 'admin'),
|
|
('globetrotter', 'agent@globetrotter.test', 'Tom Okafor', 'agent')
|
|
) AS v(slug, email, full_name, role) ON v.slug = t.slug
|
|
ON CONFLICT (tenant_id, email) DO NOTHING;
|
|
|
|
-- --------------------------------------------------------------- airports --
|
|
INSERT INTO airports (iata_code, name, city, country, timezone) VALUES
|
|
('JFK', 'John F. Kennedy International', 'New York', 'United States', 'America/New_York'),
|
|
('LAX', 'Los Angeles International', 'Los Angeles', 'United States', 'America/Los_Angeles'),
|
|
('SFO', 'San Francisco International', 'San Francisco', 'United States', 'America/Los_Angeles'),
|
|
('LHR', 'Heathrow', 'London', 'United Kingdom', 'Europe/London'),
|
|
('CDG', 'Charles de Gaulle', 'Paris', 'France', 'Europe/Paris'),
|
|
('FRA', 'Frankfurt am Main', 'Frankfurt', 'Germany', 'Europe/Berlin'),
|
|
('DXB', 'Dubai International', 'Dubai', 'United Arab Emirates', 'Asia/Dubai'),
|
|
('SIN', 'Changi', 'Singapore', 'Singapore', 'Asia/Singapore'),
|
|
('HND', 'Haneda', 'Tokyo', 'Japan', 'Asia/Tokyo'),
|
|
('SYD', 'Kingsford Smith', 'Sydney', 'Australia', 'Australia/Sydney'),
|
|
('BOM', 'Chhatrapati Shivaji Maharaj', 'Mumbai', 'India', 'Asia/Kolkata'),
|
|
('DEL', 'Indira Gandhi International', 'Delhi', 'India', 'Asia/Kolkata')
|
|
ON CONFLICT (iata_code) DO NOTHING;
|
|
|
|
-- --------------------------------------------------------------- airlines --
|
|
INSERT INTO airlines (iata_code, name) VALUES
|
|
('AA', 'American Airlines'),
|
|
('BA', 'British Airways'),
|
|
('EK', 'Emirates'),
|
|
('SQ', 'Singapore Airlines'),
|
|
('LH', 'Lufthansa'),
|
|
('AI', 'Air India'),
|
|
('UA', 'United Airlines'),
|
|
('AF', 'Air France')
|
|
ON CONFLICT (iata_code) DO NOTHING;
|
|
|
|
-- ---------------------------------------------------------------- flights --
|
|
-- Each tenant carries its own inventory of the same routes at its own fares:
|
|
-- Skyline sells at list price, Globetrotter at a 12% negotiated markup. This
|
|
-- is what makes tenant isolation visible in the UI -- same route, different
|
|
-- price and different availability depending on who is logged in.
|
|
WITH routes(airline, flight_no, origin, dest, dep_hour, dur_hours, cabin, price, seats) AS (
|
|
VALUES
|
|
('AA', 'AA100', 'JFK', 'LHR', 18, 7, 'economy', 540.00, 180),
|
|
('BA', 'BA178', 'JFK', 'LHR', 21, 7, 'business', 2450.00, 40),
|
|
('AA', 'AA136', 'LAX', 'JFK', 8, 5, 'economy', 310.00, 200),
|
|
('UA', 'UA523', 'SFO', 'JFK', 7, 5, 'economy', 295.00, 190),
|
|
('EK', 'EK202', 'JFK', 'DXB', 23, 12, 'economy', 780.00, 350),
|
|
('EK', 'EK506', 'DXB', 'BOM', 3, 3, 'economy', 210.00, 300),
|
|
('SQ', 'SQ321', 'SIN', 'LHR', 10, 13, 'business', 3100.00, 50),
|
|
('SQ', 'SQ001', 'SIN', 'SFO', 9, 15, 'economy', 890.00, 280),
|
|
('LH', 'LH400', 'FRA', 'JFK', 13, 8, 'economy', 495.00, 220),
|
|
('AF', 'AF006', 'CDG', 'JFK', 11, 8, 'premium_economy', 1150.00, 60),
|
|
('AI', 'AI101', 'DEL', 'JFK', 2, 15, 'economy', 720.00, 240),
|
|
('AI', 'AI864', 'BOM', 'DEL', 6, 2, 'economy', 85.00, 160),
|
|
('BA', 'BA015', 'LHR', 'SYD', 22, 22, 'economy', 1080.00, 260),
|
|
('LH', 'LH716', 'FRA', 'HND', 14, 11, 'economy', 640.00, 230)
|
|
),
|
|
priced AS (
|
|
SELECT t.id AS tenant_id,
|
|
al.id AS airline_id,
|
|
r.flight_no,
|
|
o.id AS origin_id,
|
|
d.id AS dest_id,
|
|
r.cabin,
|
|
r.seats,
|
|
day.n AS day_offset,
|
|
r.dur_hours,
|
|
-- Fares drift with departure date so search results look realistic.
|
|
ROUND(
|
|
(r.price * CASE t.slug WHEN 'globetrotter' THEN 1.12 ELSE 1.00 END)
|
|
* (1 + ((day.n % 7) - 3) * 0.035)
|
|
, 2) AS price,
|
|
date_trunc('day', now()) + (day.n || ' days')::interval
|
|
+ (r.dep_hour || ' hours')::interval AS departs
|
|
FROM routes r
|
|
CROSS JOIN generate_series(1, 21) AS day(n)
|
|
CROSS JOIN tenants t
|
|
JOIN airlines al ON al.iata_code = r.airline
|
|
JOIN airports o ON o.iata_code = r.origin
|
|
JOIN airports d ON d.iata_code = r.dest
|
|
)
|
|
INSERT INTO flights (
|
|
tenant_id, airline_id, flight_number, origin_airport_id, destination_airport_id,
|
|
departure_time, arrival_time, aircraft, cabin_class,
|
|
base_price, currency, seats_total, seats_available
|
|
)
|
|
SELECT p.tenant_id, p.airline_id, p.flight_no, p.origin_id, p.dest_id,
|
|
p.departs,
|
|
p.departs + (p.dur_hours || ' hours')::interval,
|
|
'Boeing 787-9',
|
|
p.cabin,
|
|
p.price,
|
|
'USD',
|
|
p.seats,
|
|
-- Nearer departures are fuller, so the UI shows varied availability.
|
|
GREATEST(1, p.seats - ((22 - p.day_offset) * 7) % (p.seats / 2))
|
|
FROM priced p
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM flights f
|
|
WHERE f.tenant_id = p.tenant_id
|
|
AND f.flight_number = p.flight_no
|
|
AND f.departure_time = p.departs
|
|
);
|