Klepsidra Feature Planner

Contents

1. User authentication
2. Quote and wisdom recording
3. Trip tracker
   1. Trip tracker schema
4. Notes
5. Unified search
6. 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.

| profile_picture | TEXT | | | Path/URL to profile picture. | | two_factor_enabled | BOOLEAN | DEFAULT FALSE | Indicates if 2FA is enabled for the user. | | two_factor_secret | TEXT | | Secret key for generating 2FA tokens (e.g., TOTP). | | api_access_enabled | BOOLEAN | DEFAULT FALSE | Indicates if API access is allowed for this user. | | last_login_at | TIMESTAMP | | Timestamp of the user’s last login. | | last_login_ip | VARCHAR(45) | | Last login IP address (IPv4/IPv6). | | created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Timestamp when the account was created. | | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Timestamp for last update. |

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).

| description | TEXT | | | Description of the role’s purpose. |

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).

| description | TEXT | | | Description of the permission. |

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.

1. 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.

| ip_address | VARCHAR(45) | | | IP address associated with the action.

Summary

This schema achieves the following:

1. Normalization: Key entities like roles, permissions, and preferences are separated for reuse and scalability.
2. Flexibility: Easily extendable with additional tables (e.g., API tokens, notification settings).
3. 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

1. Users to API Tokens: One user can have multiple API tokens (users.id → api_tokens.user_id).
2. Users to 2FA Recovery Codes: One user can have multiple recovery codes (users.id → two_factor_recovery_codes.user_id).
3. 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

1. 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';

1. 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();

1. 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

1. Security: 2FA fields and recovery codes ensure robust protection.
2. Scalability: External API tokens with rate-limiting ensure controlled integration with third-party apps.
3. 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)

1. Basic quote entry and management
2. Simple tagging system
3. Search functionality
4. Export capability
5. User accounts

Phase 2

1. Advanced categorization
2. Verification system
3. Mobile apps
4. Sharing features
5. Analytics dashboard

Phase 3

1. API development
2. Integration ecosystem
3. Advanced learning tools
4. Community features
5. 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:

1. Core Quote Management:

   • Quotes with translations and versioning
   • Authors and sources tracking
   • Categories and tags
   • Collections system

2. User Management:

   • User accounts and roles
   • Personal collections
   • Annotations and favorites

3. Organization:

   • Hierarchical categories
   • Flexible tagging
   • Custom collections

4. Quality Control:

   • Verification system
   • Activity logging
   • Moderation capabilities

5. 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

1. Cascading Deletes:

   • Collection quotes are deleted when collection is deleted
   • Tags are preserved when quotes are deleted
   • Annotations are preserved when quotes are deleted

2. Constraints:

   • Authors must have name
   • Quotes must have text and author
   • Collections must have owner and name
   • Birth date must be before death date

3. 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

1. 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

2. Users → Collections (One-to-Many)

   • One user can own multiple collections
   • collections.user_id → users.user_id

3. 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

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

   • Each quote has one author
   • quotes.author_id → authors.author_id
   • Required relationship (NOT NULL)

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

   • Each quote can have one source
   • quotes.source_id → sources.source_id
   • Optional relationship (NULL allowed)

3. 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

4. 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

5. Quotes → Translations (One-to-Many)

   • One quote can have multiple translations
   • quote_translations.quote_id → quotes.quote_id

Collection-Related Relationships

1. 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

1. 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

1. 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

1. 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);

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;

3. 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

1. 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.

2. 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.

1. 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.

1. 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.

1. 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.

2. 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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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

1. 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.
2. 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.
3. 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).
4. 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?”
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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

1. 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.
2. 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.
3. 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).
4. 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).
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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:

1. Weekly or Monthly Summary Emails: Send automated summaries of recent travels, upcoming visa expirations, and day limits to keep users engaged and informed.
2. 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.
3. 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.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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:

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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.

11. 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.

12. 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:

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.

1. Database Schema Overview

The database schema includes the following main tables:

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

Table document_issuers

