Would you like to see your link here? Contact us

Notesclub

created by hec & contributors

terms privacy

Klepsidra Feature Planner

notebooks/feature_planner.livemd

Sacha El Masry

@sachamasry

klepsidra

More notebooks

Klepsidra Feature Planner

User authentication
Quote and wisdom recording
Trip tracker
1. Trip tracker schema
Notes
To-Dos

User authentication

Detailed Schema Design

Table: users

Stores basic user information and profile details. This is the main table for user data.

This schema takes into account core fields while ensuring normalization by linking to related tables for permissions, preferences, and other entities. There are fields to track 2FA status and API-related identifiers directly within the users table.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the user.
username	VARCHAR(150)	NOT NULL, UNIQUE	Unique login username.
email	VARCHAR(255)	NOT NULL, UNIQUE	Email address for login/notifications.
password_hash	TEXT	NOT NULL	Hashed password for authentication.

first_name	VARCHAR(100)			User’s first name.
last_name	VARCHAR(100)			User’s last name.

role_id	UUID	FOREIGN KEY (roles.id)	Role assigned to the user, references roles table.
user_type	ENUM(‘internal’, ‘external’)	NOT NULL	Specifies if the user is internal (employee) or external (contact).
employee_id	UUID	FOREIGN KEY (employees.id)	Links to the employees table if the user is internal.
contact_id	UUID	FOREIGN KEY (contacts.id)	Contact information, references contacts table if the user is external.
is_active	BOOLEAN DEFAULT TRUE		Indicates if the account is active.
is_verified	BOOLEAN DEFAULT FALSE		Indicates if the email/account is verified.

Related Tables

These tables help normalize the schema by separating permissions, preferences, and logging data.

Table roles

Manages user roles and permissions.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the role.
name	VARCHAR(50)	NOT NULL, UNIQUE	Role name (e.g., Admin, Manager, Employee).

Table permissions

Defines granular permissions assigned to roles.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the permission.
name	VARCHAR(50)	NOT NULL, UNIQUE	Permission name (e.g., view_users, edit_projects).

Table role_permissions

Many-to-Many relationship between roles and permissions.

Column Name	Data Type	Constraints	Description
role_id	UUID	FOREIGN KEY (roles.id)	Role identifier.
permission_id	UUID	FOREIGN KEY (permissions.id)	Permission identifier.

Schema for the user preferences table, user actions log, and countries & visas API integration tables to further enhance the app.

Table: user_preferences

The user_preferences table stores individual user settings, preferences for notifications, dashboard layouts, UI settings, notifications, and other customizations.

Column	Type	Description
id	UUID	Primary key, unique identifier for each preference record.
user_id	UUID	Foreign key referencing users(id).
preference_type	VARCHAR	Type of preference (e.g., notification, dashboard, language).
preference_key	VARCHAR	Specific setting within the preference type (e.g., email_notifications, default_currency).
preference_value	VARCHAR	The value for the specific preference setting (e.g., true, USD).
created_at	TIMESTAMP	When the preference was created.
updated_at	TIMESTAMP	When the preference was last updated.

Sample Entries

notification, email_notifications, true – To enable or disable email notifications.
dashboard, show_trip_statistics, true – To show/hide specific dashboard elements.
language, default_language, en – To set the preferred language.
currency - User’s default currency
colour, user profile colour - Set user colour displayed in the UI
photo or avatar - Profile image displayed in the UI
timezone - Timezone to use to make timer creation easier
theme - System theme to display to user

This structure allows for flexible key-value storage of user preferences, which can be expanded as new features are added.

Table user_logs

Tracks user activity for auditing purposes.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the log entry.
user_id	UUID	FOREIGN KEY (users.id)	User identifier.
action	VARCHAR(255)	NOT NULL	Description of the user’s action.
timestamp	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	When the action occurred.

Summary

This schema achieves the following:

Normalization: Key entities like roles, permissions, and preferences are separated for reuse and scalability.
Flexibility: Easily extendable with additional tables (e.g., API tokens, notification settings).
Auditability: Supports tracking user activities via the user_logs table.

Additional Tables for 2FA and API Management

Table api_tokens

Manages API access tokens for users, including expiration and usage tracking.

Column Name	Data Type	Constraints	Description

| id | UUID PRIMARY KEY Unique identifier for the token. | | user_id | UUID | FOREIGN KEY (users.id) User associated with the API token. | | token | TEXT | NOT NULL, UNIQUE The API access token (securely generated). | | expires_at | TIMESTAMP || Expiration timestamp for the token. | | created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | When the token was generated. | | last_used_at | TIMESTAMP | | Last time the token was used. | | ip_restrictions | TEXT | | JSON or text list of allowed IP ranges for the token. |

Table two_factor_recovery_codes

Stores one-time use recovery codes for users who lose access to their 2FA devices.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the recovery code.
user_id	UUID	FOREIGN KEY (users.id)	User associated with the recovery code.
recovery_code	TEXT	NOT NULL, UNIQUE	Encrypted or hashed one-time recovery code.
created_at	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	When the recovery code was generated.
used_at	TIMESTAMP		Timestamp of when the recovery code was used.

Updated Tables for Enhanced API Integration

Table api_limits

Tracks API rate limits and quotas for users or their tokens.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the rate limit record.
user_id	UUID	FOREIGN KEY (users.id)	User associated with the rate limit.
token_id	UUID	FOREIGN KEY (api_tokens.id)	Specific token (optional, for token-level limits).
request_count	INTEGER	DEFAULT 0	Number of API requests made in the current period.
limit	INTEGER	NOT NULL	Maximum allowed API requests.
reset_at	TIMESTAMP	NOT NULL	When the rate limit counter resets.

Updated Schema for Two-Factor Authentication

Table two_factor_attempts

Logs attempts to use two-factor authentication for security auditing.

Column Name	Data Type	Constraints	Description
id	UUID	PRIMARY KEY	Unique identifier for the attempt record.
user_id	UUID	FOREIGN KEY (users.id)	User associated with the 2FA attempt.
timestamp	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	When the 2FA attempt occurred.
success	BOOLEAN	DEFAULT FALSE	Whether the 2FA attempt was successful.
ip_address	VARCHAR(45)		IP address of the 2FA attempt.

Relationships

Users to API Tokens: One user can have multiple API tokens (users.id → api_tokens.user_id).
Users to 2FA Recovery Codes: One user can have multiple recovery codes (users.id → two_factor_recovery_codes.user_id).
Users to API Limits: API usage can be tracked globally for a user or per token (users.id → api_limits.user_id).

Example Query Use Cases

Enable 2FA for a User

Update the two_factor_enabled field for the user and generate a two_factor_secret.

UPDATE users
SET two_factor_enabled = TRUE, two_factor_secret = 'NEW_SECRET_HERE'
WHERE id = 'USER_UUID';

Log an API Token Usage

Increment the request_count in api_limits when a token is used.

UPDATE api_limits
SET request_count = request_count + 1
WHERE user_id = 'USER_UUID' AND token_id = 'TOKEN_UUID' AND reset_at > NOW();

Log a 2FA Attempt

Insert a record in two_factor_attempts for a login attempt.

INSERT INTO two_factor_attempts (user_id, timestamp, success, ip_address)
VALUES ('USER_UUID', NOW(), FALSE, '192.168.1.1');

Benefits

Security: 2FA fields and recovery codes ensure robust protection.
Scalability: External API tokens with rate-limiting ensure controlled integration with third-party apps.
Auditability: Logs for 2FA attempts and API usage help with compliance and debugging.

Quotes and wisdom recording

The following fature list comes from a conversation with Claude AI, on the 9th November 2024.

Core Functionality

1. Quote Entry and Management

Rich text input with formatting support
Fields for:
- Quote text
- Author
- Source/work
- Year/date
- Original language
- Translation credits (if applicable)
- Context of the quote
- Categories/themes
- Verification status
Bulk import capability (CSV, JSON)
Version history for quote edits
Duplicate detection

2. Organization and Categorization

Hierarchical tagging system
Custom collections/folders
Multiple categorization methods:
- By theme
- By author
- By time period
- By source type
- By language
Smart collections based on rules
Cross-referencing between related quotes

3. Search and Discovery

Full-text search
Advanced filtering options
Fuzzy matching for similar quotes
Author biography lookup
Related quotes suggestion
Search within specific collections/categories
Save search criteria as views

4. Verification and Sources

Source verification system
Citation format generator
External reference links
Reliability rating
Community verification flags
Primary source documentation
Academic reference integration

5. Engagement Features

Personal annotations
Highlighting and markup
Note-taking capability
Reflection prompts
Study modes
Spaced repetition for memorization
Reading lists

6. Sharing and Export

Multiple export formats (PDF, DOCX, HTML)
Social sharing integration
Quote cards generator
Presentation mode
API access
Collaborative collections
Public/private visibility settings

7. Analytics and Insights

Reading history
Favorite themes analysis
Author distribution
Time period coverage
Language diversity metrics
Usage patterns
Personal growth tracking

Technical Requirements

1. Data Security

End-to-end encryption
Regular backups
Data export capability
Privacy controls
User authentication
Access logging

2. Integration Capabilities

API endpoints
Common authentication methods
Database compatibility
Cloud storage options
Third-party app connections
Browser extensions

3. Platform Support

Web application
Mobile apps (iOS/Android)
Desktop versions
Offline functionality
Cross-device sync
Cloud backup

User Experience Features

1. Customization

Theme options
Layout preferences
Custom fields
Personalized dashboards
Widget system
Quick access shortcuts

2. Accessibility

Screen reader support
Keyboard navigation
Font size adjustment
High contrast modes
Multi-language interface
Voice input support

3. Learning Tools

Reading speed optimization
Comprehension tools
Translation integration
Context explanation
Historical background
Related concepts

