DEV Community: Rina Park

Structuring the Database: Building an AI Task System [Floxis #2]

Rina Park — Thu, 16 Apr 2026 05:10:46 +0000

1. Overview

In this article, I document the development process of a personal project called Floxis (codename).

Floxis is a system (PersonalOS) that uses AI to structure natural language input and manage tasks. It is planned to be extended in the future to support features such as workflows.

In the previous phase of development, I adopted a minimal structure to get a rough understanding of how the system would behave. For more details, see the previous article.

This time, I designed a data model with future extensions in mind—particularly productivity analysis—and reorganized the database structure.

The design focuses on the following points:

Separation of current state and history
Avoiding strict workflow constraints
Maintaining a minimal structure while ensuring extensibility

In this article, I explain the design decisions behind this data model and the reasoning behind them.

2. Repository

GitHub Repository

3. Core Structure

The system is composed of the following five tables:

tasks: current state and basic task data
statuses: status definitions
categories: classification labels
projects: grouping of tasks
task_status_logs: status change history

4. Concept Diagram

5. Design Principles

Separate current state and history to keep operations lightweight while enabling analysis
Store statuses as definitions only; allow free transitions
Treat categories and projects as independent concepts
Apply categories at the project level while still allowing standalone tasks
Treat status_id as the single source of truth; completed_at is auxiliary
Keep the structure minimal but extensible

6. Design Decisions

In this design, I intentionally avoided some common approaches.

6.1. Why I didn’t implement strict state transitions

In reality, tasks don’t follow a strict linear flow:

start → on hold → resume → complete
complete → reopen

If we enforce transitions strictly, we introduce:

ordering constraints
invalid transition handling
UI complexity

For this project, I decided that:

👉 Knowing the current state and having a history log is sufficient.

6.2. Why I didn’t implement workflow constraints

Workflows tend to:

reduce flexibility
depend heavily on task granularity
be overkill for personal use

In personal task management, the meaning of states is often fluid.

So instead of enforcing workflows:

statuses are just definitions
transitions are unrestricted

6.3. How analysis works with only logs

Instead of modeling perfect workflows, analysis is result-based.

Examples:

time from creation → final completion
time from first “in progress” → completion

These can be derived from logs.

The goal is not perfect traceability, but:

👉 extracting meaningful metrics.

So logs are append-only and intentionally simple.

6.4. Why categories are tied to projects

I separate roles as:

Category = type of work (e.g., development, study)
Project = unit of effort (e.g., Floxis)

If tasks inside a project have independent categories:

categorization becomes fragmented
aggregation becomes harder

So I decided:

👉 project-level category is the default.

6.5. Why tasks can exist without categories or projects

Not all tasks are structured:

quick notes
one-off tasks
unclassified items

A system that requires structure upfront will fail in real use.

So:

project_id is optional
category_id is optional

👉 the system accepts unstructured input first.

7. Key Rules

7.1. Category handling

projects.category_id can be null
tasks.category_id is only for standalone tasks
If project_id is set, category is derived from the project

7.2. Status handling

tasks.status_id is the single source of truth
No workflow constraints

7.3. Task completion

completed_at is set only when status = completed
Reset to null if status changes
Represents current completion state, not history

Note: To keep status_id and completed_at consistent, synchronization is enforced at the database level using a trigger.

7.4. Status logs

task_status_logs is append-only
A new record is inserted on each status change
Used for analytics, not for enforcing workflows

8. ER Diagram

9. Implementation

The migration was done in three steps:

Add new structures
Migrate existing data
Remove legacy columns

Migration files:

The goal was to evolve the schema without breaking existing data.

10. Conclusion

Strict workflows can sometimes get in the way of personal productivity.

For Floxis, I’m taking a “log-first, constraint-last” approach—keeping the experience flexible while ensuring data integrity at the database level.

This design prioritizes flexibility over strict control, accepting some trade-offs in consistency to keep the system adaptable in its early stage.

In the next article, I’ll align the application layer with this new schema.

Designing Before Coding: Building an AI Task System [Floxis #1]

Rina Park — Wed, 15 Apr 2026 10:59:13 +0000

1. Overview

In this article, I’ll document the development process of a personal project called Floxis (codename).

To improve my development skills, I’m building this project by thinking through the design from the start.

Floxis is a system that uses AI to structure natural language input and manage tasks (PersonalOS). In the future, I plan to extend it to support workflows and other features.

2. Repository

GitHub Repository

3. What I did in Floxis #1

Defined the development scope
Selected the tech stack
Set up the initial project (Next.js / Supabase)

4. Defining the Development Scope

Floxis is intended to evolve into a system that manages tasks, habits, assets, and schedules in an integrated way.

However, for v0.1, I’m prioritizing building a minimal and stable foundation rather than expanding features.

4.1. What’s included in v0.1

Task creation (manual + AI input)
Task list display
Completion management

4.2. What’s not included

Habit tracking
Asset management
Schedule integration

4.3. Notes

AI will be integrated as part of the system, but its role and detailed design will be covered in a separate article.

5. Initial Project Setup

In Floxis #1, I set up the frontend, backend, and database to establish a minimal working system.

5.1. Creating the Next.js project

I created a Next.js project with the following settings:

TypeScript: enabled
ESLint: enabled
App Router: enabled (current standard)
React Compiler: disabled (prioritizing stability)

At this stage, I prioritized stability and ease of understanding over performance optimization.

5.2. Setting up the database with Supabase

I created a project in Supabase and defined the tasks table.

create table tasks (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  title_original text,
  status text not null default 'pending'
    check (status in ('pending', 'completed')),
  due_date date,
  category text,
  created_at timestamp with time zone default now(),
  completed_at timestamp with time zone
);

5.3. Connecting Next.js and Supabase

Installed @supabase/supabase-js
Set up environment variables in .env.local
Created a Supabase client

After that, I implemented data fetching on the top page and confirmed that data could be retrieved from the database.

5.4. Implementing task creation

Using Server Actions, I enabled adding tasks from the UI.

Form submission → insert into DB
Re-fetch the list using revalidatePath

This established the minimal loop of “create → display”.

5.5. Introducing migration management

I introduced migration management using the Supabase CLI to manage database changes as code.

supabase init
supabase link
Created a baseline with db pull
Committed migration files to Git

This ensures reproducibility and version control of the database schema.

6. What’s working after Floxis #1

At this point, the following minimal system is in place:

Task creation
Task list display
Database connectivity
Database schema versioning

7. Conclusion

Floxis #1 didn’t involve much code, but it established a minimal system that actually works.

By thinking through the design from the start, I feel I was able to clarify the foundation of the system.

In Floxis #2 and beyond, I’ll work on expanding the data structure, adding features, and integrating AI.