Skip to Content
architectureDatabase Schema

Last Updated: 3/8/2026

Database Schema

Overview

LinkAce uses a relational database (MySQL, PostgreSQL, or SQLite) to store all application data. This document outlines the main database tables and their relationships.

Core Tables

users

Stores user account information.

ColumnTypeDescription
idbigintPrimary key
namevarcharUser’s display name
emailvarcharEmail address (unique)
email_verified_attimestampEmail verification timestamp
passwordvarcharHashed password
remember_tokenvarcharRemember me token
created_attimestampAccount creation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Unique: email

Stores bookmark/link information.

ColumnTypeDescription
idbigintPrimary key
user_idbigintForeign key to users
urltextThe bookmark URL
titlevarcharLink title
descriptiontextLink description
is_privatebooleanPrivacy flag
check_disabledbooleanDisable link checking
iconvarcharFavicon path
created_attimestampCreation time
updated_attimestampLast update time
deleted_attimestampSoft delete timestamp

Indexes:

  • Primary: id
  • Foreign: user_id references users(id)
  • Index: created_at, is_private
  • Soft delete: deleted_at

Relationships:

  • Belongs to: User
  • Has many: LinkCheck, Note
  • Belongs to many: Tag, List

tags

Stores tags for categorizing links.

ColumnTypeDescription
idbigintPrimary key
user_idbigintForeign key to users
namevarcharTag name
is_privatebooleanPrivacy flag
created_attimestampCreation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Foreign: user_id references users(id)
  • Index: name

Relationships:

  • Belongs to: User
  • Belongs to many: Link

lists

Stores curated lists of links.

ColumnTypeDescription
idbigintPrimary key
user_idbigintForeign key to users
namevarcharList name
descriptiontextList description
is_privatebooleanPrivacy flag
created_attimestampCreation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Foreign: user_id references users(id)

Relationships:

  • Belongs to: User
  • Belongs to many: Link

Pivot table for link-tag relationships (many-to-many).

ColumnTypeDescription
link_idbigintForeign key to links
tag_idbigintForeign key to tags

Indexes:

  • Foreign: link_id references links(id) ON DELETE CASCADE
  • Foreign: tag_id references tags(id) ON DELETE CASCADE
  • Unique: (link_id, tag_id)

Pivot table for link-list relationships (many-to-many).

ColumnTypeDescription
link_idbigintForeign key to links
list_idbigintForeign key to lists

Indexes:

  • Foreign: link_id references links(id) ON DELETE CASCADE
  • Foreign: list_id references lists(id) ON DELETE CASCADE
  • Unique: (link_id, list_id)

Monitoring & Archives

Stores link check history and status.

ColumnTypeDescription
idbigintPrimary key
link_idbigintForeign key to links
status_codeintegerHTTP status code
statusvarcharCheck status (success, failed, moved)
created_attimestampCheck time

Indexes:

  • Primary: id
  • Foreign: link_id references links(id) ON DELETE CASCADE
  • Index: created_at

Relationships:

  • Belongs to: Link

archives

Stores Internet Archive Wayback Machine snapshots.

ColumnTypeDescription
idbigintPrimary key
link_idbigintForeign key to links
archive_urltextWayback Machine URL
created_attimestampArchive creation time

Indexes:

  • Primary: id
  • Foreign: link_id references links(id) ON DELETE CASCADE

Relationships:

  • Belongs to: Link

Additional Features

notes

Stores user notes attached to links.

ColumnTypeDescription
idbigintPrimary key
link_idbigintForeign key to links
user_idbigintForeign key to users
notetextNote content
is_privatebooleanPrivacy flag
created_attimestampCreation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Foreign: link_id references links(id) ON DELETE CASCADE
  • Foreign: user_id references users(id) ON DELETE CASCADE

Relationships:

  • Belongs to: Link, User

api_tokens

Stores API authentication tokens.