CREATE TABLE document_issuers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE, -- Name of the issuing authority (e.g., "US Department of State")
    description TEXT, -- Description of the authority
    country_id CHAR(3) REFERENCES locations_countries(iso_3_country_code), -- Country where the authority operates, references country's iso_3_country_code

    contact_information JSONB, -- JSON structure for storing contact details (e.g., email, phone, address)
    website_url TEXT, -- Website for the issuing authority
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Index Suggestions

CREATE INDEX idx_document_issuers_country_id ON document_issuers (country_id);

The contact_information field in the document_issuers table is defined as JSONB because it allows flexibility and efficiency in storing complex, semi-structured data that varies across issuers.

Reasons for Using JSONB

1. Complex and Nested Data

   • Contact information can include multiple attributes, such as:
     • Email addresses
     • Phone numbers (possibly multiple, e.g., direct line and helpline)
     • Physical addresses
     • Office hours
     • Additional details like fax numbers or secondary addresses
     • Representing this data in a nested JSON structure avoids the need for additional tables or complex column-based designs.

2. Variability

   • Different document issuers may provide different types of contact information:
     • Some may offer only an email and phone.
     • Others might include multiple office addresses or hours.
     • A JSONB field handles these variations elegantly, as fields within the JSON document can be optional.

3. Querying and Indexing

   • JSONB in PostgreSQL supports:
     • Efficient indexing via GIN (Generalized Inverted Index).
     • Flexible querying with JSON path expressions or PostgreSQL-specific operators (->>, #>>).
     • Example: Quickly retrieve issuing authorities with a specific office address or working hours.

4. Future-Proofing

   • If the format or attributes of contact information evolve (e.g., adding new communication channels), the schema doesn’t require modification.
   • Only the JSON structure changes, simplifying maintenance.

5. Reduced Table Complexity

   • Without JSONB, you might need multiple related tables, such as:
     • emails
     • phone_numbers
     • addresses
     • office_hours
     • Consolidating these details into a single field reduces the table count and joins, making the schema cleaner.

Examples of JSONB usage

Example 1: A simple document issuer with minimal details

{
    "email": "contact@authority.gov",
    "phone": "+1-202-555-0198",
    "address": "123 Government Street, Washington, DC",
    "working_hours": "Mon-Fri, 9 AM - 5 PM"
}

Example 2: A complex document issuer with multiple addresses and lines:

{
  "emails": ["info@authority.com", "support@authority.com"],
  "phones": ["+1-123-456-7890", "+1-098-765-4321"],
  "addresses": [
    "HQ: 456 Main St, Capital City",
    "Branch: 789 Regional Ave, Suburbia"
  ],
  "working_hours": {
    "HQ": "Mon-Fri, 8 AM - 6 PM",
    "Branch": "Mon-Fri, 9 AM - 5 PM"
  },
  "fax": "+1-555-555-5555"
}

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_templates

CREATE TABLE document_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for each template
    name TEXT NOT NULL UNIQUE, -- Name of the template (e.g., "Passport Template")
    description TEXT, -- Description or additional information about the template
    template_file_path TEXT, -- Path or reference to the template file
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp of creation
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Timestamp of last update
);

Schema Details

• id: A unique identifier for each template, ensuring distinct entries.
• name: Unique name for identifying the document template (e.g., “Schengen Visa Application Form”).
• description: Optional, provides a brief description of the template’s purpose or instructions.
• template_file_path: A path or URL pointing to the stored file or document template. This might reference a storage service (e.g., AWS S3, Google Cloud Storage) or a local directory.
• created_at and updated_at: Timestamps to track when the template was created and last updated.

Indexes and Constraints

To optimize queries and ensure data integrity:

• Unique Index on name:

CREATE UNIQUE INDEX idx_document_templates_name ON document_templates (name);

• Full-Text Search on description: (Optional)

CREATE INDEX idx_document_templates_description_gin 
ON document_templates USING gin (to_tsvector('english', description));

Relationship with Other Tables

• Referenced by document_types: The document_types.template_id foreign key references document_templates.id. This allows associating a document type with its corresponding template.
• Example Use Case: A visa application form (template) linked to multiple visa types in different jurisdictions.

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
    is_country_specific BOOLEAN DEFAULT TRUE, -- Indicates if this document type varies by country
    requires_renewal BOOLEAN DEFAULT TRUE, -- Indicates if renewal is required
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Field Explanation

1. 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.

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

