refactor tables

[arnavsurve]

Create a single table for all tasks and a table for workspaces

This table will store information about each workspace, including which user it belongs to (foreign key referencing account_id in accounts)

CREATE TABLE workspaces (
    workspace_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    account_id INT REFERENCES accounts(id)
);

Modify tasks to contain a foreign key to reference the workspace_id from the workspaces table

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    due_date TIMESTAMP,
    completion VARCHAR(50),
    workspace_id INT REFERENCES workspaces(workspace_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Examples

Get all workspaces for a user:

SELECT * FROM workspaces WHERE account_id = 1;

Get all tasks in a workspace:

SELECT * FROM tasks WHERE workspace_id = 1;

Get all tasks across all workspaces for a user:

SELECT t.* FROM tasks t
JOIN workspaces w ON t.workspace_id = w.workspace_id
WHERE w.account_id = 1;

[linear[bot]]

TAS-34 refactor tasks tables