Designing a Data Source Discovery App - Part 20: Securing Access with Token-Based Authentication

Last time I described the technique the kDS Discovery app uses for generating hierarchical diagrams. Now I want to describe the app's security requirements for a token-based authentication system. This eliminates password management while providing time-limited, auditable access for both administrators and interview respondents.

Database Architecture Overview

The security model consists of three core tables that work together to provide comprehensive access control:

1. The Interview Invite Table

The interview.invite table manages respondent access through secure tokens:

Table "interview"."invite" {
  "token_" uuid [pk, not null, default: `gen_random_uuid()`]
  "invite_date" timestamptz [not null]
  "expires_at" timestamptz [not null]
  "interview_id" uuid [not null]
  "respondent_id" uuid [not null]
  "create_date" timestamptz [not null]
  "created_by" varchar(92) [not null]
  "modified_date" timestamptz 
  "modified_by" varchar(72)
  "source_" varchar(96) [not null]
}

Key design decisions:

• UUID tokens: Using gen_random_uuid() provides cryptographically secure, non-guessable tokens
• Expiration tracking: Built-in expiration ensures tokens have limited lifespans
• Audit trail: Complete tracking of who created and modified each invitation
• Foreign key relationships: Clean links to both interview and respondent records

2. The Admin Account Table

The admin.account table stores administrator credentials and security settings:

Table "admin"."account" {
  "admin_id" uuid [pk, not null, default: `gen_random_uuid()`]
  "name_" varchar(72) [not null]
  "email_" text [not null]
  "login_id" varchar(72)
  "expiration_time" varchar(24) [not null]
  "enable_" bit(1) [not null]
  "ip_address" INET
  "allowed_ip_range" INET
  "security_group" VARCHAR(50) NOT NULL
  "create_date" timestamptz [not null]
  "created_by" varchar(92) [not null]
  "modified_date" timestamptz
  "modified_by" varchar(72)
  "source_" varchar(72) [not null]
}

Notable features:

• IP restrictions: Using PostgreSQL's INET type for network-level security
• Security groups: Role-based access control through group assignments
• Enable flag: Quick account suspension without deletion
• Expiration tracking: Built-in account lifecycle management

3. The Admin Access Table

The admin.access table manages active admin sessions:

Table "admin"."access" {
  "token_" uuid [pk, not null, default: `gen_random_uuid()`]
  "admin_id" uuid [not null]
  "start_at" timestamptz [not null]
  "expires_at" timestamptz [not null]
}

This streamlined design focuses on:

• Session tokens: Separate from account credentials
• Time windowing: Clear start and expiration timestamps
• Admin linkage: Direct relationship to the admin account

Authentication Workflow

The diagram below illustrates how the token-based authentication system handles access requests for both respondents and administrators. The workflow demonstrates the multi-layered security checks, including token validation, IP restrictions, and optional MFA for administrative access.

Key Functional Requirements

Respondent Authentication

The system must provide a seamless experience for survey respondents by generating unique invitation tokens that include interview and respondent identifiers. Each token should have a configurable expiration period (typically 7-14 days) and be delivered via email with a secure access link. When respondents click the link, the system validates the token's authenticity and expiration status before granting access to the specific interview.

Administrator Authentication

Admin access requires multi-layered security starting with credential verification and token generation for valid sessions. The system must enforce IP-based restrictions when configured, checking whether the admin's current IP falls within allowed ranges. For enhanced security, the system should support optional multi-factor authentication before creating the admin session. Each admin token should have a shorter lifespan than respondent tokens (typically 8-12 hours) to minimize exposure.

Security Controls

The database design incorporates several security measures including automatic token expiration and cleanup processes, comprehensive audit logging for all token operations, and role-based access control through security groups. The system must support immediate token revocation for compromised accounts and implement rate limiting on authentication endpoints to prevent brute-force attacks.

Monitoring and Maintenance

Essential monitoring capabilities include tracking failed authentication attempts, alerting on suspicious access patterns, and maintaining detailed logs of all security events. The system should automatically clean up expired tokens to prevent database bloat and provide administrative interfaces for reviewing active sessions and access patterns.

Why Token-Based Authentication?

Traditional password-based systems require significant overhead for password storage, reset mechanisms, and user management. Our token-based architecture eliminates these complexities while providing stronger security guarantees:

• Time-limited access by default
• Network-level security controls
• Complete audit trails
• Flexible permission management
• Scalable authentication without session state

By leveraging PostgreSQL's native features like UUID generation and INET types, combined with careful schema design, this system provides enterprise-grade security without the overhead of traditional authentication methods. In the next post, we'll examine the implementation code that brings this security architecture to life. We'll also talk about securing the database.

Join Our Beta Program

As we continue developing the kDS Data Source Discovery App with these enterprise-grade security features, we're actively seeking organizations to participate in our beta program. If your organization could benefit from streamlined data source discovery with robust security controls, we'd love to collaborate with you.

Interested? Leave a comment below or reach out directly at info@keeshinds.com.

Thank you for stopping by.