Database Schema

Complete reference for the ɳTask application database schema with advanced collaborative features.

Overview

The schema implements a Google Docs-like collaborative todo list system with:

Multiple lists per user with customization
Advanced todo metadata (priority, tags, due dates, location, recurring, attachments)
Real-time presence tracking
Granular sharing permissions (owner/editor/viewer)
Smart notifications (6 types)
User preferences with real-time sync
Background workers (recurring tasks, reminders, geolocation)

Total Tables: 8 Total Indexes: 20+ Total Foreign Keys: 12+ Total Triggers: 5

Tables

`app_profiles`

User profiles, automatically created on signup. Extended with preferences support.

Column	Type	Description
`id`	UUID	Primary key (references auth.users.id)
`email`	TEXT	User's email address
`display_name`	TEXT	Display name for UI
`avatar_url`	TEXT	Avatar image URL
`bio`	TEXT	User bio/description
`time_format`	TEXT	Preferred time format (12h or 24h)
`theme_preference`	TEXT	Theme preference (light, dark, system)
`default_list_id`	UUID	Default list to open (references app_lists.id)
`notification_settings`	JSONB	Notification preferences
`created_at`	TIMESTAMPTZ	When created
`updated_at`	TIMESTAMPTZ	Last updated (auto-updated)

Indexes:

Primary key on id
Foreign key index on default_list_id

Triggers:

Auto-creates profile when user signs up
Auto-updates updated_at on changes

Example:

query {
  app_profiles {
    id
    email
    display_name
    avatar_url
    time_format
    theme_preference
    default_list_id
    notification_settings
  }
}

`app_lists`

Todo list containers. Each user can have multiple lists with custom colors, icons, and locations.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`user_id`	UUID	Owner (references auth.users.id)
`title`	TEXT	List title (default: "Untitled List")
`description`	TEXT	Optional description
`color`	TEXT	Hex color code (default: "#6366f1")
`icon`	TEXT	Icon name or emoji (default: "list")
`is_default`	BOOLEAN	Whether this is the user's default list
`position`	INTEGER	Sort order (for drag-and-drop)
`location_name`	TEXT	Location name (e.g., "Whole Foods")
`location_lat`	NUMERIC(10,8)	Latitude for geo-reminders
`location_lng`	NUMERIC(11,8)	Longitude for geo-reminders
`location_radius`	INTEGER	Reminder radius in meters (default: 100)
`reminder_on_arrival`	BOOLEAN	Enable arrival notifications
`created_at`	TIMESTAMPTZ	When created
`updated_at`	TIMESTAMPTZ	Last updated (auto-updated)

Indexes:

Primary key on id
Index on user_id (for user queries)
Index on position (for sorting)

Permissions (RLS):

Users can CRUD their own lists
Users can view lists shared with them (via app_list_shares)

Triggers:

Auto-updates updated_at on changes
Auto-creates default list on user signup

Example:

mutation {
  insert_app_lists_one(object: {
    title: "Grocery Shopping"
    description: "Weekly groceries"
    color: "#10b981"
    icon: "🛒"
    location_name: "Whole Foods Market"
    location_lat: 37.7749
    location_lng: -122.4194
    location_radius: 100
    reminder_on_arrival: true
  }) {
    id
    title
    color
    icon
  }
}

`app_todos`

