Partner Service Data Model¶

Complete data model documentation for the Partner Service entity and related tables.

Overview¶

The Partner Service manages service definitions that partners can offer through the platform. Each service includes metadata, configuration parameters, lifecycle hooks, and approval workflows.

Technical Data Mapping¶

Main Table: `partner_services`¶

The primary table storing service definitions.

Column Name	Data Type	Constraints	Default	Description
`id`	UUID	PRIMARY KEY	`gen_random_uuid()`	Unique identifier for the service
`partner_id`	UUID	NOT NULL, FOREIGN KEY → `partners(id)`	-	Reference to the partner who owns this service
`name`	VARCHAR(255)	NOT NULL	-	Service name
`type`	VARCHAR(50)	NOT NULL	-	Service type (enum: ACCOMMODATION, TRANSPORT, ACTIVITY, INSURANCE, FOOD_BEVERAGE, CUSTOM)
`description`	TEXT	NULL	NULL	Full description of the service
`short_description`	TEXT	NULL	NULL	Brief description for listings
`parameters`	JSONB	NULL	NULL	Service-specific configuration parameters
`lifecycle_hooks`	JSONB	NULL	NULL	Lifecycle event hooks configuration
`status`	VARCHAR(20)	NOT NULL	'DRAFT'	Service status (enum: DRAFT, ACTIVE, SUSPENDED)
`version`	INTEGER	NOT NULL	1	Version number for tracking changes
`admin_notes`	TEXT	NULL	NULL	Internal notes for administrators
`approval_required`	BOOLEAN	NOT NULL	true	Whether admin approval is required
`approved_by`	UUID	NULL	NULL	ID of the admin who approved the service
`approved_at`	TIMESTAMPTZ	NULL	NULL	Timestamp when the service was approved
`created_at`	TIMESTAMPTZ	NOT NULL	`now()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	NOT NULL	`now()`	Last update timestamp

Indexes¶

idx_partner_services_partner_id on partner_id - Fast lookup by partner
idx_partner_services_type on type - Filter by service type
idx_partner_services_status on status - Filter by status

Foreign Key Constraints¶

fk_partner_service_partner - partner_id references partners(id) ON DELETE CASCADE

Related Table: `partner_service_versions`¶

Stores historical versions of service definitions.

Column Name	Data Type	Constraints	Default	Description
`id`	UUID	PRIMARY KEY	`gen_random_uuid()`	Unique identifier for the version record
`service_id`	UUID	NOT NULL, FOREIGN KEY → `partner_services(id)`	-	Reference to the service
`partner_id`	UUID	NOT NULL	-	Reference to the partner (denormalized for performance)
`version`	INTEGER	NOT NULL	-	Version number
`data`	JSONB	NOT NULL	-	Complete service data snapshot at this version
`created_at`	TIMESTAMPTZ	NOT NULL	`now()`	When this version was created

Indexes¶

idx_partner_service_versions_service_id on service_id - Fast lookup by service

Foreign Key Constraints¶

fk_partner_service_version_service - service_id references partner_services(id) ON DELETE CASCADE

Related Table: `service_proposals`¶

Stores proposals for service changes (create, update, status change).

Column Name	Data Type	Constraints	Default	Description
`id`	UUID	PRIMARY KEY	`gen_random_uuid()`	Unique identifier for the proposal
`service_id`	UUID	NOT NULL, FOREIGN KEY → `partner_services(id)`	-	Reference to the service (NULL for CREATE proposals)
`proposer_type`	VARCHAR(20)	NOT NULL	-	Who created the proposal (enum: PARTNER, ADMIN)
`proposer_id`	UUID	NOT NULL	-	ID of the proposer (partner or admin)
`proposal_type`	VARCHAR(50)	NOT NULL	-	Type of proposal (enum: CREATE, UPDATE, STATUS_CHANGE)
`proposed_data`	JSONB	NOT NULL	-	Complete proposed service data
`changed_fields`	JSONB	NOT NULL	-	Fields that would change (for UPDATE proposals)
`status`	VARCHAR(20)	NOT NULL	'PENDING'	Proposal status (enum: PENDING, APPROVED, REJECTED, WITHDRAWN)
`message`	TEXT	NULL	NULL	Optional message from proposer
`created_at`	TIMESTAMPTZ	NOT NULL	`now()`	When proposal was created
`updated_at`	TIMESTAMPTZ	NOT NULL	`now()`	Last update timestamp
`reviewed_at`	TIMESTAMPTZ	NULL	NULL	When proposal was reviewed
`reviewed_by`	UUID	NULL	NULL	ID of the reviewer
`review_message`	TEXT	NULL	NULL	Review comments

Indexes¶

idx_service_proposals_service_id on service_id - Fast lookup by service
idx_service_proposals_status on status - Filter by proposal status
idx_service_proposals_proposer_type on proposer_type - Filter by proposer type

Foreign Key Constraints¶

fk_service_proposal_service - service_id references partner_services(id) ON DELETE CASCADE

Related Table: `service_consents`¶

Stores consent records for service agreements between partners and admins.

Column Name	Data Type	Constraints	Default	Description
`id`	UUID	PRIMARY KEY	`gen_random_uuid()`	Unique identifier for the consent record
`service_id`	UUID	NOT NULL, FOREIGN KEY → `partner_services(id)`	-	Reference to the service
`partner_consented`	BOOLEAN	NOT NULL	false	Whether partner has consented
`partner_consented_at`	TIMESTAMPTZ	NULL	NULL	When partner consented
`partner_consented_by`	UUID	NULL	NULL	Partner user ID who consented
`admin_consented`	BOOLEAN	NOT NULL	false	Whether admin has consented
`admin_consented_at`	TIMESTAMPTZ	NULL	NULL	When admin consented
`admin_consented_by`	UUID	NULL	NULL	Admin user ID who consented
`consent_version`	INTEGER	NOT NULL	1	Version of the consent agreement
`locked_fields`	JSONB	NULL	NULL	Fields that are locked and cannot be changed
`created_at`	TIMESTAMPTZ	NOT NULL	`now()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	NOT NULL	`now()`	Last update timestamp