• is_country_specific: For document types like visas that vary by country, this field helps determine if country-specific rules apply.
• requires_renewal: Simplifies checking whether a document type has a renewal requirement.

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

1. 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.
2. 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).
country_id	UUID	Foreign key referencing countries(id).
document_issuer_id	UUID	Foreign key referencing document issuing authority.
unique_reference_number	VARCHAR	Unique reference number for the document, given by the document issuing authority.
name	TEXT	User-defined title or name for the document.
description	TEXT	Additional details or context.
issued_at	DATE	Date when the document was issued.
expires_at	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_days	INT	User-defined override for notification lead time (in days), a buffer time for user action for this particular document.

Fields and Their Purpose

1. unique_reference_number (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.
2. 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.”

Indexes

1. Unique Index on unique_reference_number
   • Field: unique_reference_number
   • Purpose: Ensures that reference numbers (e.g., passport numbers) are unique across documents and facilitates fast lookups by this field.
   • Index Type: Unique index.

CREATE UNIQUE INDEX idx_documents_unique_reference_number ON documents(unique_reference_number);

1. Index on user_id
   • Field: user_id
   • Purpose: Supports queries that filter or join by user, such as retrieving all documents for a specific user.
   • Index Type: Standard B-Tree.

CREATE INDEX idx_documents_user_id ON documents(user_id);

1. Composite Index on user_id and document_type_id
   • Fields: (user_id, document_type_id)
   • Purpose: Optimizes queries where users search for documents by type. For instance:

SELECT * FROM documents WHERE user_id = ? AND document_type_id = ?;

CREATE INDEX idx_documents_user_type ON documents(user_id, document_type_id);

1. Index on expires_at
   • Field: expires_at
   • Purpose: Facilitates queries for finding documents that are about to expire. Critical for generating expiration reminders and notifications.
   • Index Type: Standard B-Tree.

CREATE INDEX idx_documents_expires_at ON documents(expires_at);

1. Index on country_id
   • Field: country_id
   • Purpose: Useful for queries that filter documents by the issuing country. For example:

SELECT * FROM documents WHERE country_id = 'USA';

CREATE INDEX idx_documents_country_id ON documents(country_id);

1. Composite Index on document_type_id and country_id
   • Fields: (document_type_id, country_id)
   • Purpose: Optimizes queries for filtering by document type and country, such as when finding templates or issuing rules for a particular type in a specific country.
   • Example Query:

SELECT * FROM documents WHERE is_active = true;

CREATE INDEX idx_documents_is_active ON documents(is_active);

1. Full-Text Index on name and description (Optional)
   • Fields: (name, description)
   • Purpose: Supports full-text search when users search for documents by name or description.
   • Database-Specific:
   • MySQL/MariaDB: Use a FULLTEXT index.
   • PostgreSQL: Use a GIN index with the to_tsvector function.

PostgreSQL Example:

CREATE INDEX idx_documents_fulltext ON documents USING gin (to_tsvector('english', name || ' ' || description));

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:

1. The lead time needed to act before expiry.
2. Processing time for the document authority.
3. 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:

1. notification_lead_time (e.g., 30 days).
2. processing_time_estimate (e.g., 20 days for a visa).
3. 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.

1. 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.

1. 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 travel_visa_types

Capture the high-level categories of visas (e.g., tourist, work, business, student, nomad).

CREATE TABLE travel_visa_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- Type of visa (e.g. Tourist, Business, Student, Nomad)
    description TEXT, -- Optional: description of what the visa type entails
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Usage Scenarios

• A Schengen Visa will remain a traditional visa entry with multiple_entry set to true or false.
• An ETIAS entry will have is_electronic = true and requires_preapproval = true.

Example Data

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');

Likely Query Patterns

1. Fetching visa types by name: • When searching for a specific visa type (e.g., for admin or UX purposes), users might query by the name field.
2. General management or list views: • Queries may order or filter by the created_at and updated_at timestamps to show recently added or updated visa types.
3. Foreign key references: • The id column will frequently be referenced by related tables like travel_country_visa_rules.

Recommended Indexes

1. Primary Index on id: • This is the default primary key index, ensuring efficient lookups by id, particularly for foreign key relationships:

PRIMARY KEY (id)

1. Unique Index on name: • To ensure no two visa types have the same name, which is critical for data integrity and allows efficient lookups by name:

CREATE UNIQUE INDEX unique_visa_type_name ON travel_visa_types (name);

Ensures uniqueness and supports quick searches, as name is likely queried frequently.

1. Index for ordering by timestamps: • For displaying visa types in chronological order (e.g., sorted by created_at or updated_at), add a composite index:

CREATE INDEX visa_types_created_updated_idx ON travel_visa_types (created_at, updated_at);

Useful for admin views or audit trails, where sorting or filtering by timestamps is common.

1. Full-text Search Index (Optional): • If description contains substantial textual information and you anticipate searches within it, create a full-text index (PostgreSQL example):

CREATE INDEX visa_types_description_fts_idx ON travel_visa_types USING gin(to_tsvector('english', description));

Full-text Search on description: Adds flexibility for advanced search capabilities, useful in admin interfaces or automated systems referencing visa descriptions.

Table: travel_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.
user_id	UUID	Foreign key, referencing users(id)
visa_type_id	UUID	Foreign key to visa type
country_id	UUID	Foreign key referencing countries(id).
issuing_authority_id	UUID	Foreign key referencing visa issuing authority
unique_reference_number	TEXT	Unique visa number
visa_block_id	UUID	Foreign key referencing the block to which the visa applies.
valid_from	date	Date the visa is valid from
valid_to	date	Date the visa is valid until
duration_days	INT	Maximum duration allowed by this visa.
description	TEXT	Additional details about the visa
is_active	BOOLEAN	Is this visa currently active
invalidation_reason	TEXT	If the visa is no longer active, why was it invalidated or revoked
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:

1. Data Integrity: Ensures consistent naming and eliminates the risk of typos or slight variations (e.g., “Tourist Visa” vs. “tourist visa”).
2. Query Efficiency: Queries filtering by visa type can be performed on indexed IDs, which are faster to search than strings.
3. Reduced Redundancy: Instead of storing the same string repeatedly in multiple rows, only the foreign key (UUID) is stored.
4. 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

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;

Improved Travel and Residency Tools

Proof of Stay and Entry Logs

• Purpose: Enable users to log entry/exit dates or upload supporting documents (e.g., flight tickets, boarding passes).
• Benefit: Helps users reconstruct travel histories for visa applications or tax compliance.

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_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:

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

Example Data Scenarios

1. 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');

1. 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';

1. 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');

1. 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.

1. 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.

1. 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.

Notes

Table knowledge_management_notes

Stores the primary notes.

CREATE TABLE knowledge_management_notes (
    note_id SERIAL PRIMARY KEY, -- Unique ID for each note
    title VARCHAR(255) NOT NULL UNIQUE, -- The title of the note; must be unique and meaningful
    content TEXT NOT NULL, -- Main content/body of the note
    content_format ENUM('markdown', 'org-mode', 'plaintext', 'html') DEFAULT 'plaintext', -- Input format
    rendered_content TEXT,            -- Rendered output (e.g., HTML, PDF content, etc.)
    rendered_content_format ENUM('html', 'pdf', 'plaintext', 'other') DEFAULT 'html', -- Format of rendered_content
    summary TEXT DEFAULT NULL, -- A brief summary or abstract of the note
    status ENUM('draft', 'fleeting', 'evergreen', 'archived') DEFAULT 'fleeting', -- Lifecycle state
    review_date DATE DEFAULT NULL, -- Next scheduled review date
    pinned BOOLEAN DEFAULT FALSE, -- Indicates whether the note is pinned
    attachments JSON DEFAULT NULL, -- A JSON field to store metadata about attached files, embedded media, or links. This can include filenames, URLs, or base64-encoded data
    source_id INT DEFAULT NULL, -- Links to a source in the citations table (optional)
    priority INT DEFAULT NULL, -- Importance/urgency of the note
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Creation timestamp
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Last modification timestamp
);

Indexes

Index on status

• Purpose: Many queries will likely filter notes by their status (e.g., active, draft, or archived), so an index on this field can improve performance when filtering by these states.

CREATE INDEX idx_status ON knowledge_management_notes (status);

Index on pinned

• Purpose: Similarly, the pinned field is likely to be used frequently in queries (e.g., for filtering or prioritizing pinned notes). Indexing this field makes sense for faster retrieval.

CREATE INDEX idx_pinned ON knowledge_management_notes (pinned);

Composite Index on status, pinned, and priority

• Purpose: If you frequently query notes based on multiple criteria (e.g., status, pinned status, and priority), a composite index on these fields will speed up such queries by avoiding the need for a full scan of the table.

CREATE INDEX idx_status_pinned_priority ON knowledge_management_notes (status, pinned, priority);

Index on created_at

• Purpose: Sorting or filtering notes by their creation date is a common operation. An index on created_at helps optimize queries that involve date-based filters or sorting.

CREATE INDEX idx_created_at ON knowledge_management_notes (created_at);

Index on review_date

• Purpose: Since your system supports regular reviews of notes, querying by review_date is important. Indexing this field will speed up searches for notes that are due for review.

CREATE INDEX idx_review_date ON knowledge_management_notes (review_date);

Index on format

• Purpose: If you frequently query notes based on their input format (e.g., searching for all Markdown-formatted notes), indexing this field can improve performance.

CREATE INDEX idx_format ON knowledge_management_notes (format);

Full-Text Index on content

• Purpose: The content field stores the primary text for the note. Since you may need to search within the note’s content (e.g., searching for a specific word or phrase), a full-text index would be beneficial for those types of queries.

CREATE INDEX idx_content_fulltext ON knowledge_management_notes USING GIN (to_tsvector('english', content));

Index on rendered_format

• Purpose: If you query the rendered format of the note (e.g., to distinguish between HTML, PDF, or other formats), an index on rendered_format can speed up these queries.

CREATE INDEX idx_content_fulltext ON knowledge_management_notes USING GIN (to_tsvector('english', content));

Other indexes:

-- Indexes
CREATE INDEX idx_notes_title ON Notes (title);
CREATE INDEX idx_notes_status_updated ON Notes (status, updated_at);

Design rationale

text_format field

• Purpose: To let the system know how to interpret the content field. • Type: ENUM to restrict allowed formats to avoid errors or unsupported types. • Possible Values: • ‘markdown’: Markdown syntax. • ‘org-mode’: Org-mode syntax (e.g., for advanced outlining). • ‘plaintext’: Raw text without formatting. • ‘html’: Already processed HTML. • Default: ‘plaintext’.

rendered_content field

• Purpose: Specifies the format of the content stored in the rendered_content field. • Type: ENUM to restrict allowed values and ensure consistency. • Possible Values:

•   'html': For rendered HTML output (default and most common case).
•   'pdf': For rendered PDF content (if supported by the system).
  • 'plaintext': For plain text renderings.
  • 'other': To handle any future or custom formats.

rendered_format

•   Purpose: Specifies the format of the content stored in the rendered_content field.
•   Type: ENUM to restrict allowed values and ensure consistency.
•   Possible Values:
•   'html': For rendered HTML output (default and most common case).
•   'pdf': For rendered PDF content (if supported by the system).
•   'plaintext': For plain text renderings.
•   'other': To handle any future or custom formats.

status field

Purpose:

The status field is central to managing the lifecycle of a note, aligning with concepts from Zettelkasten, Evergreen Notes, and general note refinement workflows. Notes often progress through phases of utility, from fleeting ideas to long-term reusable knowledge, requiring a clear way to indicate their stage.

Reasoning:

• Fleeting Notes: Temporary thoughts, unpolished ideas, or quick captures that require further review or elaboration. • Inspired by Zettelkasten’s transient notes. • Evergreen Notes: Finalized, polished notes that are reusable and form part of a permanent knowledge base. • Matches the Evergreen Notes philosophy of cultivating lasting knowledge. • Archived Notes: Notes no longer active or relevant but retained for reference, historical value, or audit trails. • Addresses scenarios where notes don’t need regular engagement but still hold potential value.

Design Choice:

• Using an ENUM ensures that the status is limited to predefined, meaningful states. • The statuses provide clear, structured workflows without overwhelming users with options.

pinned field

Purpose:

The pinned field addresses the need for quick access to notes that are currently important, frequently referenced, or actively in use.

Reasoning:

• Users often work on ongoing projects, reference specific notes repeatedly, or need a “favorites”-style feature for easy retrieval. • Borrowed from the behavior seen in apps like Apple Notes and Notion, where pinning critical items enhances productivity. • Pinning is an intuitive and lightweight mechanism to signal note importance without requiring complex tagging or prioritization systems.

Design Choice:

• A boolean field (true/false) keeps it simple and efficient. It’s easy for users to toggle and straightforward to implement in UI and queries (e.g., “show pinned notes”). • Unlike priority, which is about ranking importance, pinned focuses on accessibility and user intent.

priority field

Purpose:

The priority field provides a granular way to rank notes based on their relative importance, urgency, or the user’s intent to engage with them soon.

Reasoning:

• Some users may need to manage notes based on importance or time sensitivity, such as: • Notes requiring immediate action or review. • Notes deemed central to a research topic or project. • Notes for upcoming deadlines. • Inspired by task management systems like Todoist and Asana, where priority fields help users organize content dynamically. • Complements the review_date field for scheduling reviews but offers a non-temporal perspective on importance.

Design Choice:

• A numeric field allows for a wide range of priorities (e.g., 1 for the highest priority, 5 for lower priority). Users can implement their own frameworks for interpreting these levels. • By making it optional, it doesn’t overwhelm users who don’t need such specificity, but power users can leverage it for more refined note organization.

Interplay between these fields

These three fields work together to provide a holistic approach to note management: • status categorizes notes by lifecycle stage, helping users focus on refining content where needed. • pinned highlights active or high-use notes for easy access. • priority introduces a flexible way to rank and organize notes by subjective importance.

Together, they balance simplicity for casual users and powerful organizational tools for advanced workflows, ensuring broad usability and scalability.

Table knowledge_management_annotations

Stores annotations and quotes linked to sources.

CREATE TABLE knowledge_management_annotations (
    annotation_id UUID PRIMARY KEY,
    source_id UUID NOT NULL,          
    text TEXT NOT NULL,               
    note_id UUID,                     
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (source_id) REFERENCES knowledge_management_sources (source_id),
    FOREIGN KEY (note_id) REFERENCES knowledge_management_notes (note_id)
);

Table knowledge_management_sources

Tracks source materials for annotations.

CREATE TABLE knowledge_management_sources (
    source_id UUID PRIMARY KEY,
    title TEXT,                       
    author TEXT,                      
    type ENUM('book', 'article', 'webpage', 'video', 'other') DEFAULT 'other',
    publication_date DATE,            
    source_url TEXT                   
);

Notes and annotations tables

Links tags to notes and annotations.

CREATE TABLE knowledge_management_note_tags (
    note_id UUID NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY (note_id) REFERENCES knowledge_management_notes (note_id),
    FOREIGN KEY (tag_id) REFERENCES knowledge_management_tags (tag_id)
);

CREATE TABLE knowledge_management_annotation_tags (
    annotation_id UUID NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY (annotation_id) REFERENCES knowledge_management_annotations (annotation_id),
    FOREIGN KEY (tag_id) REFERENCES knowledge_management_tags (tag_id)
);

Table knowledge_management_relationship_types

CREATE TABLE knowledge_management_relationship_types(
    relationship_id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,  -- Relationship name
    reverse_name TEXT,          -- Reverse relationship name
    description TEXT,           -- Explanation of the relationship type
    default BOOLEAN,            -- Flag the default, most generic, relationship type to use
    is_predefined BOOLEAN DEFAULT 1 -- Indicates if the type is part of the tight list
);

Table knowledge_management_note_relations

Handles interconnections between notes.

CREATE TABLE knowledge_management_note_relations (
    link_id SERIAL PRIMARY KEY,
    source_note_id UUID NOT NULL,
    target_note_id UUID NOT NULL,
    relationship_type_id INTEGER NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (source_note_id) REFERENCES knowledge_management_notes (note_id),
    FOREIGN KEY (target_note_id) REFERENCES knowledge_management_notes (note_id)
    FOREIGN KEY (relationship_type_id) REFERENCES knowledge_management_relationship_types (relationship_id)

);

Table knowledge_management_reviews

Tracks review schedules for notes.

CREATE TABLE knowledge_management_reviews (
    review_id SERIAL PRIMARY KEY,
    note_id UUID NOT NULL,
    scheduled_date TIMESTAMP NOT NULL,
    completed_date TIMESTAMP,
    FOREIGN KEY (note_id) REFERENCES knowledge_management_notes (note_id)
);

Unified search

Unified search encapsulates the idea that all, or almost all, entities in a system be indexd for full-text search, enabling excellent user experience through searching for anything from one central location.

An implementation direction for this unified search is a hybrid approach with first-class fields for faceted search and a generic content field for broader full-text search. In determining the discrete fields needed for the actual faceted search, a focus on attributes commonly shared across many disparate entities was taken. These fields should serve as filters or facets, enabling advanced and precise searches.

1. Entity Metadata

   • entity_id: Unique identifier for each entity (e.g., user ID, post ID, product ID).
   • entity_type: Type of the entity (e.g., “user”, “post”, “product”).

2. Temporal Attributes

   • created_at: Timestamp indicating when the entity was created.
   • updated_at: Timestamp for the last update to the entity.

3. Categorical Attributes

   • category: High-level category or type within an entity type (e.g., “blog”, “news”, “profile”).
   • status: Current state of the entity (e.g., “active”, “archived”, “draft”).

4. Ownership/Associations

   • owner_id: ID of the user or entity that owns this item (e.g., author of a post, creator of a product).
   • group_id: ID of a related grouping or collection (e.g., project ID, organization ID).

5. Searchable Identifiers

   • title: A short descriptive title (e.g., post title, product name, username).
   • subtitle: Secondary descriptor (subtitle, abstract, or summary).
   • tags: A list of tags or keywords for faceted filtering.

6. Location Information

   • location: Geographical location associated with the entity, if applicable (e.g., city, country).

7. Generic Content

   • content: Concatenation of all other non-first-class searchable fields.

Example: Aggregated View Design

CREATE VIEW search_view AS
SELECT 
    -- Shared across all entities
    entity_id, 
    entity_type,         -- Type of entity (e.g., “user”, “post”, “product”).
    category,            -- High-level category.
    status,              -- State of the entity.
    owner_id,            -- ID of the owner or creator.
    group_id,            -- Associated group or collection.
    title,               -- Primary descriptor.
    subtitle,            -- Secondary descriptor (subtitle, abstract, or summary).
    tags,                -- List of tags or keywords.
    location,            -- Associated geographical location.
    created_at, 
    updated_at, 
    
    -- Concatenated content field for full-text search
    content
FROM (
    -- Example for "users"
    SELECT 
        user_id AS entity_id, 
        'user' AS entity_type, 
        created_at, 
        updated_at, 
        NULL AS category, 
        status, 
        user_id AS owner_id, 
        NULL AS group_id, 
        name AS title, 
        tags, 
        location, 
        bio || ' ' || additional_info AS content
    FROM users
    UNION ALL
    -- Example for "posts"
    SELECT 
        post_id AS entity_id, 
        'post' AS entity_type, 
        created_at, 
        updated_at, 
        category, 
        status, 
        author_id AS owner_id, 
        project_id AS group_id, 
        title, 
        tags, 
        location, 
        title || ' ' || body || ' ' || metadata AS content
    FROM posts
    UNION ALL
    -- Example for "products"
    SELECT 
        product_id AS entity_id, 
        'product' AS entity_type, 
        created_at, 
        updated_at, 
        category, 
        status, 
        creator_id AS owner_id, 
        NULL AS group_id, 
        name AS title, 
        tags, 
        location, 
        name || ' ' || description || ' ' || specifications AS content
    FROM products
);

Why These Fields?

1. entity_id and entity_type: Essential for identifying the source and type of data in searches.
2. created_at and updated_at: Useful for temporal filtering, e.g., finding recent or outdated entities.
3. category and status: Allow faceted search based on the nature or lifecycle stage of the entity.
4. owner_id and group_id: Enable filtering by ownership or related collections.
5. title, subtitle and tags: Provide concise, meaningful entry points for both display and filtering.
6. content: Consolidates all text fields to ensure comprehensive full-text search.