Todo items with extensive metadata for advanced task management.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`user_id`	UUID	Owner (references auth.users.id)
`list_id`	UUID	Parent list (references app_lists.id)
`title`	TEXT	Todo title (required)
`description`	TEXT	Optional description
`completed`	BOOLEAN	Completion status
`completed_at`	TIMESTAMPTZ	When completed (NULL if not completed)
`is_public`	BOOLEAN	Public visibility toggle
`position`	INTEGER	Sort order within list (default: 0)
`priority`	TEXT	Priority level (none, low, medium, high)
`tags`	TEXT[]	Array of tags
`notes`	TEXT	Long-form notes
`due_date`	TIMESTAMPTZ	When due (optional)
`reminder_time`	TIMESTAMPTZ	When to send reminder (optional)
`location_name`	TEXT	Location name for reminder
`location_lat`	NUMERIC(10,8)	Latitude for geo-reminder
`location_lng`	NUMERIC(11,8)	Longitude for geo-reminder
`location_radius`	INTEGER	Reminder radius in meters (default: 100)
`recurrence_rule`	TEXT	Recurrence pattern (daily, weekly, monthly)
`recurrence_parent_id`	UUID	Parent recurring todo (self-reference)
`attachments`	JSONB	Array of file attachments
`created_at`	TIMESTAMPTZ	When created
`updated_at`	TIMESTAMPTZ	Last updated (auto-updated)

Indexes:

Primary key on id
Index on user_id (for user queries)
Index on list_id (for list queries)
Index on is_public (for public todo queries)
Index on position (for sorting)
Index on due_date (for reminder queries)
Index on recurrence_rule (for recurring task queries)

Check Constraints:

priority must be one of: 'none', 'low', 'medium', 'high'

Foreign Keys:

user_id → auth.users.id ON DELETE CASCADE
list_id → app_lists.id ON DELETE CASCADE
recurrence_parent_id → app_todos.id ON DELETE CASCADE

Triggers:

Auto-updates updated_at on changes

Example:

mutation {
  insert_app_todos_one(object: {
    title: "Buy milk"
    list_id: "uuid-here"
    priority: "high"
    tags: ["groceries", "urgent"]
    notes: "Get organic whole milk"
    due_date: "2026-02-13T18:00:00Z"
    location_name: "Whole Foods"
    location_lat: 37.7749
    location_lng: -122.4194
    location_radius: 100
    attachments: [{
      id: "file-1"
      name: "shopping-list.pdf"
      url: "https://storage.../file.pdf"
      type: "application/pdf"
      size: 1024
    }]
  }) {
    id
    title
    priority
    tags
    due_date
  }
}

`app_list_shares`

List-level sharing with granular permissions.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`list_id`	UUID	Shared list (references app_lists.id)
`shared_with_user_id`	UUID	User receiving access (references auth.users.id)
`shared_with_email`	TEXT	Email address (for invite system)
`permission`	TEXT	Permission level (owner, editor, viewer)
`invited_by`	UUID	User who created the share (references auth.users.id)
`accepted_at`	TIMESTAMPTZ	When invite accepted (NULL = pending)
`created_at`	TIMESTAMPTZ	When created
`updated_at`	TIMESTAMPTZ	Last updated (auto-updated)

Indexes:

Primary key on id
Index on list_id (for list queries)
Index on shared_with_email (for invite lookups)

Unique Constraints:

UNIQUE(list_id, shared_with_email) - One share per email per list

Foreign Keys:

list_id → app_lists.id ON DELETE CASCADE
shared_with_user_id → auth.users.id ON DELETE CASCADE
invited_by → auth.users.id ON DELETE CASCADE

Permissions (RLS):

Only list owners can create/modify shares
Users can view shares for lists they own or are shared with

Triggers:

Auto-updates updated_at on changes

Example:

mutation {
  insert_app_list_shares_one(object: {
    list_id: "uuid-here"
    shared_with_email: "friend@example.com"
    permission: "editor"
  }) {
    id
    shared_with_email
    permission
    accepted_at
  }
}

`app_list_presence`

Real-time presence tracking for collaborative editing.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`list_id`	UUID	List being viewed (references app_lists.id)
`user_id`	UUID	User currently viewing (references auth.users.id)
`status`	TEXT	Current status (viewing, editing)
`editing_todo_id`	UUID	Todo being edited (references app_todos.id, optional)
`last_seen_at`	TIMESTAMPTZ	Heartbeat timestamp
`created_at`	TIMESTAMPTZ	When joined

Indexes:

Primary key on id
Index on list_id (for presence queries)
Index on last_seen_at (for cleanup queries)

Unique Constraints:

UNIQUE(list_id, user_id) - One presence record per user per list

Foreign Keys:

list_id → app_lists.id ON DELETE CASCADE
user_id → auth.users.id ON DELETE CASCADE
editing_todo_id → app_todos.id ON DELETE SET NULL

Check Constraints:

status must be one of: 'viewing', 'editing'

Triggers:

Auto-updated via upsert_presence() function (heartbeat every 30s)

Example:

mutation {
  upsert_presence(
    list_id: "uuid-here"
    user_id: "uuid-here"
    status: "editing"
    editing_todo_id: "uuid-here"
  )
}

`app_notifications`

In-app notification center.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`user_id`	UUID	Recipient (references auth.users.id)
`type`	TEXT	Notification type (new_todo, due_reminder, etc.)
`title`	TEXT	Notification title
`body`	TEXT	Notification message
`data`	JSONB	Additional metadata
`read`	BOOLEAN	Read status (default: false)
`action_url`	TEXT	Optional URL to navigate to
`created_at`	TIMESTAMPTZ	When created

Indexes:

Primary key on id
Index on user_id (for user notifications)

Foreign Keys:

user_id → auth.users.id ON DELETE CASCADE

Notification Types:

new_todo - New todo assigned
due_reminder - Todo due soon
shared_list - List shared with you
evening_reminder - Daily digest at 8pm
location_reminder - Arrived at list location
list_update - Collaborator updated list

Example:

query {
  app_notifications(
    where: { user_id: { _eq: "uuid" }, read: { _eq: false } }
    order_by: { created_at: desc }
  ) {
    id
    type
    title
    body
    data
    action_url
    created_at
  }
}

`app_recurring_instances`

Tracks completion of recurring todo instances.

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`parent_todo_id`	UUID	Parent recurring todo (references app_todos.id)
`instance_date`	DATE	Date of this instance (YYYY-MM-DD)
`completed`	BOOLEAN	Completion status for this instance
`completed_at`	TIMESTAMPTZ	When completed
`created_at`	TIMESTAMPTZ	When created

Indexes:

Primary key on id
Index on parent_todo_id (for instance lookups)

Unique Constraints:

UNIQUE(parent_todo_id, instance_date) - One instance per date

Foreign Keys:

parent_todo_id → app_todos.id ON DELETE CASCADE

Example:

query {
  app_recurring_instances(
    where: {
      parent_todo_id: { _eq: "uuid" }
      instance_date: { _eq: "2026-02-12" }
    }
  ) {
    id
    instance_date
    completed
    completed_at
  }
}

`app_todo_shares` (Legacy)

Legacy todo-level sharing. Deprecated in favor of list-level sharing.

Kept for backward compatibility. New features should use app_list_shares instead.

Database Functions

`set_updated_at()`

Auto-updates updated_at timestamp on table changes.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Applied to: app_profiles, app_todos, app_lists, app_list_shares

`upsert_presence()`

Upserts presence record with heartbeat update.

CREATE FUNCTION upsert_presence(
  p_list_id UUID,
  p_user_id UUID,
  p_status TEXT,
  p_editing_todo_id UUID DEFAULT NULL
) RETURNS void AS $$
BEGIN
  INSERT INTO app_list_presence (list_id, user_id, status, editing_todo_id, last_seen_at)
  VALUES (p_list_id, p_user_id, p_status, p_editing_todo_id, now())
  ON CONFLICT (list_id, user_id)
  DO UPDATE SET
    status = EXCLUDED.status,
    editing_todo_id = EXCLUDED.editing_todo_id,
    last_seen_at = now();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Row Level Security (RLS)

All tables have RLS enabled. Policies use auth.uid() to identify current user.

`app_lists` Policies:

Users can SELECT their own lists + shared lists
Users can INSERT/UPDATE/DELETE only their own lists