Indexes¶

idx_service_consents_service_id on service_id - Fast lookup by service

Constraints¶

unique_service_consent - UNIQUE (service_id, consent_version) - One consent record per service per version

Foreign Key Constraints¶

fk_service_consent_service - service_id references partner_services(id) ON DELETE CASCADE

Enumerations¶

ServiceType¶

enum ServiceType {
  ACCOMMODATION = 'ACCOMMODATION',  // Hotels, apartments, etc.
  TRANSPORT = 'TRANSPORT',          // Flights, car rentals, etc.
  ACTIVITY = 'ACTIVITY',            // Tours, experiences, etc.
  INSURANCE = 'INSURANCE',          // Travel insurance
  FOOD_BEVERAGE = 'FOOD_BEVERAGE',  // Restaurants, cafes
  CUSTOM = 'CUSTOM'                 // Custom service type
}

ServiceStatus¶

enum ServiceStatus {
  DRAFT = 'DRAFT',       // Service is being created/edited
  ACTIVE = 'ACTIVE',     // Service is live and available
  SUSPENDED = 'SUSPENDED' // Service is temporarily unavailable
}

ProposalStatus¶

enum ProposalStatus {
  PENDING = 'PENDING',     // Awaiting review
  APPROVED = 'APPROVED',   // Approved and applied
  REJECTED = 'REJECTED',   // Rejected by reviewer
  WITHDRAWN = 'WITHDRAWN'  // Withdrawn by proposer
}

ProposalType¶

enum ProposalType {
  CREATE = 'CREATE',           // New service creation
  UPDATE = 'UPDATE',           // Service modification
  STATUS_CHANGE = 'STATUS_CHANGE' // Status change only
}

ProposerType¶

enum ProposerType {
  PARTNER = 'PARTNER',  // Proposed by partner
  ADMIN = 'ADMIN'       // Proposed by administrator
}

JSONB Structures¶