Development Priorities

Phase 1 (MVP)

Basic quote entry and management
Simple tagging system
Search functionality
Export capability
User accounts

Phase 2

Advanced categorization
Verification system
Mobile apps
Sharing features
Analytics dashboard

Phase 3

API development
Integration ecosystem
Advanced learning tools
Community features
AI-powered insights

A comprehensive database schema that supports the specified functionality.

-- Users and Authentication
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT true,
    preferences JSONB
);

-- User Roles and Permissions
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE user_roles (
    user_id UUID REFERENCES users(user_id),
    role_id INTEGER REFERENCES roles(role_id),
    PRIMARY KEY (user_id, role_id)
);

-- Authors Management
CREATE TABLE authors (
    author_id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    birth_date DATE,
    death_date DATE,
    nationality VARCHAR(100),
    biography TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(user_id),
    verified BOOLEAN DEFAULT false
);

-- Sources (Books, Speeches, etc.)
CREATE TABLE sources (
    source_id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- book, speech, article, etc.
    publication_date DATE,
    publisher VARCHAR(255),
    url TEXT,
    isbn VARCHAR(20),
    language_code VARCHAR(10),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(user_id)
);

-- Categories/Themes Hierarchy
CREATE TABLE categories (
    category_id UUID PRIMARY KEY,
    parent_category_id UUID REFERENCES categories(category_id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Main Quotes Table
CREATE TABLE quotes (
    quote_id UUID PRIMARY KEY,
    text TEXT NOT NULL,
    author_id UUID REFERENCES authors(author_id),
    source_id UUID REFERENCES sources(source_id),
    language_code VARCHAR(10),
    year INTEGER,
    context TEXT,
    verification_status VARCHAR(50) DEFAULT 'unverified',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(user_id),
    is_public BOOLEAN DEFAULT false
);

-- Quote Translations
CREATE TABLE quote_translations (
    translation_id UUID PRIMARY KEY,
    quote_id UUID REFERENCES quotes(quote_id),
    language_code VARCHAR(10) NOT NULL,
    translated_text TEXT NOT NULL,
    translator VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(user_id)
);

-- Quote Categories Junction
CREATE TABLE quote_categories (
    quote_id UUID REFERENCES quotes(quote_id),
    category_id UUID REFERENCES categories(category_id),
    PRIMARY KEY (quote_id, category_id)
);

-- Tags System
CREATE TABLE tags (
    tag_id UUID PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE quote_tags (
    quote_id UUID REFERENCES quotes(quote_id),
    tag_id UUID REFERENCES tags(tag_id),
    created_by UUID REFERENCES users(user_id),
    PRIMARY KEY (quote_id, tag_id)
);

-- Collections (User-created quote groups)
CREATE TABLE collections (
    collection_id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(user_id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE collection_quotes (
    collection_id UUID REFERENCES collections(collection_id),
    quote_id UUID REFERENCES quotes(quote_id),
    added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (collection_id, quote_id)
);

-- User Interactions
CREATE TABLE user_annotations (
    annotation_id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(user_id),
    quote_id UUID REFERENCES quotes(quote_id),
    annotation_text TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_favorites (
    user_id UUID REFERENCES users(user_id),
    quote_id UUID REFERENCES quotes(quote_id),
    favorited_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, quote_id)
);

-- Verification and Moderation
CREATE TABLE verification_history (
    verification_id UUID PRIMARY KEY,
    quote_id UUID REFERENCES quotes(quote_id),
    verified_by UUID REFERENCES users(user_id),
    verification_status VARCHAR(50) NOT NULL,
    verification_notes TEXT,
    verified_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Activity Logging
CREATE TABLE activity_log (
    log_id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(user_id),
    activity_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id UUID NOT NULL,
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for Performance
CREATE INDEX idx_quotes_author ON quotes(author_id);
CREATE INDEX idx_quotes_source ON quotes(source_id);
CREATE INDEX idx_quotes_created_by ON quotes(created_by);
CREATE INDEX idx_quotes_verification_status ON quotes(verification_status);
CREATE INDEX idx_quotes_language ON quotes(language_code);
CREATE INDEX idx_quote_text_search ON quotes USING GIN (to_tsvector('english', text));
CREATE INDEX idx_collections_user ON collections(user_id);
CREATE INDEX idx_user_annotations_quote ON user_annotations(quote_id);
CREATE INDEX idx_user_favorites_user ON user_favorites(user_id);

This schema design provides:

Core Quote Management:
- Quotes with translations and versioning
- Authors and sources tracking
- Categories and tags
- Collections system
User Management:
- User accounts and roles
- Personal collections
- Annotations and favorites
Organization:
- Hierarchical categories
- Flexible tagging
- Custom collections
Quality Control:
- Verification system
- Activity logging
- Moderation capabilities
Performance Optimizations:
- Strategic indexing
- Full-text search support
- Efficient relationship tracking

Key relationships and detailed field specifications for the core tables.

Database Relationships and Field Specifications

Core Entity Relationships

1. Quotes → Authors (Many-to-One)

Each quote must have one author (author_id in quotes table)
One author can have many quotes
Relationship enforced by foreign key quotes.author_id → authors.author_id

2. Quotes → Sources (Many-to-One)

Each quote can have one source (source_id in quotes table)
One source can contain many quotes
Optional relationship (quotes may exist without sources)
Enforced by foreign key quotes.source_id → sources.source_id

3. Quotes ↔ Categories (Many-to-Many)

Implemented through junction table quote_categories
One quote can belong to multiple categories
One category can contain multiple quotes
Allows hierarchical categorization through categories.parent_category_id

4. Quotes ↔ Tags (Many-to-Many)

Implemented through junction table quote_tags
More flexible than categories
Includes user attribution for tag assignment

5. Users ↔ Quotes (Various)

Direct relationship through quotes.created_by
Indirect relationships through:
- user_annotations
- user_favorites
- collections → collection_quotes

Detailed Field Specifications

Quotes Table

CREATE TABLE quotes (
    quote_id UUID 
        PRIMARY KEY
        COMMENT 'Unique identifier for the quote',
    
    text TEXT 
        NOT NULL
        COMMENT 'The actual quote text, unlimited length',
    
    author_id UUID 
        REFERENCES authors(author_id)
        COMMENT 'Foreign key to authors table',
    
    source_id UUID 
        REFERENCES sources(source_id)
        COMMENT 'Optional reference to source material',
    
    language_code VARCHAR(10)
        COMMENT 'ISO 639-1/2 language code',
    
    year INTEGER
        COMMENT 'Year the quote was originally spoken/written',
    
    context TEXT
        COMMENT 'Historical or situational context of the quote',
    
    verification_status VARCHAR(50)
        DEFAULT 'unverified'
        CHECK (verification_status IN (
            'unverified',
            'verified',
            'disputed',
            'needs_review'
        ))
        COMMENT 'Current verification status',
    
    created_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP
        COMMENT 'When the quote was added to the system',
    
    updated_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP
        COMMENT 'Last modification timestamp',
    
    created_by UUID
        REFERENCES users(user_id)
        COMMENT 'User who added the quote',
    
    is_public BOOLEAN
        DEFAULT false
        COMMENT 'Whether the quote is visible to all users'
);

Authors Table

CREATE TABLE authors (
    author_id UUID
        PRIMARY KEY
        COMMENT 'Unique identifier for the author',
    
    name VARCHAR(255)
        NOT NULL
        COMMENT 'Full name of the author',
    
    birth_date DATE
        COMMENT 'Author''s birth date',
    
    death_date DATE
        COMMENT 'Author''s death date',
    
    nationality VARCHAR(100)
        COMMENT 'Author''s primary nationality',
    
    biography TEXT
        COMMENT 'Brief biographical information',
    
    created_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP,
    
    updated_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP,
    
    created_by UUID
        REFERENCES users(user_id)
        COMMENT 'User who created the author entry',
    
    verified BOOLEAN
        DEFAULT false
        COMMENT 'Whether author info has been verified',
    
    CONSTRAINT birth_before_death
        CHECK (birth_date < death_date)
);

Collections System

-- Main collections table for organizing quotes
CREATE TABLE collections (
    collection_id UUID
        PRIMARY KEY
        COMMENT 'Unique identifier for the collection',
    
    user_id UUID
        REFERENCES users(user_id)
        NOT NULL
        COMMENT 'Owner of the collection',
    
    name VARCHAR(255)
        NOT NULL
        COMMENT 'Collection name',
    
    description TEXT
        COMMENT 'Collection description',
    
    is_public BOOLEAN
        DEFAULT false
        COMMENT 'Visibility setting',
    
    created_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP,
    
    updated_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT unique_user_collection_name
        UNIQUE (user_id, name)
);

-- Junction table for quotes in collections
CREATE TABLE collection_quotes (
    collection_id UUID
        REFERENCES collections(collection_id)
        ON DELETE CASCADE,
    
    quote_id UUID
        REFERENCES quotes(quote_id)
        ON DELETE CASCADE,
    
    added_at TIMESTAMP WITH TIME ZONE
        DEFAULT CURRENT_TIMESTAMP
        COMMENT 'When the quote was added to collection',
    
    PRIMARY KEY (collection_id, quote_id)
);

Common Query Patterns

1. Fetching a Quote with Related Data

SELECT 
    q.quote_id,
    q.text,
    a.name AS author_name,
    s.title AS source_title,
    array_agg(DISTINCT c.name) AS categories,
    array_agg(DISTINCT t.name) AS tags
FROM quotes q
LEFT JOIN authors a ON q.author_id = a.author_id
LEFT JOIN sources s ON q.source_id = s.source_id
LEFT JOIN quote_categories qc ON q.quote_id = qc.quote_id
LEFT JOIN categories c ON qc.category_id = c.category_id
LEFT JOIN quote_tags qt ON q.quote_id = qt.quote_id
LEFT JOIN tags t ON qt.tag_id = t.tag_id
WHERE q.quote_id = [quote_id]
GROUP BY q.quote_id, a.name, s.title;

2. User’s Favorite Quotes

SELECT 
    q.text,
    a.name AS author_name,
    uf.favorited_at
FROM user_favorites uf
JOIN quotes q ON uf.quote_id = q.quote_id
JOIN authors a ON q.author_id = a.author_id
WHERE uf.user_id = [user_id]
ORDER BY uf.favorited_at DESC;

3. Finding Similar Quotes

SELECT 
    q2.quote_id,
    q2.text,
    similarity(q1.text, q2.text) AS similarity_score
FROM quotes q1
JOIN quotes q2 ON q1.quote_id != q2.quote_id
WHERE q1.quote_id = [quote_id]
AND similarity(q1.text, q2.text) > 0.3
ORDER BY similarity_score DESC
LIMIT 5;

Data Integrity Rules

Cascading Deletes:
- Collection quotes are deleted when collection is deleted
- Tags are preserved when quotes are deleted
- Annotations are preserved when quotes are deleted
Constraints:
- Authors must have name
- Quotes must have text and author
- Collections must have owner and name
- Birth date must be before death date
Soft Deletes:
- User accounts are deactivated, not deleted
- Quotes are marked private, not deleted
- Maintains data integrity and history

A comprehensive overview of all database relationships and the indexing strategy.

Complete Database Relationships Analysis

Direct Relationships

User-Related Relationships

Users → Roles (Many-to-Many)
- Through user_roles junction table
- Enables flexible permission management
- users.user_id → user_roles.user_id
- roles.role_id → user_roles.role_id
Users → Collections (One-to-Many)
- One user can own multiple collections
- collections.user_id → users.user_id
Users → Quotes (One-to-Many)
- As creator: quotes.created_by → users.user_id
- As favorite: Through user_favorites
- As annotator: Through user_annotations

Quote-Related Relationships

Quotes → Authors (Many-to-One)
- Each quote has one author
- quotes.author_id → authors.author_id
- Required relationship (NOT NULL)
Quotes → Sources (Many-to-One)
- Each quote can have one source
- quotes.source_id → sources.source_id
- Optional relationship (NULL allowed)
Quotes ↔ Categories (Many-to-Many)
- Through quote_categories junction table
- Hierarchical categorization possible
- quotes.quote_id → quote_categories.quote_id
- categories.category_id → quote_categories.category_id
Quotes ↔ Tags (Many-to-Many)
- Through quote_tags junction table
- Flat tagging structure
- quotes.quote_id → quote_tags.quote_id
- tags.tag_id → quote_tags.tag_id
Quotes → Translations (One-to-Many)
- One quote can have multiple translations
- quote_translations.quote_id → quotes.quote_id

Collection-Related Relationships

Collections ↔ Quotes (Many-to-Many)
- Through collection_quotes junction table
- collections.collection_id → collection_quotes.collection_id
- quotes.quote_id → collection_quotes.quote_id

Verification-Related Relationships

Quotes → Verification History (One-to-Many)
- Tracks all verification status changes
- verification_history.quote_id → quotes.quote_id
- verification_history.verified_by → users.user_id

Category-Related Relationships

Categories → Categories (Self-Referential)
- Enables hierarchical category structure
- categories.parent_category_id → categories.category_id
- Optional relationship (NULL for root categories)

Indexing Strategy

1. Primary Key Indexes

Automatically created for all PRIMARY KEY constraints
Uses B-tree data structure
Example tables: users, quotes, authors, collections

-- Already created by PRIMARY KEY constraints
CREATE INDEX pk_users ON users(user_id);
CREATE INDEX pk_quotes ON quotes(quote_id);
CREATE INDEX pk_authors ON authors(author_id);

2. Foreign Key Indexes

-- Quote relationships
CREATE INDEX idx_quotes_author ON quotes(author_id);
CREATE INDEX idx_quotes_source ON quotes(source_id);
CREATE INDEX idx_quotes_created_by ON quotes(created_by);

-- Collection relationships
CREATE INDEX idx_collections_user ON collections(user_id);
CREATE INDEX idx_collection_quotes_quote ON collection_quotes(quote_id);

-- Category relationships
CREATE INDEX idx_categories_parent ON categories(parent_category_id);

-- User interaction indexes
CREATE INDEX idx_user_favorites_user ON user_favorites(user_id);
CREATE INDEX idx_user_annotations_user ON user_annotations(user_id);
CREATE INDEX idx_user_annotations_quote ON user_annotations(quote_id);

3. Full-Text Search Indexes

-- Quote text search
CREATE INDEX idx_quotes_text_search ON quotes 
USING GIN (to_tsvector('english', text));

-- Author name search
CREATE INDEX idx_authors_name_search ON authors 
USING GIN (to_tsvector('english', name));

-- Source title search
CREATE INDEX idx_sources_title_search ON sources 
USING GIN (to_tsvector('english', title));

4. Composite Indexes for Common Queries

-- User collections by name
CREATE INDEX idx_collections_user_name ON collections(user_id, name);

-- Quotes by language and verification status
CREATE INDEX idx_quotes_lang_status ON quotes(language_code, verification_status);

-- Quote categories with timestamp
CREATE INDEX idx_quote_categories_timestamp ON quote_categories(quote_id, category_id, created_at);

5. Performance Optimization Indexes

-- Timestamp-based queries
CREATE INDEX idx_quotes_created_at ON quotes(created_at);
CREATE INDEX idx_quotes_updated_at ON quotes(updated_at);

-- Verification status queries
CREATE INDEX idx_quotes_verification ON quotes(verification_status);

-- Public/private content queries
CREATE INDEX idx_quotes_public ON quotes(is_public);
CREATE INDEX idx_collections_public ON collections(is_public);

6. Specialized Indexes

-- B-tree index for range queries on dates
CREATE INDEX idx_authors_dates ON authors(birth_date, death_date);

-- Hash index for exact matches
CREATE INDEX idx_tags_name_hash ON tags USING HASH (name);

-- GiST index for similarity searches
CREATE INDEX idx_quotes_similarity ON quotes 
USING gist (text gist_trgm_ops);

Index Maintenance Considerations

Automated Maintenance

-- Regular index statistics update
ALTER TABLE quotes SET (autovacuum_analyze_scale_factor = 0.1);
ALTER TABLE quotes SET (autovacuum_vacuum_scale_factor = 0.2);

Index Usage Monitoring

-- Query to monitor index usage
SELECT 
 schemaname || '.' || tablename as table_name,
 indexname,
 idx_scan as number_of_scans,
 idx_tup_read as tuples_read,
 idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT LIKE 'pg_%'
ORDER BY schemaname, tablename;

Performance Impact Considerations

Indexes improve query performance but impact write operations
Regular monitoring of index usage
Removal of unused indexes
Periodic reindexing of fragmented indexes

Trip tracker

Key Features

User Onboarding and Profiles: Collect basic user details, such as nationality, home country, and countries they frequently visit or plan to visit. This will help determine relevant visa requirements and legal limits.
Trip Tracking (Entries & Exits):

Entry/Exit Logging: Users should be able to log their entry and exit dates for each country.
Automatic Date Calculation: Calculate the total number of days spent in each country based on entry/exit dates.
Remaining Days Alert: Notify users of remaining days in the country before reaching legal limits.

Legal Limit Information:

Visa and Residency Rules Database: Track different types of visas and residency rules (e.g., Schengen 90/180-day rule, tourist visas, digital nomad visas, etc.).
Country-Specific Limits: Keep a database of countries’ unique requirements, such as day limits, renewal options, and penalties for overstaying.

Notifications and Alerts:

Threshold Alerts: Notify users when they are nearing their legal stay limit in a particular country.
Visa Expiry Reminders: Alert users when they need to renew or apply for a new visa.

Dashboard and Reports:
- Overview of Time Spent: Show a summary of days spent across all visited countries within a certain period.
- Historical Log: Provide a log of past visits for each country.
- Compliance Status: Indicate compliance or risk level for each country based on historical stays and upcoming plans.
Analytics:
- Day-by-Day Calculator: Calculate remaining days under specific visa requirements, e.g., 90 days in a 180-day rolling period for the Schengen Area.
- Forecasting: Estimate how long a user can legally stay in a country in the future if they re-enter on a particular date.

Implementation Steps

Database Design:
- User Table: Stores user info (e.g., name, nationality, home country).
- Country Rules Table: Includes visa requirements, day limits, and other legal limits for each country.
- Visit Logs Table: Tracks entry and exit dates per user per country.
- Notification Table: Logs alerts sent to users, such as nearing legal limit notifications.
Backend Logic:
- Date Calculation: Implement logic to calculate the total number of days in each country, accounting for rolling periods (e.g., Schengen 90/180 rule).
- Threshold Detection: Use scheduled jobs to check if users are nearing their day limits and trigger notifications.
- Visa & Residency Rule Lookup: Develop a service to fetch visa rules and legal limits based on user nationality and destination country.
User Interface:
- Dashboard: Display a map or list view with color-coding for days remaining (e.g., green for under limit, yellow nearing, red if over limit).
- Entry/Exit Form: Simple form for users to input entry and exit dates.
- Notifications & Alerts: Provide an area for users to view alerts related to nearing limits and expiring visas.
APIs and Integration:
- Visa Rules API: If possible, integrate with an external API for visa and residency information (if a reliable one exists).
- Calendar Sync: Allow users to sync entry/exit dates with their calendars for easier tracking.
Testing and Validation:
- Simulate Different Scenarios: Test with various travel scenarios and visa rules to ensure calculations are accurate.
- User Feedback: Conduct user testing to ensure usability, especially for notifications and alerts.

This tracker could be particularly valuable for digital nomads and remote workers who travel frequently, offering peace of mind and a simple way to ensure compliance with travel and visa restrictions.

Up-to-the-minute tracking of a user’s remaining days in each country.

Additional Features

Travel History Analytics
- Country Trends: Visual summaries showing which countries users visit most often, average duration of stays, and time of year.
- Total Time Abroad: Track the cumulative days spent abroad over a chosen period, which could help with tax considerations or visa renewals. • Interactive Map: Show a map with heat zones or markers based on recent travel, giving users a visual sense of their travel patterns.
Tax Residency and Compliance Checker
- Home Country Tax Requirements: Provide information on tax residency rules for users’ home countries (e.g., 183-day rule in many countries).
- Country-Specific Tax Alerts: Warn users when they’re approaching the threshold for tax residency in a foreign country, which could have significant implications for freelancers.
- Income Reporting Reminders: Provide reminders to users based on their time in a country, especially if they need to report income or file taxes locally.
Document Storage and Expiry Reminders
- Document Uploads: Allow users to store digital copies of important documents, such as passports, visas, residence permits, or work permits, in an encrypted section of the app.
- Expiry Notifications: Automatically send reminders when important documents are approaching expiration (e.g., passport or visa renewal deadlines).
Visa and Legal Compliance Assistance
- Customized Visa Recommendations: Based on travel patterns, suggest visa types or residency permits that might better suit a user’s needs.
- Local Contact Info for Embassies: Provide embassy and consulate contact information based on the user’s current location, in case they need assistance.
- Real-Time Compliance Check: Let users simulate future trips and test if these will impact their legal days. For instance, “If I enter the Schengen Area on March 1, can I still stay 30 days legally?”
Community Insights and Ratings
- Crowdsourced Information: Allow users to leave reviews or insights about visa processes, embassy wait times, or compliance experiences for specific countries.
- Country-Specific Tips: Enable users to share tips specific to freelancers and nomads, such as internet speed, co-working spaces, or short-term rental resources.
- Local Freelance/Remote Work Resources: Suggest local resources for remote workers, like coworking spaces, networking events, or digital nomad communities.
Social and Networking Features
- Proximity Alerts: Notify users if other app users are currently in the same city or country and are open to connecting (opt-in for privacy).
- User Groups by Country: Create country-based or city-based groups within the app to foster connections among users in the same area.
Integration with Travel and Expense Management
- Automatic Entry/Exit Tracking with GPS (if opted-in): Allow users to automatically track their movements between countries, removing the need for manual entry of entry/exit dates. They could receive a notification whenever they cross into a new country.
- Expense and Currency Tracker: Track expenses in local currency, then aggregate them for a specific trip or country. This could be especially useful for budgeting and reporting purposes for freelancers.
- Flight and Accommodation Booking Integration: Integrate with travel services to auto-fill entry/exit dates based on bookings, simplifying the logging process.
Visa-Related Insurance & Health Resources
- Insurance Recommendations: Depending on a user’s destination, suggest relevant travel, health, or liability insurance options that fulfill visa or entry requirements.
- COVID-19 or Health Entry Requirements: Offer current entry requirements, including any vaccine, health insurance, or testing requirements, and keep them updated as regulations change.
AI-Powered Visa/Residency Assistant
- Intelligent Stay Planning: A smart assistant could suggest optimal dates or even generate travel plans that maximize stay lengths while complying with legal requirements (e.g., suggesting specific entry dates for the Schengen Area based on past stays).
- Q&A Bot for Visa Questions: An AI-powered chat assistant could answer common questions about visa requirements, country-specific limitations, or document needs, providing real-time support.
Offline Mode
- Offline Access and Logging: Let users log entries/exits and view their days remaining even without an internet connection. Sync data automatically once they’re back online, which is useful when traveling to remote areas with limited connectivity.

Technical Enhancements with Phoenix LiveView

Phoenix LiveView offers some excellent opportunities for seamless real-time interactions and notifications, which could enhance several of these features:

Real-Time Compliance Check: With LiveView’s real-time updates, users could immediately see the effect of a logged entry or exit on their remaining days.
Collaborative Features: If users share their travel plans with a friend or colleague, they could see updates live, such as meeting up in a shared destination.
Interactive Map with Live Data: Real-time map adjustments based on user entries, showing compliance status, visa availability, and other key information.

These extra features would make the app more than just a travel tracker; it could become a centralized toolkit for remote workers managing compliance, finances, and social connectivity while on the go.

Additional and Advanced Feature Ideas

AI-Powered Travel and Visa Recommendations
- Personalized Destination Suggestions: Based on user profiles and previous travel patterns, offer destination suggestions that align with visa limitations, lifestyle preferences, cost of living, or work infrastructure quality (e.g., internet speed, co-working spaces).
- Adaptive Visa Calculator: An AI-powered tool that adapts to the user’s nationality and travel history to dynamically recommend visa options, especially useful for countries with complex or changing visa policies.
Tax Optimization and Residency Recommendations
- Tax Residency Optimizer: For high-frequency travelers, this feature could analyze a user’s travel history and provide tax residency suggestions that minimize legal or financial risk.
- Income Split Tracking: Assist freelancers and remote workers in managing income streams based on location, allowing them to attribute revenue to specific countries and potentially reduce tax liabilities.
Customized Compliance Rules for Different User Types
- Freelancer vs. Employee Mode: Customize compliance rules for freelancers, remote employees, and entrepreneurs, as these groups often face different regulatory requirements in various countries.
- Income Declaration Notifications: For users who operate businesses or freelance in multiple locations, automatically alert them if certain countries require income declarations above specific thresholds (e.g., Brazil, Japan).
Multi-Country Permit or Long-Term Visa Planner
- Digital Nomad Visa Planner: Provide information on and track eligibility for digital nomad visas, long-term work visas, or residency permits in various countries.
- Multi-Stop Journey Calculator: For those who travel between specific countries frequently, calculate an optimized route to maximize allowable stay time across all destinations (e.g., managing time across multiple Schengen and non-Schengen countries).
Financial and Cost of Living Tracker
- Local Currency Conversion & Cost Tracking: Track expenses in local currency, and provide insights on exchange rates and costs of living. A budget comparison tool could show users how their expenses stack up in each country.
- Expense Category Analysis: Track expenses by category (e.g., housing, food, travel) to give users insights into their cost of living per country and per trip.
- Digital Nomad Cost of Living Database: Combine community contributions with data from sources like Numbeo to display cost comparisons for popular nomad destinations.
Cross-Platform Accessibility and Integrations
- Mobile App with Offline Mode: Extend the app to a mobile version with an offline mode that allows travelers to log entries/exits without an internet connection.
- Calendar Integrations for Travel Logging: Sync with calendars (Google, iCal, Outlook) to automatically track trips based on calendar entries and travel bookings.
- Expense and Time-Tracking Tool Integration: Integrate with popular freelancer tools like Toggl or FreshBooks to help users track their work time and expenses per location.
Country Culture and Etiquette Tips
- Local Etiquette Reminders: Provide users with useful information on cultural etiquette, customs, and tips (e.g., tipping, language basics, holiday closures).
- Digital Nomad Lifestyle Guides: Offer information on local co-working spaces, digital nomad communities, and networking events for freelancers or remote workers.
Interactive Trip Planning and Visualization Tools
- Visa-Friendly Route Planner: An interactive tool that visualizes an ideal route through visa-compliant countries for long-term trips, highlighting cost-effective routes, weather conditions, and visa regulations.
- Customizable Travel Map: Let users mark and customize a global map with visited and planned destinations, allowing them to keep a visual record of their travels.
Emergency Resources and Safety Information
- Emergency Contacts and Health Facilities: Provide a list of local emergency contacts (e.g., 911 equivalent), hospitals, and health facilities relevant to each country.
- Safety Score and Alerts: Provide users with real-time safety scores or alerts for their current location. Integrate with travel safety services like SafeTravel or TravelSafe for updates on political situations, natural disasters, or health alerts.
Enhanced Privacy and Data Security Features
- Selective Data Sharing and Privacy Controls: Allow users to toggle visibility on specific data, so they can choose to share their travel history only with select individuals or groups.
- Encrypted Document Vault: Store users’ documents and personal data in a secure, encrypted vault accessible only with two-factor authentication.
- Zero-Knowledge Data Option: For those who prioritize privacy, offer a “zero-knowledge” data storage option, where only the user holds the encryption keys, ensuring even app admins can’t access stored data.

Suggested User Engagement Features

To build a strong community and improve retention, consider adding features that increase user engagement and social interaction:

Weekly or Monthly Summary Emails: Send automated summaries of recent travels, upcoming visa expirations, and day limits to keep users engaged and informed.
Gamification Elements: Introduce achievement badges (e.g., “Schengen Pro” for users who maximize their 90/180 days or “Country Hopper” for frequent travelers) to make tracking progress enjoyable.
Referral Program: Encourage users to invite others through a referral program, offering credits or access to premium features for successful referrals.

Premium Features for Monetization

If you plan to offer a free version, consider a tiered approach with premium features like: • Advanced Visa Insights and Notifications: Real-time updates and in-depth visa information for paid users. • Multi-Currency Expense Analysis: Advanced financial analysis tools, such as a multi-currency tracker and a personal finance assistant. • Priority Support and Visa Assistance: Offer premium users priority support for queries related to visas, compliance, and other travel-related issues.

Personalized Country Readiness Score
- Readiness Score Calculation: Create a readiness score for each country based on the user’s requirements. For instance, if someone prioritizes ease of entry, safety, cost of living, internet speed, and co-working availability, this score could dynamically adjust based on these criteria and current conditions.
- Customizable Criteria: Allow users to select what matters most (e.g., safety, visa ease, tax implications, healthcare access) so that the app tailors suggestions accordingly.
Future Travel “Sandbox”
- Trip Simulation Mode: Users could simulate potential trips to see how each planned stay affects visa compliance, tax residency, and financial expenses. For example, if a user adds a hypothetical 2-month stay in Thailand followed by a month in Japan, the app would show how this affects their overall compliance and cost projections.
- Best Time to Enter Tool: For countries with visa resets or rolling period requirements, this tool could suggest the optimal times to enter to maximize the allowable stay, ideal for Schengen zone travel or countries with specific re-entry rules.
Currency & Exchange Rate Optimizer
- Real-Time Conversion: Track expenses and show real-time currency conversion based on the user’s home currency. This could help freelancers manage their finances across multiple currencies and avoid surprises due to fluctuating exchange rates.
- Preferred Currency Alert: For major transactions (e.g., accommodation, flights), notify users when a preferred currency (or a favorable exchange rate) is available to help them save on large expenses.
Nomad Health Toolkit
- Local Health Tips: Provide health advisories specific to the user’s current or upcoming location, such as common illnesses, seasonal health considerations, or vaccine requirements.
- Mental Wellness Resources: Nomadic life has unique stressors, so offer access to resources like remote counseling services, mindfulness exercises, or community wellness events.
- Health Insurance Recommendations: Tailored to the user’s travel profile, offer suggestions for health and travel insurance, and link to providers with options for expats and nomads.
Localized Remote Work Productivity Boosters
- Internet Speed and Connectivity Tips: Pull data from user contributions and sources like Speedtest to show expected internet speeds at popular spots.
- Coworking and Quiet Spaces Locator: Provide an updated list of coworking spaces, cafes, and libraries in the user’s area, along with reviews, so they can find productive spots quickly.
- Focus Mode and Noise Levels: Create a local focus mode that shows noise levels in various locations and suggests quiet times or alternative spots for focused work.
Automatic Compliance Documentation Generator
- Visa and Residency Paperwork Generator: Generate draft documents (like visa applications, tax forms, etc.) based on pre-entered user data to make paperwork less burdensome.
- Travel History Report: Allow users to download an official report of their travel history, which can be used as supporting documentation for visa applications, tax filings, or compliance checks.
- Customizable Templates: Provide templates for letters or forms that are often needed, such as proof of residency, bank letters for visa purposes, and travel itineraries.
Carbon Footprint Tracker
- Flight Emissions Calculator: Allow users to track the carbon footprint of their travel. By entering or importing flight and transportation details, the app could estimate the total emissions produced.
- Offset Suggestions: Offer carbon offsetting recommendations or partnerships with offset programs. This would appeal to environmentally conscious travelers who want to minimize their impact.
AI-Based Work-Life Balance and Scheduling Suggestions
- Optimal Time Zone Finder: For users working with international teams, provide a tool to find the best working time zone to maximize overlap with clients or teams.
- Work-Life Balance Insights: Analyze the user’s travel patterns and suggest ways to improve work-life balance. For instance, if a user is moving too frequently and working long hours, the app could recommend “focus-friendly” destinations to facilitate a healthier routine.
- Time Blocking for Nomads: Suggest work and leisure time blocks based on the local time zone, typical weather, or local cultural activities, helping users build a rhythm suited to their environment.
Integrated Language Learning Mini-Tools
- Quick Language Tips: Offer quick, situation-specific language tips or phrase guides based on the user’s location (e.g., ordering food, asking for directions, or common pleasantries).
- Local Language Flashcards: A quick study tool that users can access before or during their stay, focusing on useful vocabulary relevant to nomads.
- Translation Shortcuts: Built-in translation tool that provides instant translations for common phrases and integrates with the app for easy access.
Digital Nomad “Matchmaking” for Collaboration
- Project-Based Networking: Match users with similar interests, allowing them to connect with other nomads for collaboration on projects, co-working, or social networking.
- Skills Exchange Board: Allow users to exchange services (e.g., a web designer might trade skills with a copywriter) in a currency-free format that’s valuable for nomads.
- Language Exchange Pairs: Pair users up for language exchange sessions if they’re in the same area, which can provide a low-stakes way to meet others and learn the local language.

Technical Enhancements with Phoenix LiveView

LiveView’s dynamic nature will make a few of these advanced features not only possible but also smooth and responsive:

Real-Time Cost and Currency Updates: With LiveView, you could provide live currency conversion within expense trackers, instantly recalculating as the user adds new entries.
Travel Dashboard with Notifications: Create an always-updated compliance dashboard that refreshes as users log new entries and exits, helping them make on-the-go adjustments.
AI-Assisted Suggestions in Real-Time: Using Elixir’s concurrency capabilities, implement lightweight AI functions for compliance checking or visa recommendations that run seamlessly alongside user actions.

These additional features would make the app not just a compliance tool but a comprehensive lifestyle and productivity suite for digital nomads, addressing their needs for adaptability, resourcefulness, and community while helping them navigate complex legal landscapes.

Let’s take the app into “next-level” territory, where it becomes an all-encompassing lifestyle, productivity, and compliance hub for digital nomads. Here’s how we could push the boundaries even further:

Nomad Lifestyle and Location-Driven Personalization
- Smart Destination Personas: Build personalized destination profiles based on user behavior, interests, and past choices. For example, if a user frequently visits beach destinations with active nightlife, the app could suggest similar options worldwide.
- AI-Powered Local Highlights: An AI engine that learns from user preferences and curates hyper-local suggestions for activities, dining, and events tailored to individual tastes and work habits.
Real-Time Local Integration with Smart Home Systems
- Smart Home Compatibility: Integrate with smart home and IoT devices (e.g., Google Home, Alexa) to automate daily routines. For example, automatic wake-up calls based on time zone changes or location-based reminders for tasks or meetings.
- Environment Adaptation: Based on location data, adjust settings in a user’s devices (e.g., light preferences for jetlag adjustment, white noise generation in noisy areas) for a seamless environment wherever they are.
Advanced Global Health and Safety Toolkit
- Location-Based Health Alerts: Integrate with health databases to provide real-time health and safety alerts, such as local outbreaks, pollution levels, or regional safety warnings, with proactive recommendations like vaccines or health check-ups.
- Dynamic “Go/No-Go” Score: Calculate and show a score for health and safety, real-time updates on political stability, healthcare quality, or visa situation for every country, and even drill down to the city level.
Augmented Reality (AR) Location Assistance
- AR Travel Guide: Allow users to point their phone camera at landmarks or venues to get instant, on-screen information such as travel tips, safety notices, or crowdsourced tips for a specific place.
- AR Navigation to Work-Friendly Spaces: Display AR directions to coworking spaces, cafes, or important landmarks, overlaying useful details like quiet times, availability of outlets, or current crowd size.
Digital Nomad Banking and Financial Optimization
- Nomad-Specific Banking Options: Partner with digital-first banks (like Revolut or Wise) to provide seamless access to cross-border accounts, low-fee transfers, and remote-friendly financial services.
- Auto-Transfer and Currency Conversion Recommendations: Use AI to detect favorable exchange rates and suggest timing for currency exchanges or bank transfers, minimizing conversion fees.
- Tax-Friendly Bank Accounts: For nomads needing banking in multiple regions, recommend tax-friendly accounts based on the user’s primary business needs and personal tax situation.
AI-Powered Content Creation Tools for Freelancers
- Writing and Research Assistant: A built-in, AI-powered content creation assistant tailored to freelance writers or bloggers. Integrate with writing tools and provide automatic language localization, content ideas, and SEO insights based on trends.
- Remote Work Portfolio Optimizer: For freelance creatives, provide automated portfolio analysis and suggestions on how to optimize for the next destination (e.g., trends in Japan if they’re headed there).
- Dynamic Pricing Suggestion Tool: For freelancers who adjust their rates based on local costs of living, provide a pricing assistant that suggests optimal rates for services based on local economic data and platform rates in that region.
Nomad-Friendly Property and Relocation Services
- Short-Term Housing Matching: Partner with local housing providers and landlords who cater to nomads, allowing users to secure housing for weeks or months at a time, with special features like furnished apartments, co-living spaces, and flexible contracts.
- Relocation Concierge Services: Offer in-app concierge services that assist with legal requirements, settling-in support, and even relocation planning for popular nomad hubs like Bali or Lisbon.
- Nomad Homestay Exchanges: Enable a peer-to-peer property exchange where nomads can “swap” their rental homes temporarily, fostering an affordable way to travel and stay around the world.
Intelligent Visa and Residency Progression Planner
- Path to Residency AI Tool: For nomads considering long-term residency or citizenship in a particular country, suggest the best visa path based on the user’s travel habits, business needs, and residency aspirations.
- Live Residency Eligibility Tracker: Keep a real-time counter of days in each country and provide instant feedback on eligibility for residency or long-term visa options as thresholds approach.
- Country-Switching Advice: Suggest visa resets by recommending nearby “visa-friendly” countries, allowing users to maximize stay limits while complying with legal requirements.
Collaborative Nomad Community Projects
- Skill-Building Circles and Micro-Workshops: Let users host and join micro-workshops within the app, sharing expertise with each other on topics like client management, project bids, or personal finance for nomads.
- Collaborative Workspaces and Group Trips: Organize and manage co-living or co-working retreats, allowing nomads to work together in new locations for a set period with organized activities.
- Nomad-to-Nomad Gigs: Offer users the ability to hire other nomads for small gigs, from photography to logo design, fostering a freelance support ecosystem.
Adaptive Legal and Tax Advisory Platform
- AI Legal Assistant for International Compliance: Using AI, provide customized advice on international tax, intellectual property, and client contracts based on local laws, ideal for freelancers doing business in multiple countries.
- Pro-Bono or Low-Cost Legal Services: Partner with legal firms specializing in expat and digital nomad issues to offer users affordable consultations for visa issues, tax laws, or compliance questions.
- Personalized Tax Filing Assistance: For users reaching residency thresholds or tax complexities, provide automated tax filing assistance in collaboration with accountants who specialize in expat and international digital nomad clients.
Machine Learning-Driven Work-Life Recommendations
- Machine Learning for Lifestyle Balancing: Analyze travel, work, and social patterns to suggest routines that optimize well-being, work efficiency, and travel enjoyment (e.g., advising rest periods between countries, reminding users to socialize).
- Personalized Nomad Habits: Track individual work rhythms, downtime, and social patterns to give custom advice on managing burnout, work-life balance, and staying healthy while on the move.
Customizable Nomad Dashboard with Real-Time Insights
- User-Centric Dashboard: Let users create a personalized dashboard that displays the metrics most important to them: visa days, productivity stats, budget tracking, project deadlines, or nomad achievements.
- Nomad Leaderboards: Add a fun, optional “leaderboard” for friendly competition among nomads (e.g., “Most Visited Countries This Year,” “Longest Stay in One Place,” or “Most Active in the Community”).
- Travel Cost Projection: Automatically pull data from various sources to give real-time projections of costs for upcoming locations, helping nomads anticipate expenses better.

Adding integrations with GraphQL or RESTful APIs will also ensure the app connects effortlessly with third-party services, especially for financial, health, or location data updates. Phoenix Contexts will be ideal to keep the code modular, separating user-centric services, financial calculators, and AI systems into well-defined domains for easy expansion.

Together, these features would make your app a total lifestyle solution for digital nomads—one that goes beyond travel compliance to offer deep personalization, AI-driven support, and a robust global community. It could truly transform the digital nomad lifestyle!

Several regions and economic blocks have collective visa or entry policies that apply to the entire block rather than individual countries. Here’s an overview of some key visa blocks and regional entry agreements that function similarly to the Schengen Area:

Schengen Area (Europe)

Countries: 27 European countries, including France, Germany, Italy, Spain, and others.
Entry Policy: Travelers can move freely between member states with a single Schengen visa. Stays are typically limited to 90 days within a 180-day period for non-residents.
Note: Some countries, like Switzerland and Norway, are in the Schengen Area but not in the EU, which means the Schengen visa policy applies without EU residency rights.

CARICOM Single Market and Economy (CSME)

Countries: Caribbean nations like Barbados, Jamaica, Trinidad and Tobago, and others within the Caribbean Community (CARICOM).
Entry Policy: Nationals of CARICOM countries are often allowed to travel visa-free between member states for extended stays, especially if they are engaging in work or business.
Note: For non-CARICOM nationals, visa policies may differ by country within CARICOM, but agreements for the free movement of people are expanding.

Gulf Cooperation Council (GCC)

Countries: Middle Eastern countries including Saudi Arabia, UAE, Qatar, Bahrain, Kuwait, and Oman.
Entry Policy: GCC nationals can travel freely between member states without a visa. However, for tourists and foreign workers, a visa policy may vary slightly between countries.
Visa Options for Visitors: Some member states issue a GCC Resident Visa, which can allow residents of one GCC country to visit others for short stays without needing a separate visa.

Central America-4 (CA-4) Border Control Agreement

Countries: Guatemala, El Salvador, Honduras, and Nicaragua.
Entry Policy: Under the CA-4 agreement, visitors can travel freely between these countries for up to 90 days total within the region (not per country). Extensions may be available but apply to the region as a whole, not individual countries.
Note: This is similar to the Schengen model but with fewer countries involved and more limited mutual agreements.

East African Tourist Visa

Countries: Kenya, Rwanda, and Uganda.
Entry Policy: A single East Africa Tourist Visa allows entry to all three countries for 90 days. Travelers can move freely between these countries within this period.
Note: This visa is available for tourism only. Other travel purposes, like work or study, require individual visas.

Pacific Alliance

Countries: Chile, Colombia, Mexico, and Peru.
Entry Policy: While there is no unified tourist visa yet, the Pacific Alliance aims to facilitate free movement. Citizens of these countries can often travel visa-free within the alliance for short stays, and member states are working on mutual visa waiver agreements for broader mobility.
Note: For tourists from outside the alliance, visa policies may vary by country, but the alliance has plans to harmonize entry requirements in the future.

Mercosur (Southern Common Market)

Countries: Argentina, Brazil, Paraguay, Uruguay, and associate members like Chile, Bolivia, and Peru.
Entry Policy: Nationals of Mercosur countries can travel freely for work, residence, and tourism within the bloc. Tourists from non-Mercosur countries must comply with individual visa policies per country.
Note: Member countries offer temporary residence visas to other Mercosur nationals, which allows longer stays and simplified work authorization.

ASEAN (Association of Southeast Asian Nations)

Countries: Brunei, Cambodia, Indonesia, Laos, Malaysia, Myanmar, Philippines, Singapore, Thailand, Vietnam.
Entry Policy: ASEAN nationals can travel visa-free within the bloc for tourism for varying durations depending on the countries involved. Efforts are underway to create an ASEAN common visa for non-ASEAN tourists, though it’s still in development.
Note: For now, the ASEAN common visa remains a goal, and each country maintains its own visa policy for non-ASEAN nationals.

Trip Tracker schema

Data structure

Entities for Users, Trips, Countries, Visas, Documents, and Notifications.

Database Schema Overview

The database schema includes the following main tables:

users – Stores user information and profile details.
trips – Logs individual trips, capturing entry/exit dates, location, and user ID.
countries – Stores country-specific information, including visa requirements and legal stay limits.
visas – Holds visa types, requirements, and stay limits for each country.
notifications – Logs notifications sent to users for reminders and alerts.
documents – Stores document metadata for user-related documents, such as visas, IDs, and passports.
analytics – Aggregates data for reporting and analysis of travel patterns, visa use, and other metrics.

Table: countries

Stores information about each country and its travel regulations.

Column	Type	Description
id	UUID	Primary key, unique identifier for each country.
name	VARCHAR	Country name.
code	VARCHAR	ISO country code.
currency	VARCHAR	Primary currency used in the country.
updated_at	TIMESTAMP	Last update timestamp for the country data.

Table document_types

This table will store all possible document types.

CREATE TABLE document_types (
    id UUID PRIMARY KEY,            -- Unique identifier for the document type
    name VARCHAR NOT NULL UNIQUE,   -- E.g., "Passport," "Visa," "ID Card," etc.
    description TEXT,               -- Optional: description of the document type
    default_validity_period INTERVAL, -- Default validity for this type (e.g., "10 years")
    notification_lead_time_days INT DEFAULT 30,    -- Recommended days before expiry to trigger renewal notification
    processing_time_estimate_days INT DEFAULT 30,   -- Estimated document issuance processing and delivery time in days 
    default_buffer_time_days INT DEFAULT 14,    -- Default safety buffer, in days, for user action
    template_id UUID REFERENCES document_templates(id), -- Optional reference to template
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Field Explanation

lead_time_days:

Defines the number of days before the expiry date when a notification should be triggered.
- Default value of 30 days ensures timely reminders for most documents but can be customized for each document type.

processing_time_estimate_days:
- Estimated number of days required by the issuing authority to process and deliver the document.
default_buffer_time_days:
- Default extra days added as a buffer for user action. This can account for unforeseen delays or personal preparation time.

Example Data

Table document_types

INSERT INTO document_types (id, name, description, notification_lead_time, processing_time_estimate, default_user_buffer_time) VALUES
('1', 'Passport', 'Official travel document issued by a country', 180, 30, 14), -- Notify 6 months + processing + buffer
('2', 'Visa', 'Permit allowing entry into a country', 30, 20, 7),              -- Notify 1 month + processing + buffer
('3', 'ID Card', 'National identification card', 90, 45, 10),                 -- Notify 3 months + processing + buffer
('4', 'Work Permit', 'Document authorizing employment in a foreign country', 60, 60, 14); -- Notify 2 months + processing + buffer

Example Notification Workflow

Regular Check:
- A cron job or a background process periodically runs the query above to fetch documents nearing expiry.
- Example: A job could run every day at midnight to check for documents triggering notifications.
Notification Generation:
- The application generates notifications or reminders for users whose documents meet the criteria.
- Notifications could be stored in a table or directly sent via email/alerts.

Enhancing User Flexibility

For greater personalization, allow users to override the default buffer time per document. Add a field in the documents table to specify a custom buffer time.

Table: documents

Tracks document details for each user, including expiry dates and types.

Column	Type	Description
id	UUID	Primary key for each document record.
user_id	UUID	Foreign key referencing users(id).
document_type_id	UUID	Normalized type of document (e.g., passport, visa).
document_reference	VARCHAR	Unique reference number for the document, given by the issuing authority.
issuing_authority	VARCHAR	Name of the authority that issued the document.
description	TEXT	Document description or other notes.
country_id	UUID	Foreign key referencing countries(id).
issue_date	DATE	Date when the document was issued.
expiry_date	DATE	Expiry date of the document.
is_active	BOOLEAN, DEFAULT TRUE	Indicates if the document is currently valid.
invalidation_reason	TEXT	Explanation for why the document is no longer valid.
file_url	VARCHAR	URL or file path to the document file storage.
custom_buffer_time	INT	Default extra days added as a buffer for user action.
created_at	TIMESTAMP	Document creation timestamp.

Fields and Their Purpose

document_reference (VARCHAR NOT NULL):
- Stores the unique identifier for the document, such as a passport number, license number, or visa reference.
- Ensures traceability and association with official records.
- Example Values:
- “AB1234567” for a passport.
- “DL123456789” for a driving license.
- “VisaRef12345” for a visa.
issuing_authority (VARCHAR):
- Records the name of the entity that issued the document, such as a government body, consulate, or private organization.
- Useful for legal and administrative purposes.
- Example Values:
- “Department of State” for a U.S. passport.
- “Ontario Ministry of Transportation” for a Canadian driver’s license.
- “British High Commission” for a UK visa.
invalidation_reason (TEXT):
- Provides a descriptive explanation when the document is marked inactive (i.e., is_active = FALSE).
- Improves traceability and helps users understand why a document is no longer valid.
- Example Values:
- “Lost or stolen.”
- “Superseded by a newer document.”
- “Invalid due to incorrect information.”

Example data

Table documents

INSERT INTO documents (
    id, user_id, document_type_id, document_reference, issuing_authority, file_url, expiry_date, 
    issued_date, is_active, invalidation_reason, notes
)
VALUES
('doc1', 'user1-id', '1', 'AB1234567', 'Department of State', '/storage/passports/user1_passport.pdf',
 '2030-12-31', '2020-01-01', TRUE, NULL, 'Main passport for international travel'),

('doc2', 'user1-id', '2', 'Visa123456', 'Schengen Consulate', '/storage/visas/user1_visa.pdf',
 '2024-05-01', '2023-05-01', TRUE, NULL, 'Schengen visa for Europe'),

('doc3', 'user1-id', '1', 'CD7890123', 'Department of State', '/storage/passports/user1_old_passport.pdf',
 '2025-12-31', '2015-01-01', FALSE, 'Superseded by a newer passport.', 'Previous passport.');

Query Examples

Fetch Documents for a Specific User:

SELECT d.*, dt.name AS document_type
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
WHERE d.user_id = 'user1-id';

Count Documents by Type:

SELECT dt.name AS document_type, COUNT(d.id) AS document_count
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
GROUP BY dt.name;

Retrieve All Active Documents

SELECT d.*, dt.name AS document_type
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
WHERE d.is_active = TRUE;

Retrieve Inactive Documents with Reasons:

SELECT d.*, dt.name AS document_type, d.invalidation_reason
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
WHERE d.is_active = FALSE;

Search by Document Reference:

SELECT d.*, dt.name AS document_type
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
WHERE d.document_reference = 'AB1234567';

Query to Identify Expiring Documents

Fetch documents that are nearing expiry and should trigger a notification. To calculate the exact notification date, sum up the required fields (notification_lead_time, processing_time_estimate, default_user_buffer_time):

SELECT d.id AS document_id,
       d.user_id,
       d.expiry_date,
       dt.name AS document_type,
       COALESCE(d.custom_buffer_time, dt.default_user_buffer_time) AS buffer_time,
       dt.notification_lead_time + dt.processing_time_estimate + 
       COALESCE(d.custom_buffer_time, dt.default_user_buffer_time) AS total_lead_time
FROM documents d
JOIN document_types dt ON d.document_type_id = dt.id
WHERE d.expiry_date IS NOT NULL 
  AND d.expiry_date - INTERVAL '1 day' * (
      dt.notification_lead_time + dt.processing_time_estimate + 
      COALESCE(d.custom_buffer_time, dt.default_user_buffer_time)
  ) <= NOW();

This query ensures that notifications are triggered early enough to account for:

The lead time needed to act before expiry.
Processing time for the document authority.
The user’s buffer period.

This ensures flexibility:

Use the document type’s default buffer if the user hasn’t set a custom one.
Honor the user’s custom buffer time when specified.

Table: notifications

Logs notifications sent to users, such as expiry reminders and travel alerts.

Column	Type	Description
id	UUID	Primary key for each notification record.
user_id	UUID	Foreign key referencing users(id).
document_id	UUID	Foreign key referencing documents (id).
notification_date	TIMESTAMP	When the notification was created.
type	VARCHAR	Type of notification (e.g., visa expiry, stay limit).
message	TEXT	Notification message content.
is_sent	BOOLEAN	Has the notification been sent?
sent_at	TIMESTAMP	When was the notification sent?
is_read	BOOLEAN	Status of notification (read/unread).
created_at	TIMESTAMP	When the notification was generated.

Notification Timing Calculation

When determining when a notification should be raised, combine the following:

notification_lead_time (e.g., 30 days).
processing_time_estimate (e.g., 20 days for a visa).
default_user_buffer_time (e.g., 7 days for buffer).

In this example, the total lead time for notifications would be 57 days before expiry.

Sample Notification Entry

For a passport expiring in 180 days, a notification might look like this:

INSERT INTO notifications (id, user_id, document_id, notification_date, message)
VALUES 
('notif1', 'user1-id', 'passport-doc-id', NOW(), 'Your passport is set to expire in 6 months. Please renew it promptly.');

Table: analytics

Stores aggregated data on user travels, legal compliance, and more for analytics and reporting.

Column	Type	Description
id	UUID	Primary key for each analytics record.
user_id	UUID	Foreign key referencing users(id).
total_trips	INT	Total number of trips taken by the user.
total_days_traveled	INT	Total number of days spent traveling.
average_trip_length	FLOAT	Average length of user’s trips (in days).
visa_compliance_score	FLOAT	Custom metric score for compliance tracking.
updated_at	TIMESTAMP	Last update timestamp for analytics data.

Basic Relationships and Indexing

Foreign Key Relationships

trips.user_id → users.id
trips.country_id → countries.id
visas.country_id → countries.id
documents.user_id → users.id
documents.country_id → countries.id
notifications.user_id → users.id
analytics.user_id → users.id

Indexes

users.email – Unique index for efficient user authentication.
trips.user_id, trips.entry_date – Index to improve performance on trip querying by user.
documents.user_id, documents.expiry_date – Index to optimize document expiry reminders.
notifications.user_id, notifications.is_read – Index for unread notification queries.

Potential Extensions and Modifications

This schema is designed to be extensible, allowing for further features and customization. Here’s a quick look at some possible enhancements:

Add User Preferences Table: For storing user-specific preferences (e.g., notification frequency, preferred visa types).
User Actions Log: Track actions or events for each user, useful for analytics or to improve the user experience with insights.
Countries & Visas API Integration Table: A separate table to store and sync country and visa data from third-party APIs.

This schema provides a solid foundation for the initial key features while remaining flexible for future development.

Table: trips

Logs user trips with entry and exit dates, location, and trip status.

Column	Type	Description
id	UUID	Primary key for each trip.
user_id	UUID	Foreign key referencing users(id).
country_id	UUID	Foreign key referencing countries(id).
entry_date	DATE	Date of entry into the country.
exit_date	DATE	Date of exit from the country (nullable if ongoing).
status	VARCHAR	Status of the trip (e.g., active, completed).
created_at	TIMESTAMP	Trip log creation timestamp.

Table: visa_types

This table will serve as a lookup for all possible visa types.

CREATE TABLE visa_types (
    id UUID PRIMARY KEY,            -- Unique identifier for each visa type
    name VARCHAR NOT NULL UNIQUE,   -- E.g., "Tourist Visa," "Business Visa," "Work Permit"
    description TEXT,               -- Optional: description of what the visa type entails
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table: visas

Defines different visa types, their durations, and any other requirements for each country.

Column	Type	Description
id	UUID	Primary key, unique identifier for each visa type.
country_id	UUID	Foreign key referencing countries(id).
visa_block_id	UUID	Foreign key referencing the block to which the visa applies.
visa_type	VARCHAR	Name/type of the visa (e.g., tourist, business, nomad).
duration_days	INT	Maximum duration allowed under this visa.
renewable	BOOLEAN	Indicates if the visa is renewable.
requirements	TEXT	Specific requirements for obtaining the visa.
updated_at	TIMESTAMP	Last update timestamp for the visa data.

CREATE TABLE visas (
    id UUID PRIMARY KEY,
    country_id UUID REFERENCES countries(id),       -- Country the visa applies to
    visa_block_id UUID REFERENCES visa_blocks(id), -- Optional: Block the visa applies to (e.g., Schengen)
    visa_type_id UUID REFERENCES visa_types(id),   -- Normalized visa type
    visa_rules_summary TEXT,                       -- Description of visa requirements/rules
    max_stay_limit INT,                            -- Maximum days allowed to stay
    in_force_from DATE NOT NULL,                   -- Date the visa rules came into force
    in_force_until DATE,                           -- Optional: End date if the rule is no longer valid
    succeeded_by UUID REFERENCES visas(id),        -- Links to the next version of the visa rules
    preceeded_by UUID REFERENCES visas(id),        -- Links to the previous version of the visa rules
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Handling Superseded Visa Rules

The addition of in_force_from, in_force_until, and succeeded_by allows for clear versioning:

•   `in_force_from` and `in_force_until`: Define the time range during which the visa rules are valid.
•   `succeeded_by` and `preceeded_by`: Form a chain of rule changes, allowing the application to trace historical changes.

This structure ensures that users see the most recent, in-force rules but can query historical data if needed.

Benefits of Normalizing visa_type:

Data Integrity: Ensures consistent naming and eliminates the risk of typos or slight variations (e.g., “Tourist Visa” vs. “tourist visa”).
Query Efficiency: Queries filtering by visa type can be performed on indexed IDs, which are faster to search than strings.
Reduced Redundancy: Instead of storing the same string repeatedly in multiple rows, only the foreign key (UUID) is stored.
Flexibility: Additional metadata about each visa type (e.g., descriptions, eligibility) can be added to the visa_types table without altering the visas schema.

Example Data

visa_types Table

INSERT INTO visa_types (id, name, description) VALUES
('1', 'Tourist Visa', 'Allows entry for leisure travel'),
('2', 'Business Visa', 'Allows entry for business-related activities'),
('3', 'Work Permit', 'Grants permission to work in the destination country'),
('4', 'Student Visa', 'Allows enrollment in educational institutions');

visas Table

INSERT INTO visas (id, country_id, visa_block_id, visa_type_id, visa_rules_summary, max_stay_limit, in_force_from)
VALUES
('v1', 'france-id', 'schengen-id', '1', '90 days in Schengen area', 90, '2022-01-01'),
('v2', 'japan-id', NULL, '1', '90 days visa-free travel for selected countries', 90, '2022-01-01'),
('v3', 'usa-id', NULL, '2', 'Business visits up to 180 days', 180, '2021-06-01');

Query Examples

Filter by Visa Type:

To retrieve all visas for “Tourist Visa”:

SELECT v.*
FROM visas v
JOIN visa_types vt ON v.visa_type_id = vt.id
WHERE vt.name = 'Tourist Visa';

Group by Visa Type:

To count visas by type:

SELECT vt.name, COUNT(v.id) AS visa_count
FROM visas v
JOIN visa_types vt ON v.visa_type_id = vt.id
GROUP BY vt.name;

Table: visa_blocks

To handle visa blocks in the database schema, you can add a visa_block entity to capture these shared policies:

CREATE TABLE visa_blocks (
    id UUID PRIMARY KEY,
    name VARCHAR NOT NULL,
    max_stay INT,                     -- Maximum stay in days, e.g., 90
    stay_period INT,                   -- Period within which max_stay applies, e.g., 180 for "90 days within 180 days"
    shared_visa_policy BOOLEAN,        -- Indicates if a single visa applies to the whole block
    description TEXT                   -- Description of any special rules or policies
);

CREATE TABLE country_visa_blocks (
    country_id UUID REFERENCES countries(id),
    visa_block_id UUID REFERENCES visa_blocks(id),
    PRIMARY KEY (country_id, visa_block_id)
);

With this setup:

Each visa block (e.g., “Schengen Area,” “CA-4”) has rules applied to all associated countries.
The country_visa_blocks table allows linking countries to visa blocks, so when a user enters a country in a block, the app can check block-level rules.

This approach makes it easy to track which countries participate in each block and enforce block-wide limits on stays and visas.

Versioned Country and Visa Blocks Integration

To integrate countries and visa blocks, the schema can rely on the country_visa_blocks table while keeping a historical record of changes to block membership:

CREATE TABLE country_visa_blocks (
    id UUID PRIMARY KEY,
    country_id UUID REFERENCES countries(id),
    visa_block_id UUID REFERENCES visa_blocks(id),
    membership_in_force_from DATE NOT NULL,        -- When the country joined the block
    membership_in_force_until DATE,               -- When the country left the block (if applicable)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This setup ensures you can:

Track when a country joined or left a visa block.
Handle scenarios where countries temporarily suspend participation (e.g., Brexit-like scenarios).
Keep historical records for reports and audits.

Example Data Scenarios

Schengen Area Example

A visa_block entry for Schengen might look like this:

INSERT INTO visa_blocks (id, name, max_stay, stay_period, shared_visa_policy, description) 
VALUES ('1', 'Schengen Area', 90, 180, TRUE, 'Single visa for travel within the Schengen Zone');

Visa Rules for Schengen

Visa rules for Schengen could evolve like this:

INSERT INTO visas (id, country_id, visa_block_id, visa_type, visa_rules_summary, max_stay_limit, in_force_from, succeeded_by) 
VALUES 
('v1', NULL, '1', 'Tourist Visa', 'Allows 90 days of travel within 180-day period', 90, '2022-01-01', NULL),
('v2', NULL, '1', 'Tourist Visa', 'Updated rules for 90 days travel within 180-day period (COVID-19 exceptions)', 90, '2023-01-01', 'v3');

UPDATE visas SET succeeded_by = 'v2' WHERE id = 'v1';

Country Membership in Schengen

INSERT INTO country_visa_blocks (id, country_id, visa_block_id, membership_in_force_from) 
VALUES ('c1', 'france-id', '1', '1995-03-26');

Table: user_actions_log

The user_actions_log table tracks user actions within the app for analytics, debugging, or engagement metrics.

Column	Type	Description
id	UUID	Primary key, unique identifier for each log entry.
user_id	UUID	Foreign key referencing users(id).
action_type	VARCHAR	Type of action (e.g., login, trip_added, document_uploaded).
action_description	TEXT	Detailed description of the action, if needed.
action_metadata	JSONB	Metadata related to the action (e.g., IP address, device info).
created_at	TIMESTAMP	Timestamp when the action was performed.

Sample Entries

login, User logged in successfully, { “ip”: “192.168.1.1”, “device”: “iPhone 12” }
trip_added, User added a new trip to France, { “country_id”: “france-uuid”, “entry_date”: “2024-06-01” }

This table uses a JSONB field to store additional metadata related to each action, making it easy to extend the schema as new action types are tracked.

Table: country_api_data

This table stores country information retrieved from third-party APIs, keeping track of important data such as visa policies, which can be regularly updated from an external source.

Column	Type	Description
id	UUID	Primary key, unique identifier for each country API data record.
country_id	UUID	Foreign key referencing countries(id).
api_source	VARCHAR	Name or identifier of the API source (e.g., VisaDB).
data	JSONB	JSON structure containing the API data for the country.
last_updated	TIMESTAMP	Timestamp of the last API data update.
created_at	TIMESTAMP	Timestamp when the record was created.

Example of data Field

{
    "visa_requirements": {
        "tourist": { "max_stay": 90, "renewable": true },
        "business": { "max_stay": 30, "renewable": false }
    },
    "health_alerts": ["COVID-19 vaccination required", "Malaria endemic area"]
}

This table is designed to support structured storage of API data, allowing easy access to country-specific visa rules and other important information without requiring constant API calls.

Table: visa_api_data

This table specifically focuses on visa-related information for different countries, stored per visa type. The data can be pulled from APIs and is stored here for easy access and caching.

Column	Type	Description
id	UUID	Primary key, unique identifier for each visa API data record.
visa_id	UUID	Foreign key referencing visas(id).
api_source	VARCHAR	Name or identifier of the API source (e.g., VisaHQ).
data	JSONB	JSON structure containing the API data for the visa.
last_updated	TIMESTAMP	Timestamp of the last API data update.
created_at	TIMESTAMP	Timestamp when the record was created.

Example of data Field

{
    "requirements": ["Proof of funds", "Return ticket"],
    "processing_time": "3-5 business days",
    "application_fee": "USD 50",
    "additional_notes": "No visa required for stays under 30 days for US citizens."
}

Storing visa-specific data as JSONB allows flexibility in handling structured information without requiring frequent schema changes. This table would allow caching of detailed, per-visa information from external sources.

Relationships and Indexing Recommendations

Foreign Key Relationships:
user_preferences.user_id → users.id
user_actions_log.user_id → users.id
country_api_data.country_id → countries.id
visa_api_data.visa_id → visas.id
Indexes:
user_preferences.user_id, user_preferences.preference_type – For efficient querying of a user’s preferences.
user_actions_log.user_id, user_actions_log.action_type – For quickly filtering actions by type for a specific user.
country_api_data.country_id, country_api_data.api_source – To retrieve the latest country data from a particular API.
visa_api_data.visa_id, visa_api_data.api_source – To retrieve visa information quickly based on the source API.

Summary of Use Cases

user_preferences table allows for scalable, flexible storage of user-specific preferences for notifications, dashboards, and other settings.
user_actions_log enables logging and tracking of all user activities, which is useful for insights, analytics, and auditing.
country_api_data and visa_api_data tables serve as caches for external API data, ensuring that country and visa information is readily available without needing to repeatedly query third-party APIs.

Together, these tables extend the database’s core functionality, ensuring the app can handle user customizations, activity tracking, and robust country/visa management via third-party APIs.

Back

Klepsidra Feature Planner

notebooks/feature_planner.livemd

Klepsidra Feature Planner

Contents

User authentication

Detailed Schema Design

Related Tables

Additional Tables for 2FA and API Management

Updated Tables for Enhanced API Integration

Updated Schema for Two-Factor Authentication

Relationships

Example Query Use Cases

Quotes and wisdom recording

Core Functionality

1. Quote Entry and Management

2. Organization and Categorization

3. Search and Discovery

4. Verification and Sources

5. Engagement Features

6. Sharing and Export

7. Analytics and Insights

Technical Requirements

1. Data Security

2. Integration Capabilities

3. Platform Support

User Experience Features

1. Customization

2. Accessibility

3. Learning Tools

Development Priorities

Phase 1 (MVP)

Phase 2

Phase 3

This schema design provides:

Database Relationships and Field Specifications

Core Entity Relationships

1. Quotes → Authors (Many-to-One)

2. Quotes → Sources (Many-to-One)

3. Quotes ↔ Categories (Many-to-Many)

4. Quotes ↔ Tags (Many-to-Many)

5. Users ↔ Quotes (Various)

Detailed Field Specifications

Quotes Table

Authors Table

Collections System

Common Query Patterns

1. Fetching a Quote with Related Data

2. User’s Favorite Quotes

3. Finding Similar Quotes

Data Integrity Rules

Complete Database Relationships Analysis

Direct Relationships

User-Related Relationships

Quote-Related Relationships

Collection-Related Relationships

Verification-Related Relationships

Category-Related Relationships

Indexing Strategy

1. Primary Key Indexes

2. Foreign Key Indexes

3. Full-Text Search Indexes

4. Composite Indexes for Common Queries

5. Performance Optimization Indexes

6. Specialized Indexes

Index Maintenance Considerations

Trip tracker

Key Features

Implementation Steps

Additional Features

Technical Enhancements with Phoenix LiveView

Additional and Advanced Feature Ideas

Suggested User Engagement Features

Premium Features for Monetization

Trip Tracker schema

Data structure

Field Explanation

Example Data

Example Notification Workflow

Enhancing User Flexibility

Fields and Their Purpose