Skip to main content
The GOV.UK Notify API uses PostgreSQL with SQLAlchemy ORM. The database schema is defined in app/models.py and contains comprehensive models for users, services, templates, notifications, and more.

​
Core Models

​
User Model

Location: app/models.py:136 The User model manages authentication and permissions for platform users. 
class User(db.Model):
    __tablename__ = "users"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = db.Column(db.String, nullable=False)
    email_address = db.Column(db.String(255), nullable=False, index=True, unique=True)
    _password = db.Column(db.String, nullable=False)
    mobile_number = db.Column(db.String, nullable=True)
    auth_type = db.Column(db.String, db.ForeignKey("auth_type.name"), default=SMS_AUTH_TYPE)
    platform_admin = db.Column(db.Boolean, default=False)
Key Features:
  • Password hashing using bcrypt
  • Multiple authentication types: SMS, email, WebAuthn
  • Platform admin flag for elevated permissions
  • Many-to-many relationships with Services and Organisations
Relationships:
  • services - Services the user belongs to (via user_to_service)
  • organisations - Organisations the user belongs to (via user_to_organisation)

​
Service Model

Location: app/models.py:562 Represents a government service that sends notifications. 
class Service(db.Model, Versioned):
    __tablename__ = "services"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    _name = db.Column("name", db.String(255), nullable=False, unique=True)
    active = db.Column(db.Boolean, default=True)
    restricted = db.Column(db.Boolean, nullable=False)  # Trial mode
    
    # Rate limits per channel
    rate_limit = db.Column(db.Integer, default=3000)
    sms_message_limit = db.Column(db.BigInteger, default=999_999_999)
    email_message_limit = db.Column(db.BigInteger, default=999_999_999)
    letter_message_limit = db.Column(db.BigInteger, default=999_999_999)
Key Features:
  • Hybrid properties for name handling and email sender configuration
  • Service state: active, restricted (trial mode), count_as_live
  • Message limits per notification type
  • Versioning support for audit trail
Relationships:
  • users - Users with access to this service
  • templates - Notification templates
  • jobs - Bulk sending jobs
  • organisation - Parent organisation

​
Template Models

Location: app/models.py:1035 (TemplateBase), app/models.py:1216 (Template) Templates define the content and structure of notifications. 
class Template(TemplateBase):
    __tablename__ = "templates"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    template_type = db.Column(template_types, nullable=False)  # sms, email, letter
    content = db.Column(db.Text, nullable=False)
    subject = db.Column(db.Text)  # For email and letter
    archived = db.Column(db.Boolean, default=False)
    version = db.Column(db.Integer, default=0)
    
    # Letter-specific fields
    postage = db.Column(db.String, nullable=True)  # first, second
    letter_languages = db.Column(db.Enum(LetterLanguageOptions))
    letter_welsh_content = db.Column(db.Text)
    
    # Unsubscribe support
    has_unsubscribe_link = db.Column(db.Boolean, default=False)
Template History:
  • TemplateHistory maintains version history
  • Foreign key constraint links to specific versions
  • Immutable once a version is created

​
Notification Model

Location: app/models.py:1524 Tracks individual notification sends. 
class Notification(db.Model):
    __tablename__ = "notifications"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    to = db.Column(db.String, nullable=False)
    normalised_to = db.Column(db.String, nullable=True)
    notification_type = db.Column(notification_types, nullable=False)
    status = db.Column("notification_status", db.Text, default="created")
    
    # Personalisation stored encrypted
    _personalisation = db.Column(db.String, nullable=True)
    
    # Timestamps
    created_at = db.Column(db.DateTime, nullable=False, index=True)
    sent_at = db.Column(db.DateTime, nullable=True)
    updated_at = db.Column(db.DateTime, nullable=True)
    
    # Cost tracking
    billable_units = db.Column(db.Integer, default=0)
    rate_multiplier = db.Column(db.Numeric(asdecimal=False))
    international = db.Column(db.Boolean, default=False)
    phone_prefix = db.Column(db.String, nullable=True)
Status Values:
  • created - Initial state
  • sending - In transit to provider
  • delivered - Successfully delivered
  • permanent-failure - Failed permanently
  • temporary-failure - Failed temporarily
  • technical-failure - System error
Key Indexes:
  • ix_notifications_service_created_at - Service queries
  • ix_notifications_notification_type_composite - Type + status + created
  • ix_notifications_normalised_to_trgm - Recipient search (trigram)
  • ix_notifications_client_reference_trgm - Reference search (trigram)

​
Job Model