`parameters` (JSONB)¶

Service-specific configuration parameters. Structure is flexible and depends on service type.

Example:

{
  "capacity": 10,
  "duration_hours": 3,
  "location": {
    "latitude": 38.7223,
    "longitude": -9.1393,
    "address": "Lisbon, Portugal"
  },
  "pricing": {
    "base_price": 50.00,
    "currency": "EUR"
  }
}

`lifecycle_hooks` (JSONB)¶

Configuration for lifecycle event hooks that trigger external actions.

Structure:

interface LifecycleHooks {
  onStart?: LifecycleHook;    // Triggered when service starts
  onEnd?: LifecycleHook;      // Triggered when service ends
  onSuccess?: LifecycleHook;  // Triggered on successful completion
  onFailure?: LifecycleHook;  // Triggered on failure
  onSuspend?: LifecycleHook;  // Triggered when service is suspended
  onResume?: LifecycleHook;   // Triggered when service is resumed
}

interface LifecycleHook {
  type: 'api' | 'script';              // Hook type
  target: string;                       // API endpoint or script path
  payload?: Record<string, unknown>;    // Additional payload data
  retries?: number;                     // Number of retry attempts
}

Example:

{
  "onStart": {
    "type": "api",
    "target": "https://api.example.com/service/start",
    "payload": {
      "service_id": "{{service_id}}",
      "timestamp": "{{timestamp}}"
    },
    "retries": 3
  },
  "onSuccess": {
    "type": "script",
    "target": "/scripts/notify-partner.sh",
    "retries": 1
  }
}

Data Relationships¶

Entity Relationship Diagram¶

┌─────────────┐
│  partners   │
│─────────────│
│ id (PK)     │
│ name        │
│ ...         │
└──────┬──────┘
       │
       │ 1:N
       │
┌──────▼──────────────────┐
│   partner_services      │
│─────────────────────────│
│ id (PK)                 │
│ partner_id (FK) ────────┼──┐
│ name                    │  │
│ type                    │  │
│ status                  │  │
│ ...                     │  │
└──────┬──────────────────┘  │
       │                     │
       │ 1:N                 │
       │                     │
┌──────▼──────────────┐      │
│ service_proposals   │      │
│─────────────────────│      │
│ id (PK)             │      │
│ service_id (FK) ─────┘      │
│ proposer_type               │
│ status                      │
│ ...                         │
└─────────────────────────────┘
       │
       │ 1:N
       │
┌──────▼──────────────────┐
│ service_consents        │
│─────────────────────────│
│ id (PK)                 │
│ service_id (FK) ────────┘
│ partner_consented       │
│ admin_consented         │
│ consent_version         │
│ ...                     │
└─────────────────────────┘
       │
       │ 1:N
       │
┌──────▼──────────────────┐
│ partner_service_versions │
│─────────────────────────│
│ id (PK)                  │
│ service_id (FK) ─────────┘
│ version                  │
│ data (JSONB)             │
│ ...                      │
└──────────────────────────┘

Relationship Summary¶

One Partner can have Many Services (1:N)
One Service can have Many Proposals (1:N)
One Service can have Many Consents (1:N)
One Service can have Many Versions (1:N)

Non-Technical Field Guide¶

This section provides a user-friendly explanation of each field in the Service entity for non-technical users.

Service Identification¶

Service ID¶

What it is: A unique identifier automatically generated when a service is created
Who sees it: System administrators and developers
Can you change it?: No, it's permanent
Example: 550e8400-e29b-41d4-a716-446655440000

Partner ID¶

What it is: Links the service to the partner company that owns it
Who sees it: System administrators
Can you change it?: Only when transferring service ownership (rare)
Why it matters: Ensures each service belongs to the correct partner

Service Information¶

Service Name¶

What it is: The display name of the service
Who sees it: Customers, partners, and administrators
Can you change it?: Yes, through a proposal
Best practices:
Use clear, descriptive names
Keep it under 255 characters
Make it search-friendly
Example: "Lisbon City Walking Tour"

Service Type¶