ColumnTypeDescription
idbigintPrimary key
user_idbigintForeign key to users
namevarcharToken name/description
tokenvarcharHashed token (unique)
last_used_attimestampLast usage timestamp
created_attimestampCreation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Foreign: user_id references users(id) ON DELETE CASCADE
  • Unique: token

Relationships:

  • Belongs to: User

settings

Stores system and user settings.

ColumnTypeDescription
idbigintPrimary key
user_idbigintForeign key to users (null for system settings)
keyvarcharSetting key
valuetextSetting value (JSON or string)
created_attimestampCreation time
updated_attimestampLast update time

Indexes:

  • Primary: id
  • Foreign: user_id references users(id) ON DELETE CASCADE
  • Unique: (user_id, key)

Laravel Framework Tables

migrations

Tracks applied database migrations.

ColumnTypeDescription
idintPrimary key
migrationvarcharMigration name
batchintMigration batch number

failed_jobs

Stores failed queue jobs for retry.

ColumnTypeDescription
idbigintPrimary key
uuidvarcharUnique job identifier
connectiontextQueue connection
queuetextQueue name
payloadlongtextJob payload
exceptionlongtextException details
failed_attimestampFailure timestamp

Indexes:

  • Primary: id
  • Unique: uuid

password_reset_tokens

Stores password reset tokens.

ColumnTypeDescription
emailvarcharUser email
tokenvarcharReset token
created_attimestampCreation time

Indexes:

  • Primary: email

sessions

Stores user session data (if using database sessions).

ColumnTypeDescription
idvarcharSession ID
user_idbigintForeign key to users (nullable)
ip_addressvarcharClient IP
user_agenttextClient user agent
payloadlongtextSession data
last_activityintLast activity timestamp

Indexes:

  • Primary: id
  • Index: user_id, last_activity

jobs

Stores queued jobs (if using database queue).

ColumnTypeDescription
idbigintPrimary key
queuevarcharQueue name
payloadlongtextJob payload
attemptstinyintRetry attempts
reserved_atintReserved timestamp
available_atintAvailable timestamp
created_atintCreation timestamp

Indexes:

  • Primary: id
  • Index: queue

Database Relationships Diagram

users
  ├── has many → links
  ├── has many → tags
  ├── has many → lists
  ├── has many → notes
  └── has many → api_tokens

links
  ├── belongs to → user
  ├── has many → link_checks
  ├── has many → archives
  ├── has many → notes
  ├── belongs to many → tags (via link_tag)
  └── belongs to many → lists (via link_list)

tags
  ├── belongs to → user
  └── belongs to many → links (via link_tag)

lists
  ├── belongs to → user
  └── belongs to many → links (via link_list)

Performance Considerations

Indexing Strategy

  1. Primary Keys: All tables have auto-incrementing primary keys
  2. Foreign Keys: All foreign keys are indexed for join performance
  3. Search Fields: title, description, url are full-text indexed
  4. Filter Fields: is_private, created_at are indexed for filtering

Query Optimization

  • Use eager loading for relationships to prevent N+1 queries
  • Implement pagination for large result sets
  • Use database-level caching for frequently accessed data
  • Utilize query scopes for common filters

Soft Deletes

The links table uses soft deletes:

  • Deleted items have deleted_at timestamp set
  • Items can be restored from trash
  • Permanent deletion removes the record entirely

Migrations

All schema changes are version-controlled via Laravel migrations in:

database/migrations/

Running migrations:

php artisan migrate

Rolling back:

php artisan migrate:rollback

Database Collation

For proper Unicode support and case-insensitive searches:

  • MySQL: utf8mb4_unicode_ci (default)
  • PostgreSQL: en_US.utf8
  • SQLite: UTF-8 encoding

See Adjusting Database Collation for details.

Backup Recommendations

  1. Automated Backups: Use LinkAce’s built-in backup feature
  2. Database Dumps: Regular mysqldump/pg_dump backups
  3. Replication: Set up database replication for high availability
  4. Point-in-Time Recovery: Enable binary logging (MySQL) or WAL archiving (PostgreSQL)

See Application Backups for configuration.

Api ReferenceSecurity