Location: app/models.py:1418 Represents bulk notification sending jobs. 
class Job(db.Model):
    __tablename__ = "jobs"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    original_file_name = db.Column(db.String, nullable=False)
    notification_count = db.Column(db.Integer, nullable=False)
    job_status = db.Column(db.String(255), default="pending")
    
    # Processing tracking
    processing_started = db.Column(db.DateTime, nullable=True)
    processing_finished = db.Column(db.DateTime, nullable=True)
    scheduled_for = db.Column(db.DateTime, index=True, nullable=True)
    
    # Statistics
    notifications_sent = db.Column(db.Integer, default=0)
    notifications_delivered = db.Column(db.Integer, default=0)
    notifications_failed = db.Column(db.Integer, default=0)
Job Statuses:
  • pending - Waiting to process
  • in progress - Currently processing
  • finished - Completed successfully
  • cancelled - Cancelled by user or system
  • sending limits exceeded - Hit rate limits

​
Supporting Models

​
Organisation Model

Location: app/models.py:425 
class Organisation(db.Model):
    __tablename__ = "organisation"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    name = db.Column(db.String(255), unique=True, nullable=False)
    organisation_type = db.Column(db.String(255))  # central, local, nhs, etc.
    crown = db.Column(db.Boolean, nullable=True)
    
    # Agreement tracking
    agreement_signed = db.Column(db.Boolean, nullable=True)
    agreement_signed_at = db.Column(db.DateTime, nullable=True)
    agreement_signed_version = db.Column(db.Float, nullable=True)
    
    # Billing
    billing_contact_email_addresses = db.Column(db.Text, nullable=True)
    billing_reference = db.Column(db.String(255), nullable=True)

​
API Key Model

Location: app/models.py:929 
class ApiKey(db.Model, Versioned):
    __tablename__ = "api_keys"
    
    id = db.Column(UUID(as_uuid=True), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    _secret = db.Column("secret", db.String(255), unique=True)  # Encrypted
    key_type = db.Column(db.String(255))  # normal, test, team
    expiry_date = db.Column(db.DateTime, nullable=True)
Key Types:
  • normal - Production use
  • test - Testing (doesn’t send)
  • team - Team members only

​
Provider Details Model

Location: app/models.py:1364 Tracks SMS/Email/Letter provider configuration. 
class ProviderDetails(db.Model):
    __tablename__ = "provider_details"
    
    identifier = db.Column(db.String, nullable=False)  # mmg, firetext, ses, dvla
    display_name = db.Column(db.String, nullable=False)
    notification_type = db.Column(notification_types, nullable=False)
    priority = db.Column(db.Integer, nullable=False)
    active = db.Column(db.Boolean, default=False)
    supports_international = db.Column(db.Boolean, default=False)

​
Branding Models

Email and letter branding for customization. 
class EmailBranding(db.Model):
    __tablename__ = "email_branding"
    
    colour = db.Column(db.String(7))  # Hex color
    logo = db.Column(db.String(255))  # S3 path
    name = db.Column(db.String(255), unique=True)
    text = db.Column(db.String(255))  # Alt text
    brand_type = db.Column(db.String(255))  # org, both, govuk

class LetterBranding(db.Model):
    __tablename__ = "letter_branding"
    
    name = db.Column(db.String(255), unique=True)
    filename = db.Column(db.String(255), unique=True)  # SVG filename

​
Database Features

​
Versioning

Many models inherit from Versioned mixin for audit trails:
  • Service
  • Template → TemplateHistory
  • ApiKey
  • ServiceCallbackApi

​
Encryption

Sensitive data is encrypted using the signing module:
  • API key secrets
  • Notification personalisation
  • Service callback bearer tokens

​
Indexes

Extensive indexing for performance:
  • Composite indexes for common query patterns
  • Trigram indexes (GIN) for fuzzy text search
  • Partial indexes for specific conditions

​
Extended Statistics

PostgreSQL extended statistics for query optimization: 
__extended_statistics__ = (
    ("st_dep_notifications_service_id_api_key_id", 
     ("service_id", "api_key_id"), 
     ("dependencies",)),
)

​
Connection Configuration

From app/config.py:174: 
SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_size": 5,
    "pool_timeout": 30,
    "pool_recycle": 300,
    "connect_args": {
        "connect_timeout": "5",
        "tcp_user_timeout": "5000",
        "options": "-c statement_timeout=1200000",
    },
}

​
Database Bindings

Supports separate read replica:
  • Default binding: Primary database
  • bulk binding: Read replica for analytics queries

​
Migration Strategy

Migrations managed with Alembic:
  • Located in migrations/versions/
  • Auto-generation with manual review
  • Downgrade paths for rollbacks
  • Custom logic for views in migrations/env.py
  • app/models.py - All model definitions
  • app/dao/ - Data access objects for each model
  • app/config.py - Database configuration
  • migrations/ - Alembic migration files

Build docs developers (and LLMs) love

Get started for freeTalk to us