`app_todos` Policies:

Users can SELECT todos in accessible lists
Users can INSERT/UPDATE todos in lists where they have editor+ permission
Users can DELETE only their own todos

`app_list_shares` Policies:

Only list owners can INSERT/UPDATE/DELETE shares
Users can SELECT shares for accessible lists

`app_list_presence` Policies:

Users can SELECT presence for accessible lists
Users can UPSERT their own presence

Schema Diagram

┌─────────────────┐
│  auth.users     │
└────────┬────────┘
         │
         ├──────────────────────────────────────┐
         │                                      │
         ▼                                      ▼
┌─────────────────┐                    ┌─────────────────┐
│  app_profiles   │                    │   app_lists     │
│                 │                    │                 │
│  • email        │                    │  • title        │
│  • display_name │                    │  • color        │
│  • avatar_url   │                    │  • icon         │
│  • preferences  │                    │  • location     │
└─────────────────┘                    └────────┬────────┘
                                                │
                       ┌────────────────────────┼────────────────────────┐
                       │                        │                        │
                       ▼                        ▼                        ▼
              ┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
              │   app_todos     │     │ app_list_shares │     │app_list_presence│
              │                 │     │                 │     │                 │
              │  • title        │     │  • permission   │     │  • status       │
              │  • priority     │     │  • email        │     │  • editing_todo │
              │  • tags         │     │  • accepted_at  │     │  • last_seen    │
              │  • due_date     │     └─────────────────┘     └─────────────────┘
              │  • location     │
              │  • recurring    │
              │  • attachments  │
              └────────┬────────┘
                       │
                       ▼
              ┌─────────────────┐
              │app_recurring_   │
              │   instances     │
              │                 │
              │  • instance_date│
              │  • completed    │
              └─────────────────┘


┌─────────────────┐
│app_notifications│
│                 │
│  • type         │
│  • title        │
│  • body         │
│  • read         │
└─────────────────┘

Migration Path

From simple todos → collaborative lists:

✅ Create app_lists table
✅ Add list_id to app_todos
✅ Create default list for each user
✅ Move todos to default lists
✅ Add new tables: shares, presence, notifications, recurring
✅ Apply RLS policies

Performance Optimization

Indexes on all foreign keys and query columns
Partial indexes on due_date and recurrence_rule (WHERE NOT NULL)
GIN index on tags array (if needed)
BRIN index on created_at (for time-series queries)
Connection pooling via Hasura (default: 2 connections per replica)

Backup & Restore

# Backup
docker exec nself-postgres pg_dump -U postgres nself > backup.sql

# Restore
docker exec -i nself-postgres psql -U postgres nself < backup.sql

Last Updated: 2026-02-12 Schema Version: 2.0 (Collaborative Lists)

ɳTask • Home • GitHub • Changelog • Security • Contributing • Issues

Made with ❤️ for developers and AI tools • MIT License • Built on React Native, Vite, Hasura, PostgreSQL

Home

Getting Started

Features

Features

CLI & Agents

Backend

Architecture

Deployment

Reference

External

Uh oh!

Database Schema

Database Schema

Overview

Tables

app_profiles

app_lists

app_todos

app_list_shares

app_list_presence

app_notifications

app_recurring_instances

app_todo_shares (Legacy)

Database Functions

set_updated_at()

upsert_presence()

Row Level Security (RLS)

app_lists Policies:

app_todos Policies:

app_list_shares Policies:

app_list_presence Policies:

Schema Diagram

Migration Path

Performance Optimization

Backup & Restore

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Home

Clone this wiki locally

`app_profiles`

`app_lists`

`app_todos`

`app_list_shares`

`app_list_presence`

`app_notifications`

`app_recurring_instances`

`app_todo_shares` (Legacy)

`set_updated_at()`

`upsert_presence()`

`app_lists` Policies:

`app_todos` Policies:

`app_list_shares` Policies:

`app_list_presence` Policies: