Data Model

This document defines the complete database schema for the HNS Ticketing System. Transactional entities are stored in PostgreSQL with Redis used for ephemeral state (cart reservations, queue positions).

Audit Log Storage

13 audit/log tables are stored in Grafana Loki as structured JSON logs rather than PostgreSQL. These tables are annotated below with their storage location. Only 3 log tables with transactional requirements remain in PostgreSQL. See Audit Logging Infrastructure for full details on ingestion, retention, and dashboards.

Entity Relationship Diagram

erDiagram
    User ||--o{ Order : places
    User ||--o{ SavedProfile : has
    User ||--o{ QueueEntry : joins
    User ||--o{ LoyaltyPoint : earns
    User ||--o{ PushToken : registers

    Match ||--o{ Order : for
    Match ||--o{ Ticket : issued_for
    Match ||--o{ SalesPhase : has
    Match ||--o{ MatchSector : configures
    Match ||--o{ Quota : allocated_to
    Match ||--o{ QueueEntry : queued_for

    Stadium ||--o{ Sector : contains
    Stadium ||--o{ Match : hosts

    Sector ||--o{ Seat : contains
    Sector ||--o{ MatchSector : configured_as

    Order ||--o{ Ticket : contains
    Order ||--o{ Payment : paid_by
    Order ||--o{ Refund : refunded_by

    Ticket ||--o{ TransferLog : transferred
    Ticket ||--o{ TicketHolder : assigned_to

    Quota ||--o{ QuotaSeat : allocates
    Quota ||--o{ Subquota : delegates_to

    Blacklist ||--o{ ViolationLog : blocks

---

Schema Reference

Naming Conventions

• Tables: snake_case plural (e.g., users, tickets, queue_entries)
• Columns: snake_case (e.g., created_at, user_id)
• Primary Keys: id (UUID v4)
• Foreign Keys: {table_singular}_id (e.g., user_id, match_id)
• Timestamps: {action}_at (e.g., created_at, updated_at, deleted_at)
• Enums: SCREAMING_SNAKE_CASE values

Data Types

Type	PostgreSQL	Description
UUID	UUID	Primary keys, foreign keys
String	VARCHAR(n)	Variable-length text with max
Text	TEXT	Unlimited text
Integer	INTEGER	Whole numbers
Decimal	DECIMAL(10,2)	Currency amounts
Boolean	BOOLEAN	True/false
Timestamp	TIMESTAMPTZ	Date/time with timezone
Date	DATE	Date without time
JSON	JSONB	Structured data
Array	UUID[], VARCHAR[]	PostgreSQL arrays
Encrypted	BYTEA	Encrypted sensitive data

---

E1: User & Profile Management

users

Primary user accounts (linked to Drupal identity).

Column	Type	Constraints	Description
id	UUID	PK	Internal user ID
drupal_user_id	VARCHAR(255)	UNIQUE, NOT NULL	Drupal identity reference
email	VARCHAR(255)	UNIQUE, NOT NULL	User email
status	user_status	NOT NULL, DEFAULT 'active'	Account status
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Registration time
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last update

Indexes: - idx_users_email UNIQUE on (email) - idx_users_drupal_id UNIQUE on (drupal_user_id)

Enum: user_status

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');

user_profiles

Ticketing-specific profile data (separate from Drupal).

Column	Type	Constraints	Description
user_id	UUID	PK, FK → users	One-to-one with user
full_name	VARCHAR(255)	NOT NULL	Legal name
date_of_birth	DATE	NOT NULL	Birth date
nationality	VARCHAR(100)	NOT NULL	Country code
oib	VARCHAR(11)	UNIQUE	Croatian personal ID (globally unique)
passport_number	BYTEA		Encrypted passport (non-Croatian)
phone	VARCHAR(50)		Phone number
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last update

Indexes: - idx_user_profiles_oib UNIQUE on (oib) WHERE oib IS NOT NULL

Constraints: - Either oib or passport_number must be set - OIB must pass checksum validation (ISO 7064, MOD 11-10)

saved_profiles

Frequently used ticket holder profiles for faster checkout.

Column	Type	Constraints	Description
id	UUID	PK	Profile ID
user_id	UUID	FK → users, NOT NULL	Owner
full_name	VARCHAR(255)	NOT NULL	Name
date_of_birth	DATE	NOT NULL	Birth date
nationality	VARCHAR(100)	NOT NULL	Country
oib_encrypted	BYTEA		Encrypted OIB
passport_encrypted	BYTEA		Encrypted passport
email	VARCHAR(255)		Ticket delivery email
phone	VARCHAR(50)		Phone
relationship	VARCHAR(100)		e.g., "Son", "Friend"
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

Indexes: - idx_saved_profiles_user on (user_id) - idx_saved_profiles_user_oib UNIQUE on (user_id, oib_encrypted) WHERE oib_encrypted IS NOT NULL

Constraints: - Max 10 saved profiles per user - OIB unique within user's profiles (not globally)

sessions

User authentication sessions.

Column	Type	Constraints	Description
id	UUID	PK	Session ID
user_id	UUID	FK → users, NOT NULL	Session owner
refresh_token_hash	VARCHAR(255)	NOT NULL	Hashed refresh token
device_id	VARCHAR(255)		Device identifier
expires_at	TIMESTAMPTZ	NOT NULL	Expiration time
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_sessions_user on (user_id) - idx_sessions_expires on (expires_at)

---

E2: Match & Stadium Management

stadiums

Stadium templates (reusable across matches).

Column	Type	Constraints	Description
id	UUID	PK	Stadium ID
name	VARCHAR(255)	NOT NULL	Stadium name
city	VARCHAR(100)	NOT NULL	City
country	VARCHAR(2)	NOT NULL, DEFAULT 'HR'	ISO country code
address	VARCHAR(500)		Full address
type	stadium_type	NOT NULL	HOME or AWAY
base_capacity	INTEGER	NOT NULL	Auto-calculated from sector seat counts (read-only)
is_numbered	BOOLEAN	NOT NULL, DEFAULT TRUE	Has numbered seats
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

Indexes: - idx_stadiums_type on (type) - idx_stadiums_country on (country)

Enum: stadium_type

CREATE TYPE stadium_type AS ENUM ('HOME', 'AWAY');
-- HOME: Pre-configured Croatian stadiums
-- AWAY: Away venue (created per match or reused)

sectors

Stadium sections/zones.

Column	Type	Constraints	Description
id	UUID	PK	Sector ID
stadium_id	UUID	FK → stadiums, NOT NULL	Parent stadium
name	VARCHAR(100)	NOT NULL	Sector name (e.g., "West A")
code	VARCHAR(20)	NOT NULL	Short code
capacity	INTEGER	NOT NULL	Total seats (calculated from seat map when present; manually entered for sectors without seat maps)
sector_type	sector_type	NOT NULL, DEFAULT 'STANDARD'	Type
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_sectors_stadium on (stadium_id) - idx_sectors_stadium_code UNIQUE on (stadium_id, code)

Enum: sector_type

CREATE TYPE sector_type AS ENUM ('STANDARD', 'VIP', 'ACCESSIBILITY', 'STANDING');

sector_seat_maps

Seat map configuration per sector.

Column	Type	Constraints	Description
id	UUID	PK	Map ID
sector_id	UUID	FK → sectors, NOT NULL	Parent sector
total_rows	INTEGER	NOT NULL	Number of rows
configuration_status	config_status	NOT NULL, DEFAULT 'DRAFT'	Status
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

Enum: config_status

CREATE TYPE config_status AS ENUM ('DRAFT', 'ACTIVE');

row_configurations

Row-level seat configuration.

Column	Type	Constraints	Description
id	UUID	PK	Config ID
sector_seat_map_id	UUID	FK → sector_seat_maps, NOT NULL	Parent map
row_identifier	VARCHAR(10)	NOT NULL	Row label (A, B, 1, 2)
seat_count	INTEGER	NOT NULL	Seats in row
start_seat_number	INTEGER	NOT NULL, DEFAULT 1	First seat number
numbering_direction	numbering_dir	NOT NULL, DEFAULT 'LTR'	Direction
has_gap	BOOLEAN	NOT NULL, DEFAULT FALSE	Has gap in numbering
gap_after_seat	INTEGER		Gap position
row_order	INTEGER	NOT NULL	Display order
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_row_configs_map on (sector_seat_map_id) - idx_row_configs_map_row UNIQUE on (sector_seat_map_id, row_identifier)

Enum: numbering_dir

CREATE TYPE numbering_dir AS ENUM ('LTR', 'RTL');

seats

Individual seats in a sector.

Column	Type	Constraints	Description
id	UUID	PK	Seat ID
sector_id	UUID	FK → sectors, NOT NULL	Parent sector
row_identifier	VARCHAR(10)	NOT NULL	Row (A, B, 1)
seat_number	VARCHAR(10)	NOT NULL	Seat number
seat_type	seat_type	NOT NULL, DEFAULT 'STANDARD'	Type
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_seats_sector on (sector_id) - idx_seats_sector_row_seat UNIQUE on (sector_id, row_identifier, seat_number)

Enum: seat_type

CREATE TYPE seat_type AS ENUM ('STANDARD', 'TECHNICAL', 'ACCESSIBILITY', 'COMPANION');

matches

Football matches/events.

Column	Type	Constraints	Description
id	UUID	PK	Match ID
home_team	VARCHAR(255)	NOT NULL	Home team name
away_team	VARCHAR(255)	NOT NULL	Away team name
competition	VARCHAR(255)	NOT NULL	Competition name
kick_off_time	TIMESTAMPTZ	NOT NULL	Match start time
venue_id	UUID	FK → stadiums, NOT NULL	Venue
match_type	match_type	NOT NULL	HOME or AWAY
is_numbered	BOOLEAN	NOT NULL, DEFAULT TRUE	Has seat-specific tickets
status	match_status	NOT NULL, DEFAULT 'DRAFT'	Match status
stadium_config_status	config_status	NOT NULL, DEFAULT 'DRAFT'	Stadium configuration status
~~queue_enabled~~	—	REMOVED	Queue is an auto-scaling microservice, not a per-match toggle
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated
published_at	TIMESTAMPTZ		When published
stadium_validated_at	TIMESTAMPTZ		Stadium config validated
stadium_locked_at	TIMESTAMPTZ		Stadium config locked
closed_at	TIMESTAMPTZ		When closed
closed_by	UUID	FK → users	Who closed
attendance_mode	attendance_mode		How attendance was recorded
no_attendance_reason	VARCHAR(50)		Reason if no attendance data
no_attendance_notes	TEXT		Additional notes
cancelled_at	TIMESTAMPTZ		When cancelled
cancelled_by	UUID	FK → users	Who cancelled
cancellation_reason	TEXT		Reason for cancellation

Indexes: - idx_matches_status on (status) - idx_matches_kickoff on (kick_off_time) - idx_matches_venue on (venue_id) - idx_matches_type on (match_type)

Enum: match_type

CREATE TYPE match_type AS ENUM ('HOME', 'AWAY');

Enum: match_status

CREATE TYPE match_status AS ENUM ('DRAFT', 'PUBLISHED', 'ACTIVE', 'CLOSED', 'CANCELLED');
-- DRAFT: Being configured
-- PUBLISHED: Visible to users, tickets on sale
-- ACTIVE: Match day (or close to it)
-- CLOSED: Match completed and closed
-- CANCELLED: Match cancelled

Enum: config_status

CREATE TYPE config_status AS ENUM ('DRAFT', 'VALIDATED', 'LOCKED');
-- DRAFT: Stadium being configured
-- VALIDATED: Configuration validated, ready to publish
-- LOCKED: Configuration locked (match on sale)

Enum: attendance_mode

CREATE TYPE attendance_mode AS ENUM ('WITH_DATA', 'WITHOUT_DATA');

sales_phases

Sales windows for a match.

Column	Type	Constraints	Description
id	UUID	PK	Phase ID
match_id	UUID	FK → matches, NOT NULL	Parent match
phase_type	phase_type	NOT NULL	Phase type
name	VARCHAR(100)	NOT NULL	Display name
start_at	TIMESTAMPTZ	NOT NULL	Phase start
end_at	TIMESTAMPTZ	NOT NULL	Phase end
eligibility_rules_json	JSONB		Eligibility criteria
ticket_limit	INTEGER	NOT NULL, DEFAULT 4	Max tickets per user
is_active	BOOLEAN	NOT NULL, DEFAULT TRUE	Active flag
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_sales_phases_match on (match_id) - idx_sales_phases_dates on (start_at, end_at)

Enum: phase_type

CREATE TYPE phase_type AS ENUM ('LOYALTY', 'GENERAL_PUBLIC', 'QUOTA_ONLY');

Constraints: - Phases cannot overlap for same match - end_at must be after start_at

price_categories

Match-specific price categories.

Column	Type	Constraints	Description
id	UUID	PK	Category ID
match_id	UUID	FK → matches, NOT NULL	Match
name	VARCHAR(50)	NOT NULL	Category name (e.g., "Category 1")
price	DECIMAL(10,2)	NOT NULL	Ticket price
currency	VARCHAR(3)	NOT NULL, DEFAULT 'EUR'	Currency
color	VARCHAR(7)		Hex color for map visualization
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_price_categories_match on (match_id) - idx_price_categories_match_name UNIQUE on (match_id, name)

match_sectors

Per-match sector configuration (pricing, availability, purpose).

Column	Type	Constraints	Description
id	UUID	PK	Config ID
match_id	UUID	FK → matches, NOT NULL	Parent match
sector_id	UUID	FK → sectors	Base sector (null for away-created sectors)
name	VARCHAR(100)	NOT NULL	Sector name
code	VARCHAR(20)		Sector code
total_capacity	INTEGER	NOT NULL	Total seats
technical_seats	INTEGER	NOT NULL, DEFAULT 0	Non-sellable (cameras, security)
sellable_capacity	INTEGER	GENERATED	Total - Technical
price_category_id	UUID	FK → price_categories	Price category
status	match_sector_status	NOT NULL, DEFAULT 'ACTIVE'	Availability
purpose	sector_purpose	NOT NULL, DEFAULT 'GENERAL'	Sector purpose
is_numbered	BOOLEAN	NOT NULL, DEFAULT TRUE	Has specific seats
has_seat_map	BOOLEAN	NOT NULL, DEFAULT FALSE	Seat map configured
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_match_sectors_match on (match_id) - idx_match_sectors_match_sector UNIQUE on (match_id, sector_id) WHERE sector_id IS NOT NULL - idx_match_sectors_price_cat on (price_category_id)

Enum: match_sector_status

CREATE TYPE match_sector_status AS ENUM ('ACTIVE', 'CLOSED', 'MAINTENANCE');

Enum: sector_purpose

CREATE TYPE sector_purpose AS ENUM ('GENERAL', 'AWAY_FANS', 'VIP', 'PRESS', 'FREE');
-- GENERAL: Standard public sale
-- AWAY_FANS: Reserved for away team supporters
-- VIP: VIP/hospitality area
-- PRESS: Media/press area
-- FREE: Free entry (VAT exempt)

match_seat_inventory

Per-match seat status (the working inventory).

Column	Type	Constraints	Description
id	UUID	PK	Inventory ID
match_id	UUID	FK → matches, NOT NULL	Match
seat_id	UUID	FK → seats, NOT NULL	Seat
status	seat_status	NOT NULL, DEFAULT 'AVAILABLE'	Current status
last_modified_by	UUID	FK → users	Who changed
last_modified_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When changed
version	INTEGER	NOT NULL, DEFAULT 1	Optimistic lock

Indexes: - idx_match_seat_inv_match on (match_id) - idx_match_seat_inv_match_seat UNIQUE on (match_id, seat_id) - idx_match_seat_inv_status on (match_id, status)

Enum: seat_status

CREATE TYPE seat_status AS ENUM (
    'AVAILABLE',
    'RESERVED',      -- In cart, TTL-based
    'SOLD',          -- Purchased
    'ALLOCATED',     -- Assigned to quota
    'BLOCKED',       -- Manually blocked
    'TECHNICAL',     -- Camera positions, etc.
    'OFFICIAL',      -- Reserved for officials
    'MAINTENANCE',   -- Temporarily unavailable
    'QUARANTINED',   -- COVID/safety spacing
    'INACTIVE'       -- Not in use for this match
);

seat_audit_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: seat_change. Schema shown here for reference. See Audit Logging Infrastructure.

Audit trail for seat status changes.

Column	Type	Constraints	Description
id	UUID	PK	Audit ID
match_seat_inventory_id	UUID	FK → match_seat_inventory, NOT NULL	Inventory record
from_status	seat_status	NOT NULL	Previous status
to_status	seat_status	NOT NULL	New status
reason	VARCHAR(255)		Change reason
changed_by	UUID	FK → users	Who changed
ticket_id	UUID	FK → tickets	Related ticket
order_id	UUID	FK → orders	Related order
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When changed

Indexes: - idx_seat_audit_inventory on (match_seat_inventory_id) - idx_seat_audit_created on (created_at)

match_audit_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: match_change. Schema shown here for reference. See Audit Logging Infrastructure.

Audit trail for match changes.

Column	Type	Constraints	Description
id	UUID	PK	Audit ID
match_id	UUID	FK → matches, NOT NULL	Match
changed_by	UUID	FK → users, NOT NULL	Who changed
change_type	match_change_type	NOT NULL	Type of change
changes_json	JSONB	NOT NULL	Change details
notification_sent	BOOLEAN	NOT NULL, DEFAULT FALSE	Notified users
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When changed

Enum: match_change_type

CREATE TYPE match_change_type AS ENUM ('UPDATE', 'CANCEL', 'RESCHEDULE', 'PUBLISH', 'CLOSE');

sector_snake_configs

Configuration for snake algorithm seat assignment.

Column	Type	Constraints	Description
id	UUID	PK	Config ID
sector_id	UUID	FK → sectors, NOT NULL	Sector
best_row_index	INTEGER	NOT NULL, DEFAULT 0	Starting row for snake
traversal_direction	traversal_dir	NOT NULL, DEFAULT 'TOP_TO_BOTTOM'	Row traversal
within_row_strategy	row_strategy	NOT NULL, DEFAULT 'CENTER_OUTWARD'	Seat selection
subsector_grouping_json	JSONB		Subsector definitions
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Enums:

CREATE TYPE traversal_dir AS ENUM ('TOP_TO_BOTTOM', 'BOTTOM_TO_TOP');
CREATE TYPE row_strategy AS ENUM ('CENTER_OUTWARD', 'LEFT_TO_RIGHT', 'RIGHT_TO_LEFT');

---

E4: Ticket Purchase Flow

orders

Purchase orders.

Column	Type	Constraints	Description
id	UUID	PK	Order ID
user_id	UUID	FK → users, NOT NULL	Buyer
match_id	UUID	FK → matches, NOT NULL	Match
order_number	VARCHAR(20)	UNIQUE, NOT NULL	Display number
status	order_status	NOT NULL, DEFAULT 'PENDING'	Order status
ticket_amount	DECIMAL(10,2)	NOT NULL	Ticket subtotal
ticket_vat_amount	DECIMAL(10,2)	NOT NULL	Ticket VAT
fee_amount	DECIMAL(10,2)	NOT NULL	Service fee
fee_vat_amount	DECIMAL(10,2)	NOT NULL	Fee VAT
total_amount	DECIMAL(10,2)	NOT NULL	Grand total
currency	VARCHAR(3)	NOT NULL, DEFAULT 'EUR'	Currency
sales_phase_id	UUID	FK → sales_phases	Purchase phase
queue_entry_id	UUID	FK → queue_entries	Queue entry
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		When completed
cancelled_at	TIMESTAMPTZ		When cancelled
cancelled_by	UUID	FK → users	Who cancelled
cancellation_reason	VARCHAR(255)		Reason

Indexes: - idx_orders_user on (user_id) - idx_orders_match on (match_id) - idx_orders_number UNIQUE on (order_number) - idx_orders_status on (status) - idx_orders_created on (created_at)

Enum: order_status

CREATE TYPE order_status AS ENUM (
    'PENDING',           -- Cart active
    'PAYMENT_PENDING',   -- Awaiting payment
    'PAYMENT_FAILED',    -- Payment failed (retryable)
    'COMPLETED',         -- Paid and confirmed
    'CANCELLED',         -- User/system cancelled
    'REFUNDED'           -- Fully refunded
);

tickets

Individual tickets (one per seat, one per holder).

Column	Type	Constraints	Description
id	UUID	PK	Ticket ID
order_id	UUID	FK → orders, NOT NULL	Parent order
match_id	UUID	FK → matches, NOT NULL	Match
match_seat_inventory_id	UUID	FK → match_seat_inventory, NOT NULL	Seat
barcode	VARCHAR(50)	UNIQUE, NOT NULL	Entry barcode
qr_code_data	VARCHAR(255)	NOT NULL	QR payload
status	ticket_status	NOT NULL, DEFAULT 'SOLD'	Status
price_amount	DECIMAL(10,2)	NOT NULL	Ticket price
fee_amount	DECIMAL(10,2)	NOT NULL	Fee portion
transfer_allowed	BOOLEAN	NOT NULL, DEFAULT TRUE	Can be transferred
external_pdf_url	VARCHAR(500)		Away match PDF
external_ticket_required	BOOLEAN	NOT NULL, DEFAULT FALSE	Needs PDF
pdf_delivered_at	TIMESTAMPTZ		PDF delivery time
attended_at	TIMESTAMPTZ		Entry scan time
cancelled_at	TIMESTAMPTZ		Cancellation time
cancelled_by	UUID	FK → users	Who cancelled
cancellation_reason	ticket_cancel_reason		Reason
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_tickets_order on (order_id) - idx_tickets_match on (match_id) - idx_tickets_barcode UNIQUE on (barcode) - idx_tickets_status on (status) - idx_tickets_match_status on (match_id, status)

Enum: ticket_status

CREATE TYPE ticket_status AS ENUM (
    'SOLD',                      -- Active ticket
    'ATTENDED',                  -- Used for entry
    'CANCELLED',                 -- User cancelled
    'TRANSFERRED',               -- Reassigned
    'CANCELLED_MATCH_CANCELLED', -- Match cancelled
    'CANCELLED_BLACKLIST',       -- Holder blacklisted
    'REFUNDED'                   -- Refunded
);

Enum: ticket_cancel_reason

CREATE TYPE ticket_cancel_reason AS ENUM (
    'SELF_SERVICE',
    'SUPPORT_REQUEST',
    'MATCH_CANCELLED',
    'BLACKLIST_BUYER',
    'BLACKLIST_HOLDER',
    'SYSTEM'
);

ticket_holders

Ticket holder identity information.

Column	Type	Constraints	Description
id	UUID	PK	Holder ID
ticket_id	UUID	FK → tickets, UNIQUE, NOT NULL	One-to-one
full_name	VARCHAR(255)	NOT NULL	Name on ticket
date_of_birth	DATE	NOT NULL	Birth date
nationality	VARCHAR(100)	NOT NULL	Country
oib_encrypted	BYTEA		Encrypted OIB
passport_encrypted	BYTEA		Encrypted passport
email	VARCHAR(255)		Delivery email
phone	VARCHAR(50)		Phone
is_minor	BOOLEAN	NOT NULL, DEFAULT FALSE	Under 18
source	holder_source	NOT NULL	Where data came from
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

Indexes: - idx_ticket_holders_ticket UNIQUE on (ticket_id)

Enum: holder_source

CREATE TYPE holder_source AS ENUM ('DEFAULT_PROFILE', 'SAVED_PROFILE', 'MANUAL_ENTRY');

transfer_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: transfer. Schema shown here for reference. See Audit Logging Infrastructure.

History of ticket transfers.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
ticket_id	UUID	FK → tickets, NOT NULL	Ticket
from_holder_name	VARCHAR(255)	NOT NULL	Previous holder
from_holder_oib_encrypted	BYTEA		Previous OIB
to_holder_name	VARCHAR(255)	NOT NULL	New holder
to_holder_oib_encrypted	BYTEA		New OIB
to_holder_email	VARCHAR(255)		New email
transfer_type	transfer_type	NOT NULL	How transferred
initiated_by	UUID	FK → users, NOT NULL	Who initiated
reason	VARCHAR(255)		Reason
transferred_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Indexes: - idx_transfer_logs_ticket on (ticket_id) - idx_transfer_logs_date on (transferred_at)

Enum: transfer_type

CREATE TYPE transfer_type AS ENUM ('SELF_SERVICE', 'SUPPORT');

---

E5: Waiting Queue System

queue_entries

Queue position records (PostgreSQL persistence).

Column	Type	Constraints	Description
id	UUID	PK	Entry ID
user_id	UUID	FK → users, NOT NULL	User
match_id	UUID	FK → matches, NOT NULL	Match
position	INTEGER	NOT NULL	Queue position
status	queue_status	NOT NULL, DEFAULT 'WAITING'	Status
joined_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Join time
window_started_at	TIMESTAMPTZ		Purchase window start
window_expires_at	TIMESTAMPTZ		Purchase window end
last_heartbeat	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last ping
grace_period_expires_at	TIMESTAMPTZ		Reconnection grace
is_connected	BOOLEAN	NOT NULL, DEFAULT TRUE	WebSocket connected
completed_at	TIMESTAMPTZ		Purchase completed

Indexes: - idx_queue_entries_user_match UNIQUE on (user_id, match_id) - idx_queue_entries_match_status on (match_id, status) - idx_queue_entries_match_position on (match_id, position)

Enum: queue_status

CREATE TYPE queue_status AS ENUM (
    'WAITING',      -- In queue
    'ACTIVE',       -- Purchase window granted
    'EXPIRED',      -- Window expired
    'COMPLETED',    -- Purchase completed
    'LEFT'          -- Voluntarily left
);

Redis: Queue Data Structures

# Sorted set for FIFO ordering
queue:{match_id}
  - member: user_id
  - score: join_timestamp (milliseconds)

# Hash for queue metadata
queue_meta:{match_id}
  - total_size: INTEGER
  - processing_rate: INTEGER (per minute)
  - created_at: TIMESTAMP

# User session state
queue_session:{user_id}:{match_id}
  - position: INTEGER
  - status: STRING
  - window_expires_at: TIMESTAMP
  - TTL: 1800 (30 minutes)

---

E6: Loyalty Program

loyalty_points

Points earned per match attendance.

Column	Type	Constraints	Description
id	UUID	PK	Point ID
user_id	UUID	FK → users, NOT NULL	User
match_id	UUID	FK → matches, NOT NULL	Match
ticket_id	UUID	FK → tickets, NOT NULL	Ticket
points	INTEGER	NOT NULL, DEFAULT 1	Points earned
earned_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When earned

Indexes: - idx_loyalty_points_user on (user_id) - idx_loyalty_points_user_match UNIQUE on (user_id, match_id) - idx_loyalty_points_earned on (earned_at)

Constraints: - 1 point per match (unique constraint) - Points from paid tickets only - User must have valid OIB

loyalty_award_batches

Batch processing for points after match.

Column	Type	Constraints	Description
id	UUID	PK	Batch ID
match_id	UUID	FK → matches, NOT NULL	Match
total_awarded	INTEGER	NOT NULL, DEFAULT 0	Points awarded
status	batch_status	NOT NULL, DEFAULT 'PENDING'	Status
processed_at	TIMESTAMPTZ		Completion time
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Enum: batch_status

CREATE TYPE batch_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED');

loyalty_tier_configs

Per-match loyalty tier configuration.

Column	Type	Constraints	Description
id	UUID	PK	Config ID
match_id	UUID	FK → matches, NOT NULL	Match
min_points	INTEGER	NOT NULL	Minimum points
max_tickets	INTEGER	NOT NULL	Ticket allocation
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_loyalty_tier_match on (match_id)

View: loyalty_balance

CREATE VIEW loyalty_balance AS
SELECT
    user_id,
    COUNT(*) as total_points,
    COUNT(*) FILTER (WHERE earned_at > NOW() - INTERVAL '5 years') as qualifying_points,
    MIN(earned_at) as oldest_point_date,
    MAX(earned_at) as newest_point_date
FROM loyalty_points
GROUP BY user_id;

---

E7: Quota Management

quotas

Ticket allocations to partners/sponsors.

Column	Type	Constraints	Description
id	UUID	PK	Quota ID
match_id	UUID	FK → matches, NOT NULL	Match
recipient_email	VARCHAR(255)	NOT NULL	Recipient email
recipient_name	VARCHAR(255)	NOT NULL	Recipient name
internal_note	VARCHAR(500)		Admin-only note (e.g., "VIP Sponsor")
total_quantity	INTEGER	NOT NULL	Total tickets
sector_ids	UUID[]	NOT NULL	Allowed sectors
discount_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 0	Discount (0-100, 100=free)
allocation_algorithm	allocation_algo	NOT NULL, DEFAULT 'NM'	Seat selection algorithm
expiration_date	DATE	NOT NULL	Visual reminder (no auto-expiry)
can_create_subquotas	BOOLEAN	NOT NULL, DEFAULT FALSE	Can delegate tickets
transfer_allowed	BOOLEAN	NOT NULL, DEFAULT TRUE	Tickets can be transferred
deferred_payment	BOOLEAN	NOT NULL, DEFAULT FALSE	Pay later option
auto_send_email	BOOLEAN	NOT NULL, DEFAULT TRUE	Send email on creation
status	quota_status	NOT NULL, DEFAULT 'PENDING'	Status
batch_id	UUID		Import batch
created_by	UUID	FK → users, NOT NULL	Creator
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
cancelled_at	TIMESTAMPTZ		Cancelled
cancelled_by	UUID	FK → users	Who cancelled
cancellation_type	cancellation_type		How cancelled

Indexes: - idx_quotas_match on (match_id) - idx_quotas_recipient on (recipient_email) - idx_quotas_status on (status) - idx_quotas_batch on (batch_id)

Enum: allocation_algo

CREATE TYPE allocation_algo AS ENUM ('NM', 'REDOM');
-- NM = Najbolja Mjesta (Best Seats)
-- REDOM = Sequential/Distributed across sector

Enum: quota_status

CREATE TYPE quota_status AS ENUM ('PENDING', 'ACTIVE', 'PAST_DEADLINE', 'CANCELLED', 'FULLY_CLAIMED');
-- PENDING: Created but not accessed
-- ACTIVE: Quota holder has accessed/started claiming
-- PAST_DEADLINE: Expiration passed (can still claim until admin cancels)
-- CANCELLED: Admin cancelled
-- FULLY_CLAIMED: All tickets sold

Enum: cancellation_type

CREATE TYPE cancellation_type AS ENUM ('ALL_UNUSED', 'UNFULFILLED_ONLY');
-- ALL_UNUSED: Cancel ALLOCATED + RESERVED tickets
-- UNFULFILLED_ONLY: Cancel only ALLOCATED (preserve RESERVED)

quota_seats

Seats/tickets allocated to a quota.

Column	Type	Constraints	Description
id	UUID	PK	Ticket allocation ID
quota_id	UUID	FK → quotas, NOT NULL	Parent quota
match_seat_inventory_id	UUID	FK → match_seat_inventory, NOT NULL	Seat reference
status	quota_seat_status	NOT NULL, DEFAULT 'ALLOCATED'	Ticket status
subquota_id	UUID	FK → subquotas	Delegated to subquota
claim_cart_id	UUID	FK → quota_claim_carts	Active claim cart
ticket_id	UUID	FK → tickets	Issued ticket
claimed_at	TIMESTAMPTZ		When sold
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_quota_seats_quota on (quota_id) - idx_quota_seats_seat on (match_seat_inventory_id) - idx_quota_seats_status on (status) - idx_quota_seats_subquota on (subquota_id)

Enum: quota_seat_status

CREATE TYPE quota_seat_status AS ENUM ('ALLOCATED', 'RESERVED', 'SOLD', 'DELEGATED', 'CANCELLED');
-- ALLOCATED: Available for claiming
-- RESERVED: In active claim cart (pending payment)
-- SOLD: Claimed and paid, ticket issued
-- DELEGATED: Assigned to a subquota
-- CANCELLED: Cancelled by admin or refunded

subquotas

Delegated ticket allocations (created by quota holders).

Column	Type	Constraints	Description
id	UUID	PK	Subquota ID
parent_quota_id	UUID	FK → quotas, NOT NULL	Parent quota
recipient_email	VARCHAR(255)	NOT NULL	Recipient email
recipient_name	VARCHAR(255)	NOT NULL	Recipient name
internal_note	VARCHAR(500)		Internal note (optional)
quantity	INTEGER	NOT NULL	Number of tickets
status	subquota_status	NOT NULL, DEFAULT 'ALLOCATED'	Status
expires_at	TIMESTAMPTZ	NOT NULL	Expiration date
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
claimed_at	TIMESTAMPTZ		When fully claimed
retracted_at	TIMESTAMPTZ		When retracted

Indexes: - idx_subquotas_parent on (parent_quota_id) - idx_subquotas_recipient on (recipient_email) - idx_subquotas_status on (status)

Enum: subquota_status

CREATE TYPE subquota_status AS ENUM ('ALLOCATED', 'RESERVED', 'SOLD', 'EXPIRED', 'CANCELLED', 'RETRACTED');
-- ALLOCATED: Tickets assigned, not yet claimed
-- RESERVED: Recipient started claiming (info filled, pending payment)
-- SOLD: Recipient completed claiming (paid)
-- EXPIRED: Past expiration (visual only, admin must cancel)
-- CANCELLED: Cancelled by admin
-- RETRACTED: Recalled by parent quota holder

subquota_seats

Seats assigned to a subquota.

Column	Type	Constraints	Description
id	UUID	PK	Assignment ID
subquota_id	UUID	FK → subquotas, NOT NULL	Subquota
quota_seat_id	UUID	FK → quota_seats, NOT NULL	Seat allocation
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_subquota_seats_subquota on (subquota_id) - idx_subquota_seats_quota_seat UNIQUE on (quota_seat_id)

quota_import_batches

Remains in PostgreSQL

This table stays in PostgreSQL due to transactional requirements (FK dependency — referenced by quotas.batch_id). See Audit Logging Infrastructure.

CSV import tracking.

Column	Type	Constraints	Description
id	UUID	PK	Batch ID
match_id	UUID	FK → matches, NOT NULL	Match
file_name	VARCHAR(255)	NOT NULL	Original filename
total_rows	INTEGER	NOT NULL	Total rows
successful_rows	INTEGER	NOT NULL, DEFAULT 0	Successful
failed_rows	INTEGER	NOT NULL, DEFAULT 0	Failed
error_report_json	JSONB		Error details
imported_by	UUID	FK → users, NOT NULL	Importer
imported_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Import time

Indexes: - idx_quota_import_match on (match_id)

quota_claim_carts

Quota claim cart (similar to regular purchase cart).

Column	Type	Constraints	Description
id	VARCHAR(20)	PK	Cart ID (human-readable, e.g., "26000001")
quota_id	UUID	FK → quotas, NOT NULL	Source quota
subquota_id	UUID	FK → subquotas	Source subquota (if claiming from subquota)
user_id	UUID	FK → users, NOT NULL	Claimant
status	claim_cart_status	NOT NULL, DEFAULT 'ACTIVE'	Cart status
ticket_holders_json	JSONB		Ticket holder details per seat
subtotal	DECIMAL(10,2)		Sum of discounted prices
service_fee	DECIMAL(10,2)		Service fee amount
total	DECIMAL(10,2)		Total amount
stripe_payment_intent_id	VARCHAR(255)		Stripe PI for payment
payment_status	claim_payment_status		Payment status
order_id	UUID	FK → orders	Created order (after completion)
expires_at	TIMESTAMPTZ	NOT NULL	Cart expiration (20 min TTL)
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Started
completed_at	TIMESTAMPTZ		Completed

Indexes: - idx_quota_claim_carts_quota on (quota_id) - idx_quota_claim_carts_user on (user_id) - idx_quota_claim_carts_status on (status) - idx_quota_claim_carts_expires on (expires_at) WHERE status = 'ACTIVE'

Enum: claim_cart_status

CREATE TYPE claim_cart_status AS ENUM ('ACTIVE', 'EXPIRED', 'COMPLETED', 'ABANDONED');

Enum: claim_payment_status

CREATE TYPE claim_payment_status AS ENUM ('PENDING', 'DEFERRED', 'PAID', 'FREE');
-- FREE: No payment required (100% discount)

---

E8: Payment Processing

payments

Payment transactions.

Column	Type	Constraints	Description
id	UUID	PK	Payment ID
order_id	UUID	FK → orders, NOT NULL	Order
stripe_payment_intent_id	VARCHAR(255)	UNIQUE	Stripe PI
stripe_charge_id	VARCHAR(255)		Stripe charge
amount	DECIMAL(10,2)	NOT NULL	Amount charged
currency	VARCHAR(3)	NOT NULL, DEFAULT 'EUR'	Currency
status	payment_status	NOT NULL, DEFAULT 'PENDING'	Status
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		Completed
failed_at	TIMESTAMPTZ		Failed
failure_reason	VARCHAR(255)		Failure reason

Indexes: - idx_payments_order on (order_id) - idx_payments_stripe_pi UNIQUE on (stripe_payment_intent_id) - idx_payments_status on (status)

Enum: payment_status

CREATE TYPE payment_status AS ENUM ('PENDING', 'PROCESSING', 'SUCCEEDED', 'FAILED', 'CANCELLED');

refunds

Refund transactions.

Column	Type	Constraints	Description
id	UUID	PK	Refund ID
order_id	UUID	FK → orders, NOT NULL	Order
ticket_id	UUID	FK → tickets	Specific ticket
payment_id	UUID	FK → payments, NOT NULL	Original payment
stripe_refund_id	VARCHAR(255)	UNIQUE	Stripe refund
refund_amount	DECIMAL(10,2)	NOT NULL	Amount refunded
fee_retained	DECIMAL(10,2)	NOT NULL, DEFAULT 0	Fee kept
source	refund_source	NOT NULL	Who initiated
reason	VARCHAR(255)		Reason
status	refund_status	NOT NULL, DEFAULT 'PENDING'	Status
requested_by	UUID	FK → users, NOT NULL	Requester
approved_by	UUID	FK → users	Approver
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		Completed

Indexes: - idx_refunds_order on (order_id) - idx_refunds_ticket on (ticket_id) - idx_refunds_stripe UNIQUE on (stripe_refund_id) - idx_refunds_status on (status)

Enum: refund_source

CREATE TYPE refund_source AS ENUM ('SELF_SERVICE', 'SUPPORT', 'SYSTEM');

Enum: refund_status

CREATE TYPE refund_status AS ENUM ('PENDING', 'PENDING_APPROVAL', 'APPROVED', 'PROCESSING', 'COMPLETED', 'FAILED', 'REJECTED');

fee_configurations

Fee and VAT settings.

Column	Type	Constraints	Description
id	UUID	PK	Config ID
match_id	UUID	FK → matches	Match-specific (null = default)
fee_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 6.00	Service fee %
home_ticket_vat_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 5.00	Home VAT
away_ticket_vat_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 0.00	Away VAT
home_fee_vat_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 25.00	Fee VAT home
away_fee_vat_percent	DECIMAL(5,2)	NOT NULL, DEFAULT 0.00	Fee VAT away
effective_from	DATE	NOT NULL	Start date
effective_to	DATE		End date
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_fee_configs_match on (match_id) - idx_fee_configs_dates on (effective_from, effective_to)

stripe_webhook_logs

Remains in PostgreSQL

This table stays in PostgreSQL due to transactional requirements (UNIQUE constraint on event_id for idempotent webhook processing). See Audit Logging Infrastructure.

Stripe webhook processing log.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
event_id	VARCHAR(255)	UNIQUE, NOT NULL	Stripe event ID
event_type	VARCHAR(100)	NOT NULL	Event type
payload_json	JSONB	NOT NULL	Full payload
processed	BOOLEAN	NOT NULL, DEFAULT FALSE	Processed
processed_at	TIMESTAMPTZ		Process time
error	TEXT		Error message
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Received

Indexes: - idx_stripe_webhook_event UNIQUE on (event_id) - idx_stripe_webhook_processed on (processed)

debtors

Partners with deferred payment.

Column	Type	Constraints	Description
id	UUID	PK	Debtor ID
name	VARCHAR(255)	NOT NULL	Company name
oib	VARCHAR(11)	UNIQUE, NOT NULL	Tax ID
address	TEXT	NOT NULL	Address
email	VARCHAR(255)	NOT NULL	Contact email
payment_terms_days	INTEGER	NOT NULL, DEFAULT 30	Payment terms
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

payment_offers

Deferred payment offers.

Column	Type	Constraints	Description
id	UUID	PK	Offer ID
debtor_id	UUID	FK → debtors, NOT NULL	Debtor
e_racuni_id	VARCHAR(100)		e-racuni document ID
e_racuni_reference	VARCHAR(100)		Reference number
orders	UUID[]	NOT NULL	Order IDs
total_amount	DECIMAL(10,2)	NOT NULL	Total due
currency	VARCHAR(3)	NOT NULL, DEFAULT 'EUR'	Currency
due_date	DATE	NOT NULL	Payment due
status	offer_status	NOT NULL, DEFAULT 'DRAFT'	Status
created_by	UUID	FK → users, NOT NULL	Creator
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
sent_at	TIMESTAMPTZ		Sent
paid_at	TIMESTAMPTZ		Paid

Enum: offer_status

CREATE TYPE offer_status AS ENUM ('DRAFT', 'SENT', 'PAID', 'OVERDUE', 'CANCELLED');

e_racuni_documents

Accounting document tracking.

Column	Type	Constraints	Description
id	UUID	PK	Doc ID
document_type	e_racuni_doc_type	NOT NULL	Type
local_reference_id	UUID	NOT NULL	Local entity ID
e_racuni_id	VARCHAR(100)		e-racuni ID
e_racuni_number	VARCHAR(50)		Document number
fiscalization_id	VARCHAR(100)		Fiscal ID
status	e_racuni_status	NOT NULL, DEFAULT 'DRAFT'	Status
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
fiscalized_at	TIMESTAMPTZ		Fiscalized

Enums:

CREATE TYPE e_racuni_doc_type AS ENUM ('PAYMENT_OFFER', 'INVOICE', 'CREDIT_NOTE');
CREATE TYPE e_racuni_status AS ENUM ('DRAFT', 'SENT', 'FISCALIZED', 'CANCELLED');

---

E9: Ticket Management & Delivery

wallet_passes

Digital wallet pass records.

Column	Type	Constraints	Description
id	UUID	PK	Pass ID
ticket_id	UUID	FK → tickets, NOT NULL	Ticket
wallet_type	wallet_type	NOT NULL	Apple/Google
pass_id	VARCHAR(255)	NOT NULL	Platform pass ID
pass_serial	VARCHAR(255)	NOT NULL	Serial number
last_updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_wallet_passes_ticket on (ticket_id) - idx_wallet_passes_pass_id UNIQUE on (wallet_type, pass_id)

Enum: wallet_type

CREATE TYPE wallet_type AS ENUM ('APPLE', 'GOOGLE');

away_ticket_pdfs

PDF tickets for away matches.

Column	Type	Constraints	Description
id	UUID	PK	PDF ID
match_id	UUID	FK → matches, NOT NULL	Match
file_name	VARCHAR(255)	NOT NULL	Filename
storage_url	VARCHAR(500)	NOT NULL	Storage path
ticket_id	UUID	FK → tickets	Assigned ticket
uploaded_by	UUID	FK → users, NOT NULL	Uploader
uploaded_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Upload time
distributed_at	TIMESTAMPTZ		Distribution time

Indexes: - idx_away_pdfs_match on (match_id) - idx_away_pdfs_ticket on (ticket_id)

cancellation_requests

Remains in PostgreSQL

This table stays in PostgreSQL due to transactional requirements (status machine — PENDING → PROCESSING → COMPLETED/FAILED with concurrent access control). See Audit Logging Infrastructure.

Self-service cancellation tracking.

Column	Type	Constraints	Description
id	UUID	PK	Request ID
order_id	UUID	FK → orders, NOT NULL	Order
ticket_ids	UUID[]	NOT NULL	Tickets to cancel
reason	VARCHAR(255)		Reason
refund_amount	DECIMAL(10,2)	NOT NULL	Amount to refund
initiated_by	UUID	FK → users, NOT NULL	Requester
status	cancellation_req_status	NOT NULL, DEFAULT 'PENDING'	Status
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		Completed

Enum: cancellation_req_status

CREATE TYPE cancellation_req_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED');

---

E10: Customer Support Operations

support_transfer_requests

Support-initiated transfers.

Column	Type	Constraints	Description
id	UUID	PK	Request ID
ticket_id	UUID	FK → tickets, NOT NULL	Ticket
requested_by_name	VARCHAR(255)	NOT NULL	Requester name
requested_by_contact	VARCHAR(255)	NOT NULL	Contact info
new_holder_name	VARCHAR(255)	NOT NULL	New holder
new_holder_oib_encrypted	BYTEA		New OIB
new_holder_email	VARCHAR(255)		New email
status	support_req_status	NOT NULL, DEFAULT 'PENDING'	Status
agent_id	UUID	FK → users, NOT NULL	Support agent
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		Completed

Enum: support_req_status

CREATE TYPE support_req_status AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'COMPLETED');

print_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: emergency_print. Schema shown here for reference. See Audit Logging Infrastructure.

Emergency ticket printing audit.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
ticket_id	UUID	FK → tickets, NOT NULL	Ticket
agent_id	UUID	FK → users, NOT NULL	Agent
reason	VARCHAR(255)	NOT NULL	Reason
identity_verified	BOOLEAN	NOT NULL	Verified
verification_document_type	VARCHAR(100)		Doc type
print_location	VARCHAR(255)		Location
printed_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Indexes: - idx_print_logs_ticket on (ticket_id) - idx_print_logs_agent on (agent_id)

---

E11: Blacklist & Security

blacklist

Banned individuals.

Column	Type	Constraints	Description
id	UUID	PK	Entry ID
oib	VARCHAR(11)	UNIQUE, NOT NULL	Croatian ID
first_name	VARCHAR(255)	NOT NULL	First name
last_name	VARCHAR(255)	NOT NULL	Last name
date_of_birth	DATE		Birth date
status	blacklist_status	NOT NULL, DEFAULT 'ACTIVE'	Status
source	VARCHAR(255)		Data source
notes	TEXT		Admin notes
created_by	UUID	FK → users, NOT NULL	Who added
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Added
removed_by	UUID	FK → users	Who removed
removed_at	TIMESTAMPTZ		Removed
removal_reason	VARCHAR(255)		Removal reason

Indexes: - idx_blacklist_oib UNIQUE on (oib) - idx_blacklist_status on (status) - idx_blacklist_name on (last_name, first_name)

Enum: blacklist_status

CREATE TYPE blacklist_status AS ENUM ('ACTIVE', 'REMOVED');

blacklist_audit_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: blacklist_change. Schema shown here for reference. See Audit Logging Infrastructure.

Blacklist change audit.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
blacklist_id	UUID	FK → blacklist, NOT NULL	Entry
action	blacklist_action	NOT NULL	Action
changed_by	UUID	FK → users, NOT NULL	Who
changes_json	JSONB	NOT NULL	Changes
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Enum: blacklist_action

CREATE TYPE blacklist_action AS ENUM ('CREATE', 'UPDATE', 'REMOVE', 'RESTORE');

blacklist_import_batches

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: blacklist_import. Schema shown here for reference. See Audit Logging Infrastructure.

CSV import tracking.

Column	Type	Constraints	Description
id	UUID	PK	Batch ID
file_name	VARCHAR(255)	NOT NULL	Filename
total_rows	INTEGER	NOT NULL	Total
successful_rows	INTEGER	NOT NULL, DEFAULT 0	Successful
failed_rows	INTEGER	NOT NULL, DEFAULT 0	Failed
duplicate_rows	INTEGER	NOT NULL, DEFAULT 0	Duplicates
error_report_json	JSONB		Errors
imported_by	UUID	FK → users, NOT NULL	Importer
imported_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

violation_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: violation. Schema shown here for reference. See Audit Logging Infrastructure.

Blocked purchase/transfer attempts.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
oib	VARCHAR(11)	NOT NULL	OIB attempted
blacklist_id	UUID	FK → blacklist, NOT NULL	Matched entry
action_type	violation_action	NOT NULL	What was blocked
match_id	UUID	FK → matches	Match
user_id	UUID	FK → users	User
session_id	VARCHAR(255)		Session
ip_address	VARCHAR(45)		IP
user_agent	TEXT		Browser
blocked_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Indexes: - idx_violation_logs_oib on (oib) - idx_violation_logs_blacklist on (blacklist_id) - idx_violation_logs_match on (match_id) - idx_violation_logs_date on (blocked_at)

Enum: violation_action

CREATE TYPE violation_action AS ENUM ('PURCHASE_ATTEMPT', 'TRANSFER_ATTEMPT');

blacklist_cancellations

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: blacklist_cancellation. Schema shown here for reference. See Audit Logging Infrastructure.

Auto-cancellation records.

Column	Type	Constraints	Description
id	UUID	PK	Record ID
blacklist_id	UUID	FK → blacklist, NOT NULL	Entry
cancellation_scope	cancellation_scope	NOT NULL	What cancelled
orders_cancelled	INTEGER	NOT NULL, DEFAULT 0	Orders
tickets_cancelled	INTEGER	NOT NULL, DEFAULT 0	Tickets
cancelled_by	UUID	FK → users, NOT NULL	Who
cancelled_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Enum: cancellation_scope

CREATE TYPE cancellation_scope AS ENUM ('BUYER_ORDERS', 'HOLDER_TICKETS');

---

E12: Physical Sales (Petrol)

petrol_pins

PIN codes for Petrol station sales.

Column	Type	Constraints	Description
id	UUID	PK	PIN ID
pin	VARCHAR(5)	UNIQUE, NOT NULL	5-digit PIN
user_id	UUID	FK → users, NOT NULL	App user
match_id	UUID	FK → matches, NOT NULL	Match
quantity	INTEGER	NOT NULL	Tickets
ticket_holders_json	JSONB	NOT NULL	Holder data
status	pin_status	NOT NULL, DEFAULT 'ACTIVE'	Status
expires_at	TIMESTAMPTZ	NOT NULL	Expiration
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
used_at	TIMESTAMPTZ		Used

Indexes: - idx_petrol_pins_pin UNIQUE on (pin) - idx_petrol_pins_user_match on (user_id, match_id) - idx_petrol_pins_status on (status)

Enum: pin_status

CREATE TYPE pin_status AS ENUM ('ACTIVE', 'USED', 'EXPIRED', 'CANCELLED');

petrol_deeplinks

QR codes for Petrol section access.

Column	Type	Constraints	Description
id	UUID	PK	Link ID
match_id	UUID	FK → matches, NOT NULL	Match
deeplink_url	VARCHAR(500)	NOT NULL	URL
qr_code_data	VARCHAR(255)	NOT NULL	QR payload
is_active	BOOLEAN	NOT NULL, DEFAULT TRUE	Active
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_petrol_deeplinks_match UNIQUE on (match_id)

petrol_reservations

Seat reservations at Petrol stations.

Column	Type	Constraints	Description
id	UUID	PK	Reservation ID
pin_id	UUID	FK → petrol_pins, NOT NULL	PIN
staff_id	UUID	FK → users, NOT NULL	Petrol staff
seats	UUID[]	NOT NULL	Reserved seats
status	reservation_status	NOT NULL, DEFAULT 'RESERVED'	Status
reserved_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Reserved
expires_at	TIMESTAMPTZ	NOT NULL	Expiration
identity_verified_at	TIMESTAMPTZ		Verified
completed_at	TIMESTAMPTZ		Completed
cancelled_by	UUID	FK → users	Who cancelled
cancellation_reason	VARCHAR(255)		Reason

Indexes: - idx_petrol_reservations_pin on (pin_id) - idx_petrol_reservations_status on (status)

Enum: reservation_status

CREATE TYPE reservation_status AS ENUM (
    'RESERVED',
    'IDENTITY_VERIFIED',
    'COMPLETED',
    'CANCELLED',
    'EXPIRED'
);

petrol_sales

Completed Petrol sales.

Column	Type	Constraints	Description
id	UUID	PK	Sale ID
reservation_id	UUID	FK → petrol_reservations, NOT NULL	Reservation
order_id	UUID	FK → orders, NOT NULL	Created order
payment_method	petrol_payment	NOT NULL	Payment type
payment_reference	VARCHAR(255)		POS reference
total_amount	DECIMAL(10,2)	NOT NULL	Amount
processed_by	UUID	FK → users, NOT NULL	Staff
processed_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Enum: petrol_payment

CREATE TYPE petrol_payment AS ENUM ('CASH', 'CARD');

---

E13: Access Control Integration

extra_barcodes

Pre-generated extra codes for access control.

Column	Type	Constraints	Description
id	UUID	PK	Code ID
match_id	UUID	FK → matches, NOT NULL	Match
barcode	VARCHAR(50)	NOT NULL	Code value
is_used	BOOLEAN	NOT NULL, DEFAULT FALSE	Used
used_at	TIMESTAMPTZ		When used
assigned_ticket_id	UUID	FK → tickets	Assigned ticket
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_extra_barcodes_match on (match_id) - idx_extra_barcodes_barcode UNIQUE on (match_id, barcode)

barcode_export_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: barcode_export. Schema shown here for reference. See Audit Logging Infrastructure.

Export tracking.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
match_id	UUID	FK → matches, NOT NULL	Match
file_name	VARCHAR(255)	NOT NULL	Export file
total_barcodes	INTEGER	NOT NULL	Total codes
extra_barcodes	INTEGER	NOT NULL	Extra codes
exported_by	UUID	FK → users, NOT NULL	Exporter
exported_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Indexes: - idx_barcode_exports_match on (match_id)

attendance_imports

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: attendance_import. Schema shown here for reference. See Audit Logging Infrastructure.

Post-match attendance data.

Column	Type	Constraints	Description
id	UUID	PK	Import ID
match_id	UUID	FK → matches, NOT NULL	Match
file_name	VARCHAR(255)	NOT NULL	Import file
total_scanned	INTEGER	NOT NULL	Total scanned
matched_count	INTEGER	NOT NULL	Matched tickets
unmatched_count	INTEGER	NOT NULL	Unmatched
imported_by	UUID	FK → users, NOT NULL	Importer
imported_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	When

Indexes: - idx_attendance_imports_match on (match_id)

attendance_unmatched

Unmatched scan records.

Column	Type	Constraints	Description
id	UUID	PK	Record ID
import_id	UUID	FK → attendance_imports, NOT NULL	Import
barcode	VARCHAR(50)	NOT NULL	Unmatched code
scan_timestamp	TIMESTAMPTZ		Scan time
reason	unmatched_reason	NOT NULL	Why unmatched

Enum: unmatched_reason

CREATE TYPE unmatched_reason AS ENUM ('NOT_FOUND', 'ALREADY_CANCELLED', 'INVALID_FORMAT');

---

E14: Reporting & Analytics

report_export_jobs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: report_export. Schema shown here for reference. See Audit Logging Infrastructure.

Async report generation.

Column	Type	Constraints	Description
id	UUID	PK	Job ID
report_type	report_type	NOT NULL	Type
parameters_json	JSONB	NOT NULL	Report params
status	job_status	NOT NULL, DEFAULT 'PENDING'	Status
file_url	VARCHAR(500)		Result file
requested_by	UUID	FK → users, NOT NULL	Requester
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
completed_at	TIMESTAMPTZ		Completed
error_message	TEXT		Error

Enum: report_type

CREATE TYPE report_type AS ENUM ('SALES', 'FINANCIAL', 'ATTENDANCE', 'QUOTA_USAGE');

Enum: job_status

CREATE TYPE job_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED');

---

E15: Notifications & Communications

email_templates

Email template definitions.

Column	Type	Constraints	Description
id	UUID	PK	Template ID
template_key	VARCHAR(100)	UNIQUE, NOT NULL	Lookup key
name	VARCHAR(255)	NOT NULL	Display name
subject	VARCHAR(255)	NOT NULL	Email subject
body_html	TEXT	NOT NULL	HTML body
body_text	TEXT	NOT NULL	Plain text
variables	VARCHAR[]	NOT NULL	Variable names
is_active	BOOLEAN	NOT NULL, DEFAULT TRUE	Active
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Updated

Indexes: - idx_email_templates_key UNIQUE on (template_key)

email_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: email_delivery. Schema shown here for reference. See Audit Logging Infrastructure.

Email delivery tracking.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
template_key	VARCHAR(100)	NOT NULL	Template used
recipient_email	VARCHAR(255)	NOT NULL	Recipient
subject	VARCHAR(255)	NOT NULL	Subject
variables_json	JSONB	NOT NULL	Variables
provider	VARCHAR(50)	NOT NULL, DEFAULT 'mailgun'	Provider
provider_message_id	VARCHAR(255)		Provider ID
status	email_status	NOT NULL, DEFAULT 'QUEUED'	Status
sent_at	TIMESTAMPTZ		Sent
delivered_at	TIMESTAMPTZ		Delivered
error_message	TEXT		Error
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_email_logs_recipient on (recipient_email) - idx_email_logs_status on (status) - idx_email_logs_created on (created_at)

Enum: email_status

CREATE TYPE email_status AS ENUM ('QUEUED', 'SENT', 'DELIVERED', 'BOUNCED', 'FAILED');

push_tokens

FCM device tokens.

Column	Type	Constraints	Description
id	UUID	PK	Token ID
user_id	UUID	FK → users, NOT NULL	User
device_id	VARCHAR(255)	NOT NULL	Device ID
fcm_token	VARCHAR(500)	NOT NULL	FCM token
platform	push_platform	NOT NULL	Platform
is_active	BOOLEAN	NOT NULL, DEFAULT TRUE	Active
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created
last_used_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last used

Indexes: - idx_push_tokens_user on (user_id) - idx_push_tokens_device UNIQUE on (device_id) - idx_push_tokens_fcm on (fcm_token)

Enum: push_platform

CREATE TYPE push_platform AS ENUM ('IOS', 'ANDROID');

push_logs

Stored in Grafana Loki

This audit data is stored in Grafana Loki as structured JSON logs with audit_type: push_delivery. Schema shown here for reference. See Audit Logging Infrastructure.

Push notification tracking.

Column	Type	Constraints	Description
id	UUID	PK	Log ID
user_id	UUID	FK → users, NOT NULL	Recipient
notification_type	VARCHAR(100)	NOT NULL	Type
title	VARCHAR(255)	NOT NULL	Title
body	TEXT	NOT NULL	Message
data_json	JSONB		Extra data
priority	push_priority	NOT NULL, DEFAULT 'NORMAL'	Priority
fcm_message_id	VARCHAR(255)		FCM ID
status	push_status	NOT NULL, DEFAULT 'QUEUED'	Status
sent_at	TIMESTAMPTZ		Sent
error_message	TEXT		Error
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Created

Indexes: - idx_push_logs_user on (user_id) - idx_push_logs_type on (notification_type) - idx_push_logs_status on (status) - idx_push_logs_created on (created_at)

Enums:

CREATE TYPE push_priority AS ENUM ('NORMAL', 'HIGH');
CREATE TYPE push_status AS ENUM ('QUEUED', 'SENT', 'DELIVERED', 'FAILED');

---

Redis Data Structures

Cart Reservations

# Cart state with TTL
cart:{user_id}:{match_id}
  - seats: UUID[] (match_seat_inventory IDs)
  - total_amount: DECIMAL
  - created_at: TIMESTAMP
  - TTL: 1200 seconds (20 minutes)

# Seat lock (prevents double-booking)
seat_lock:{match_seat_inventory_id}
  - cart_key: STRING
  - TTL: 1200 seconds

Queue Management

# See E5: Waiting Queue System section above

Rate Limiting

# API rate limit per user
rate_limit:api:{user_id}
  - count: INTEGER
  - TTL: 60 seconds

# Purchase attempt tracking
rate_limit:purchase:{user_id}:{match_id}
  - count: INTEGER
  - TTL: 3600 seconds

---

Migration Strategy

Initial Setup

1. Create all ENUM types first
2. Create tables in dependency order (no FK references before parent exists)
3. Add indexes after data load for performance
4. Add foreign key constraints last

Recommended Order

1. ENUMs
2. users, stadiums
3. sectors, matches, user_profiles, sessions
4. seats, sales_phases, match_sectors
5. match_seat_inventory, sector_seat_maps, row_configurations
6. orders, quotas, blacklist
7. tickets, payments, queue_entries
8. All audit/log tables
9. Foreign key constraints
10. Indexes

---

Last Updated: February 2026