What it is: Categorizes the service into one of six predefined types
Who sees it: Everyone
Can you change it?: Yes, but requires approval
Available types:
ACCOMMODATION: Hotels, apartments, hostels, vacation rentals
TRANSPORT: Flights, car rentals, train tickets, transfers
ACTIVITY: Tours, experiences, workshops, events
INSURANCE: Travel insurance, cancellation insurance
FOOD_BEVERAGE: Restaurants, cafes, food tours, wine tastings
CUSTOM: Services that don't fit other categories
Why it matters: Helps customers find services and enables proper categorization

Description¶

What it is: Full, detailed description of the service
Who sees it: Customers browsing service details
Can you change it?: Yes, through a proposal
Best practices:
Include key features and benefits
Mention what's included
Highlight unique selling points
Use clear, engaging language
Example: "Explore the historic neighborhoods of Lisbon with our expert local guide. Visit Alfama, Baixa, and Chiado while learning about Portuguese culture and history."

Short Description¶

What it is: Brief summary for service listings and previews
Who sees it: Customers browsing service lists
Can you change it?: Yes, through a proposal
Best practices:
Keep it concise (1-2 sentences)
Capture the essence of the service
Make it compelling
Example: "Guided walking tour through Lisbon's historic districts"

Service Configuration¶

Parameters¶

What it is: Flexible configuration data specific to each service type
Who sees it: System administrators and developers
Can you change it?: Yes, through a proposal
What it contains:
Capacity limits (e.g., max 10 participants)
Duration (e.g., 3 hours)
Location coordinates
Pricing information
Service-specific settings
Why it matters: Allows each service to have unique configuration without changing the database structure

Lifecycle Hooks¶

What it is: Automated actions that trigger when service events occur
Who sees it: System administrators and developers
Can you change it?: Yes, through a proposal
When they trigger:
onStart: When a service booking begins
onEnd: When a service booking ends
onSuccess: When a service completes successfully
onFailure: When a service fails
onSuspend: When a service is suspended
onResume: When a suspended service is reactivated
What they do: Can call external APIs or run scripts to integrate with partner systems
Example: Automatically notify partner's booking system when a service starts

Service Status¶

Status¶

What it is: Current state of the service in the system
Who sees it: Partners, administrators, and customers (for active services)
Can you change it?: Yes, through a proposal
Available statuses:
DRAFT: Service is being created or edited, not visible to customers
ACTIVE: Service is live and available for booking
SUSPENDED: Service is temporarily unavailable (e.g., maintenance, seasonal closure)
Why it matters: Controls service visibility and availability

Version¶

What it is: Tracks how many times the service has been modified
Who sees it: System administrators
Can you change it?: No, automatically incremented
Why it matters: Helps track service history and enables version control

Approval Workflow¶

Approval Required¶

What it is: Whether the service needs administrator approval before going live
Who sees it: Administrators
Can you change it?: Yes, by administrators
Default: Yes (true)
Why it matters: Ensures quality control and compliance

Approved By¶

What it is: ID of the administrator who approved the service
Who sees it: Administrators
Can you change it?: Automatically set when approved
Why it matters: Provides accountability and audit trail

Approved At¶

What it is: Date and time when the service was approved
Who sees it: Administrators
Can you change it?: Automatically set when approved
Why it matters: Tracks approval timeline

Admin Notes¶

What it is: Internal notes for administrators about the service
Who sees it: Administrators only
Can you change it?: Yes, by administrators
Use cases:
Approval comments
Special instructions
Compliance notes
Quality concerns
Example: "Approved pending partner verification. Service meets all quality standards."

Timestamps¶

Created At¶

What it is: Date and time when the service was first created
Who sees it: Administrators and partners
Can you change it?: No, automatically set
Why it matters: Tracks service creation date

Updated At¶

What it is: Date and time when the service was last modified
Who sees it: Administrators and partners
Can you change it?: Automatically updated on changes
Why it matters: Tracks when service information was last changed

Service Proposals¶

When partners or administrators want to create or modify a service, they create a proposal. Proposals go through an approval workflow before changes are applied.

