Rina Park

Posted on Apr 16

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

#softwareengineering #database #postgres #webdev

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.

DEV Community