Database Schema and Models
Relevant source files
The following files were used as context for generating this wiki page:
The GoGovSG database layer manages the persistence of short links, user accounts, and analytical data. It uses PostgreSQL as the primary relational data store and Sequelize as the Object-Relational Mapper (ORM) to define schemas and manage relationships. Redis complements the relational database by providing a transient storage layer for session management, one-time passwords, and high-performance redirect lookups.
Sources: README.md:92-96, src/server/models/index.ts:1-12
Entity Relationships
The system architecture centers around the User and Url entities. A user owns multiple links, while each link maintains associations with statistics and metadata such as tags.
Core Data Relationships
The following ER diagram illustrates the primary associations between the database models.
The diagram shows the 1:N relationship between Users and URLs, and the N:M relationship between URLs and Tags managed via a join table.
Sources: src/server/models/index.ts:15-50
Association Details
- Ownership: One
Usercan create manyUrls, but eachUrlbelongs to only oneUser. - Job Processing:
Userentities initiateJobrecords, which contain multipleJobItementries for bulk operations. - Tagging:
UrlandTagentities share a many-to-many relationship through theurl_tagjoin table. - Statistics: Each
Urlrelates to specialized statistical models includingDailyClicks,WeekdayClicks,Devices, andUrlClicks.
Sources: src/server/models/index.ts:15-46
Model Definitions and Constraints
The application enforces data integrity through Sequelize model definitions and Joi validation schemas.
URL Model and Validation
The Url model stores the mapping between short links and their destinations. Destinations are either long HTTPS URLs or files stored in S3.
| Field | Type | Constraints | Description |
|---|---|---|---|
shortUrl | String | Primary Key, Unique | The shortened path (e.g., /my-link). |
longUrl | String | Valid HTTPS URL | The destination address or S3 bucket link. |
state | Enum | ACTIVE, INACTIVE | Determines if the redirect is functional. |
isFile | Boolean | Default: false | Indicates if the link points to an uploaded file. |
tagStrings | String | - | Denormalized string of tags for text search. |
safeBrowsingExpiry | Date | Optional | Expiration for Google Safe Browsing safety checks. |
Sources: src/server/repositories/UrlRepository.ts:80-100, src/server/api/user/validators.ts:88-105
User Model and Authentication
The User model manages identity for Singapore government officials.
- Email Validation: The system restricts account creation to valid government emails using glob expressions.
- API Access: Users can generate API keys, which are hashed and stored to authenticate requests to the Admin API.
Sources: src/server/api/user/validators.ts:154-159, src/server/api/user/index.ts:154-160, README.md:120-120
Data Flow and Persistence Logic
Link Creation Flow
When a user creates a link, the UrlRepository executes a transaction to ensure atomic updates across the database and external storage.
The sequence demonstrates how the repository coordinates PostgreSQL records with S3 binary storage during link creation.
Sources: src/server/repositories/UrlRepository.ts:70-112
Search and Indexing
GoGovSG implements full-text search using PostgreSQL's tsvector.
- Vector Search: The
rawDirectorySearchfunction utilizesurlSearchVectorandplainto_tsqueryto match user queries againstshortUrlandtagStrings. - Ranking: Results are ranked by either
Recency(createdAt) orPopularity(total clicks). - Email Filtering: Specialized queries use
LIKE ANYagainst an array of domain tokens for directory-wide searches.
Sources: src/server/repositories/UrlRepository.ts:182-220, src/server/repositories/UrlRepository.ts:373-393
Storage Infrastructure
The project utilizes a hybrid storage approach to balance data persistence with performance.
- PostgreSQL: Serves as the source of truth for all relational models.
- Redis: Caches redirect mappings to minimize database load. The
UrlRepositoryinvalidates the Redis cache whenever a URL is updated. - AWS S3: Stores binary files for "file-based" short links. The database stores the S3 key and visibility state (Public/Private), which is toggled based on the link's
ACTIVEstatus.
Sources: README.md:103-112, src/server/repositories/UrlRepository.ts:133-157, src/server/repositories/UrlRepository.ts:402-410
Summary
The database schema and models in GoGovSG provide a robust framework for managing official government links. By integrating Sequelize for relational mapping, PostgreSQL for advanced text searching, and Redis for high-speed caching, the system ensures both data integrity and high availability for link redirection. The architecture strictly enforces government-only access through email validation and maintains a detailed history of all URL modifications.