Key fields: - Proposal Type: CREATE (new service), UPDATE (modify existing), STATUS_CHANGE (change status only) - Status: PENDING (awaiting review), APPROVED (accepted), REJECTED (denied), WITHDRAWN (cancelled by proposer) - Proposed Data: The complete service data being proposed - Changed Fields: For updates, shows what would change

Service Consents¶

Consents track agreements between partners and administrators about service terms. Both parties must consent before certain operations can proceed.

Key fields: - Partner Consented: Whether the partner has agreed - Admin Consented: Whether the administrator has agreed - Consent Version: Version of the agreement - Locked Fields: Fields that cannot be changed without new consent

Service Versions¶

Versions maintain a historical record of service changes. Each time a service is modified, a new version snapshot is created.

Key fields: - Version Number: Sequential version identifier - Data: Complete snapshot of service data at that version - Created At: When this version was created

Common Workflows¶

Creating a New Service¶

Partner creates a service proposal (type: CREATE)
Proposal status: PENDING
Administrator reviews proposal
If approved:
Service is created with status: DRAFT
Approval fields are set
Version 1 is created
Partner can then activate the service (status: ACTIVE)

Updating an Existing Service¶

Partner or admin creates a proposal (type: UPDATE)
Proposal includes proposed changes
Proposal status: PENDING
Administrator reviews
If approved:
Service is updated
Version number increments
New version snapshot is created
Updated timestamp is set

Suspending a Service¶

Partner or admin creates a proposal (type: STATUS_CHANGE)
Proposed status: SUSPENDED
After approval, service status changes to SUSPENDED
Service becomes unavailable for booking
Lifecycle hook onSuspend may trigger

Best Practices¶

For Partners¶

Complete Information: Fill in all required fields with accurate, detailed information
Clear Descriptions: Write descriptions that help customers understand what they're booking
Accurate Parameters: Ensure parameters (capacity, duration, etc.) are correct
Regular Updates: Keep service information current and accurate
Use Proposals: Always use the proposal system for changes to ensure proper approval

For Administrators¶

Review Thoroughly: Check all service details before approval
Use Admin Notes: Document any concerns or special instructions
Monitor Versions: Track service changes through version history
Check Consents: Ensure proper consent workflow is followed
Quality Control: Maintain standards for service information quality

Partner Service Data Model¶

Overview¶

Table of Contents¶

Technical Data Mapping¶

Main Table: partner_services¶

Indexes¶

Foreign Key Constraints¶

Related Table: partner_service_versions¶

Indexes¶

Foreign Key Constraints¶

Related Table: service_proposals¶

Indexes¶

Foreign Key Constraints¶

Related Table: service_consents¶

Indexes¶

Constraints¶

Foreign Key Constraints¶

Enumerations¶

ServiceType¶

ServiceStatus¶

ProposalStatus¶

ProposalType¶

ProposerType¶

JSONB Structures¶

parameters (JSONB)¶

lifecycle_hooks (JSONB)¶

Data Relationships¶

Entity Relationship Diagram¶

Relationship Summary¶

Non-Technical Field Guide¶

Service Identification¶

Service ID¶

Partner ID¶

Service Information¶

Service Name¶

Service Type¶

Description¶

Short Description¶

Service Configuration¶

Parameters¶

Lifecycle Hooks¶

Service Status¶

Status¶

Version¶

Approval Workflow¶

Approval Required¶

Approved By¶

Approved At¶

Admin Notes¶

Timestamps¶

Created At¶

Updated At¶

Related Entities¶

Service Proposals¶

Service Consents¶

Service Versions¶

Common Workflows¶

Creating a New Service¶

Updating an Existing Service¶

Suspending a Service¶

Best Practices¶

For Partners¶

For Administrators¶

See Also¶

Main Table: `partner_services`¶

Related Table: `partner_service_versions`¶

Related Table: `service_proposals`¶

Related Table: `service_consents`¶

`parameters` (JSONB)¶

`lifecycle_hooks` (JSONB)¶