میزکارنمونهٔ طراحی محصول

میزکار

طراحی دیتابیس

مدل رابطه‌ای، عضویت، دسترسی مؤثر، تراکنش‌های اصلی و DDL پیشنهادی PostgreSQL.

مدل رابطه‌ای پیشنهادی

مدل برای انتشار چندکاربره با PostgreSQL پیشنهاد شده. فایل دمو رفتارها را با دادهٔ محلی مرورگر نمایش می‌دهد. کلیدهای مرکب، رابطه‌های وابسته به پروژه را در همان فضای کاری و پروژه نگه می‌دارند.

فضای کاریپروژهصفحهلیستکار
دامنهجدول‌های اصلیرابطه و قید
هویتapp_users، workspaces، workspace_membersایمیل یکتا بدون حساسیت به بزرگی حروف. هر فضای کاری یک مدیر اصلی دارد. ایجاد و انتقال مالکیت تراکنشی است.
بردprojects، project_pages، project_lists، project_statuses، tasksهر کار به یک لیست و یک وضعیت از همان پروژه وصل است. وضعیت آخر مطابق دمو نقش ستون انجام‌شده را دارد.
اعضا و دسترسیproject_members، teams، team_members، team_page_grants، team_list_grants، project_invitationsعضویت مستقیم و دسترسی تیمی مستقل‌اند. مقدار صفر لغو صریح دسترسی مستقیم است.
همکاری روی کارtask_assignees، task_tags، task_dependencies، task_comments، task_checklists، checklist_items، task_attachments، task_watchersتخصیص و تگ چندبه‌چند. پیوست در فضای ذخیرهٔ فایل و فقط فراداده در دیتابیس.
زمان و پیامtime_entries، conversations، conversation_members، messagesبرای هر عضو در هر فضای کاری حداکثر یک تایمر باز. دسترسی پیام از عضویت در گفتگو می‌آید.
ثبت رویدادactivity_events، notifications، outboxرویداد و تغییر دامنه در یک تراکنش. اعلان و ایمیل از صف ارسال شوند.

محاسبهٔ دسترسی مؤثر

ترتیبقاعدهنمونه
۱عضویت فعال در همان فضای کاری لازم است. دعوت معلق هنوز دسترسی فعال ایجاد نمی‌کند.شخصی که دعوت را نپذیرفته، پروژه را نمی‌بیند.
۲مدیر فضای کاری و مالک پروژه اختیار مدیریت دارند. سطح مستقیم ۴ در پروژه به معنی راهبر همان پروژه است.سارا دعوت فروشگاه کتاب را انجام می‌دهد. ساخت پروژهٔ تازه در اختیار مدیر است.
۳وجود سطح مستقیم، حتی سطح صفر، نتیجهٔ تیم‌ها را برای آن فرد بازنویسی می‌کند.عضو با سطح مستقیم مشاهده، از عضویت تیمی سطح ویرایش نمی‌گیرد.
۴در دسترسی تیمی، مقدار لیست بر مقدار صفحه مقدم است. از تیم‌های عضو، بیشترین مقدار مؤثر اعمال می‌شود.تیم اجرا می‌تواند فقط یک لیست را باز کند.
۵مشاهده و دیدگاه ۱، ثبت و جابه‌جایی ۲، تغییر مشخصات ۳، مدیریت ۴. پروژهٔ بسته عملیات تغییر ندارد.سطح ۴ تیمی می‌تواند کار را مدیریت کند. واگذاری دسترسی پروژه فقط برای مدیر، مالک یا راهبر مستقیم مجاز است.

برای نسخهٔ آنلاین، شناسهٔ کاربر را از نشست معتبر بگیرید. API باید همین قواعد را در هر عملیات بررسی کند. RLS با نقش محدود دیتابیس و سیاست پروژه/لیست لایهٔ دوم است. DDL پایین ساختار داده را تعریف می‌کند و سیاست‌های اجرایی RLS و احراز هویت هنوز پیاده‌سازی نشده‌اند.

تراکنش و قواعد تغییر

عملیاتتراکنش و کنترل
ساخت پروژهعضویت فعال مدیر را تأیید کنید. پروژه، صفحهٔ اولیه، ستون‌های پیش‌فرض و رویداد را یک‌جا بسازید.
دعوت و پذیرشدعوت ثبت‌شده برای ایمیل تازه، توکن هش‌شده و تاریخ انقضا دارد. پذیرش با ایمیل تأییدشده، در تراکنش برابر همان دعوت، عضویت فضای کاری و سطح پروژه را می‌سازد. دعوت منقضی را پیش از ارسال مجدد revoke کنید. عضو فعلی با مجوز مدیر یا راهبر سطح مستقیم می‌گیرد. راهبر فقط سطوح ۰ تا ۳ را مدیریت می‌کند و سطح هم‌رده را تغییر نمی‌دهد.
ثبت و تغییر کارشمارهٔ پروژه از افزایش اتمیک next_task_no رزرو می‌شود. تغییر با شرط version جلوی بازنویسی همزمان را می‌گیرد. تغییر وضعیت، موعد و undo باید نسخهٔ مورد انتظار و فعالیت کار را ثبت کنند.
تقویمتاریخ به‌صورت date و ساعت اختیاری ذخیره می‌شود. UI با منطقهٔ فضای کاری روز را پیدا می‌کند و روز را شمسی نمایش می‌دهد. موعد بر تاریخ شروع مقدم است. کارهای باز اول و بعد اولویت، ساعت و ترتیب ثبت.
وابستگی و بایگانیثبت وابستگی با CTE بازگشتی و قفل در سطح پروژه برای جلوگیری از چرخه. بایگانی منطقی پیش‌فرض است. حذف سخت پروژه نیاز به سیاست نگهداری فایل و رخداد دارد.
اعلان و جست‌وجوقبل از نمایش اعلان یا بازکردن پیوست، دسترسی فعلی هدف را دوباره بررسی کنید. دسترسی جست‌وجو باید قبل از رتبه‌بندی نتیجه اعمال شود.

نمونهٔ خواندن سه کار در تقویم

کوئری زیر پس از اعمال محدودیت دید کاربر اجرا می‌شود. برای نمای روز شرط display_order حذف می‌شود.

-- Production query after the API or RLS has restricted visible tasks.
-- :workspace_id, :from_on and :to_on are bound parameters.
WITH ranked AS (
  SELECT t.*, COALESCE(t.due_on,t.start_on) AS day,
         row_number() OVER (
           PARTITION BY COALESCE(t.due_on,t.start_on)
           ORDER BY (s.position = last_status.last_position),
                    t.priority DESC,
                    (CASE WHEN t.due_on IS NOT NULL THEN t.due_time ELSE t.start_time END) NULLS LAST,
                    t.created_at, t.id
         ) AS display_order
  FROM tasks t
  JOIN project_lists l ON (l.workspace_id,l.project_id,l.id)=(t.workspace_id,t.project_id,t.list_id)
  JOIN project_statuses s ON (s.workspace_id,s.project_id,s.id)=(t.workspace_id,t.project_id,t.status_id)
  CROSS JOIN LATERAL (
    SELECT max(position) AS last_position FROM project_statuses
    WHERE workspace_id=t.workspace_id AND project_id=t.project_id
  ) last_status
  WHERE t.workspace_id=:workspace_id AND t.archived_at IS NULL AND l.archived_at IS NULL
    AND COALESCE(t.due_on,t.start_on) BETWEEN :from_on AND :to_on
)
SELECT * FROM ranked WHERE display_order<=3 ORDER BY day,display_order;

طرح SQL قابل کپی

اسکیمای پایهٔ پیشنهادی. بدون اجرای migration یا اتصال به سرور.

-- Proposed PostgreSQL core schema for Mizkar.
-- UUIDs are supplied by the API. All application timestamps are UTC instants.
BEGIN;
CREATE TABLE app_users (
  id uuid PRIMARY KEY, email text NOT NULL, display_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX app_users_email_ci ON app_users (lower(email));
CREATE TABLE workspaces (
  id uuid PRIMARY KEY, slug text NOT NULL UNIQUE, name text NOT NULL,
  time_zone text NOT NULL DEFAULT 'Asia/Tehran'
);
CREATE TABLE workspace_members (
  workspace_id uuid REFERENCES workspaces ON DELETE CASCADE,
  user_id uuid REFERENCES app_users,
  role text NOT NULL CHECK (role IN ('owner','member')),
  state text NOT NULL CHECK (state IN ('active','suspended')),
  PRIMARY KEY (workspace_id,user_id)
);
CREATE UNIQUE INDEX one_workspace_owner ON workspace_members (workspace_id) WHERE role='owner';

CREATE TABLE projects (
  workspace_id uuid REFERENCES workspaces ON DELETE CASCADE, id uuid,
  owner_id uuid NOT NULL, name text NOT NULL, description text NOT NULL DEFAULT '',
  closed_at timestamptz, next_task_no bigint NOT NULL DEFAULT 1 CHECK (next_task_no>0),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,id),
  FOREIGN KEY (workspace_id,owner_id) REFERENCES workspace_members
);
CREATE TABLE project_members (
  workspace_id uuid, project_id uuid, user_id uuid,
  level smallint NOT NULL CHECK (level BETWEEN 0 AND 4),
  PRIMARY KEY (workspace_id,project_id,user_id),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members
);
CREATE TABLE project_pages (
  workspace_id uuid, project_id uuid, id uuid, name text NOT NULL, position integer NOT NULL,
  PRIMARY KEY (workspace_id,project_id,id),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE
);
CREATE TABLE project_lists (
  workspace_id uuid, project_id uuid, id uuid, page_id uuid NOT NULL, name text NOT NULL,
  position integer NOT NULL, archived_at timestamptz,
  PRIMARY KEY (workspace_id,project_id,id),
  FOREIGN KEY (workspace_id,project_id,page_id) REFERENCES project_pages ON DELETE CASCADE
);
CREATE TABLE project_statuses (
  workspace_id uuid, project_id uuid, id uuid, name text NOT NULL, position integer NOT NULL,
  PRIMARY KEY (workspace_id,project_id,id),
  UNIQUE (workspace_id,project_id,position) DEFERRABLE INITIALLY IMMEDIATE,
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE
);
CREATE TABLE teams (
  workspace_id uuid, project_id uuid, id uuid, name text NOT NULL,
  PRIMARY KEY (workspace_id,project_id,id),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE
);
CREATE TABLE team_members (
  workspace_id uuid, project_id uuid, team_id uuid, user_id uuid,
  PRIMARY KEY (workspace_id,project_id,team_id,user_id),
  FOREIGN KEY (workspace_id,project_id,team_id) REFERENCES teams ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members
);
CREATE INDEX team_members_by_user ON team_members(workspace_id,user_id,project_id);
CREATE TABLE team_page_grants (
  workspace_id uuid, project_id uuid, team_id uuid, page_id uuid,
  level smallint NOT NULL CHECK (level BETWEEN 0 AND 4),
  PRIMARY KEY (workspace_id,project_id,team_id,page_id),
  FOREIGN KEY (workspace_id,project_id,team_id) REFERENCES teams ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,project_id,page_id) REFERENCES project_pages ON DELETE CASCADE
);
CREATE TABLE team_list_grants (
  workspace_id uuid, project_id uuid, team_id uuid, list_id uuid,
  level smallint NOT NULL CHECK (level BETWEEN 0 AND 4),
  PRIMARY KEY (workspace_id,project_id,team_id,list_id),
  FOREIGN KEY (workspace_id,project_id,team_id) REFERENCES teams ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,project_id,list_id) REFERENCES project_lists ON DELETE CASCADE
);
CREATE TABLE project_invitations (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL, project_id uuid NOT NULL,
  email text NOT NULL, level smallint NOT NULL CHECK (level BETWEEN 1 AND 4),
  invited_by uuid NOT NULL, token_digest bytea NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL,
  accepted_at timestamptz, revoked_at timestamptz,
  CHECK (expires_at>created_at), CHECK (accepted_at IS NULL OR revoked_at IS NULL),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,invited_by) REFERENCES workspace_members
);
CREATE UNIQUE INDEX one_open_invite ON project_invitations(workspace_id,project_id,lower(email))
  WHERE accepted_at IS NULL AND revoked_at IS NULL;

CREATE TABLE tasks (
  workspace_id uuid, project_id uuid, id uuid, task_no bigint NOT NULL CHECK (task_no>0),
  list_id uuid NOT NULL, status_id uuid NOT NULL, author_id uuid NOT NULL,
  title text NOT NULL CHECK (length(btrim(title)) BETWEEN 1 AND 180),
  description text NOT NULL DEFAULT '', position bigint NOT NULL DEFAULT 0,
  start_on date, start_time time, due_on date, due_time time,
  priority smallint NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 3),
  progress smallint NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
  weight numeric(8,2) NOT NULL DEFAULT 0 CHECK (weight>=0),
  archived_at timestamptz, version integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,project_id,id),
  UNIQUE (workspace_id,project_id,task_no),
  CHECK (start_time IS NULL OR start_on IS NOT NULL),
  CHECK (due_time IS NULL OR due_on IS NOT NULL),
  CHECK (due_on IS NULL OR start_on IS NULL OR due_on>start_on OR
    (due_on=start_on AND (due_time IS NULL OR start_time IS NULL OR due_time>=start_time))),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,project_id,list_id) REFERENCES project_lists,
  FOREIGN KEY (workspace_id,project_id,status_id) REFERENCES project_statuses,
  FOREIGN KEY (workspace_id,author_id) REFERENCES workspace_members
);
CREATE INDEX tasks_board ON tasks(workspace_id,project_id,list_id,status_id,position) WHERE archived_at IS NULL;
CREATE INDEX tasks_calendar ON tasks(workspace_id,(coalesce(due_on,start_on)),priority DESC) WHERE archived_at IS NULL;
CREATE TABLE task_assignees (
  workspace_id uuid, project_id uuid, task_id uuid, user_id uuid, assigned_by uuid NOT NULL,
  PRIMARY KEY (workspace_id,project_id,task_id,user_id),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members,
  FOREIGN KEY (workspace_id,assigned_by) REFERENCES workspace_members
);
CREATE INDEX tasks_by_assignee ON task_assignees (workspace_id,user_id,project_id,task_id);
CREATE TABLE task_dependencies (
  workspace_id uuid, project_id uuid, task_id uuid, prerequisite_id uuid,
  PRIMARY KEY (workspace_id,project_id,task_id,prerequisite_id),
  CHECK (task_id<>prerequisite_id),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,project_id,prerequisite_id) REFERENCES tasks ON DELETE CASCADE
);
CREATE TABLE project_tags (
  workspace_id uuid, project_id uuid, id uuid, label text NOT NULL, color_token text,
  PRIMARY KEY (workspace_id,project_id,id),
  UNIQUE (workspace_id,project_id,label),
  FOREIGN KEY (workspace_id,project_id) REFERENCES projects ON DELETE CASCADE
);
CREATE TABLE task_tags (
  workspace_id uuid, project_id uuid, task_id uuid, tag_id uuid,
  PRIMARY KEY (workspace_id,project_id,task_id,tag_id),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,project_id,tag_id) REFERENCES project_tags ON DELETE CASCADE
);
CREATE TABLE task_comments (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL, project_id uuid NOT NULL, task_id uuid NOT NULL,
  author_id uuid NOT NULL, body text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,author_id) REFERENCES workspace_members
);
CREATE TABLE task_checklists (
  workspace_id uuid, project_id uuid, task_id uuid, id uuid, title text NOT NULL,
  PRIMARY KEY (workspace_id,project_id,task_id,id),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE
);
CREATE TABLE checklist_items (
  workspace_id uuid, project_id uuid, task_id uuid, checklist_id uuid, id uuid,
  title text NOT NULL, done boolean NOT NULL DEFAULT false, position integer NOT NULL,
  PRIMARY KEY (workspace_id,project_id,task_id,checklist_id,id),
  FOREIGN KEY (workspace_id,project_id,task_id,checklist_id) REFERENCES task_checklists ON DELETE CASCADE
);
CREATE TABLE task_attachments (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL, project_id uuid NOT NULL, task_id uuid NOT NULL,
  uploaded_by uuid NOT NULL, object_key text NOT NULL UNIQUE, file_name text NOT NULL,
  bytes bigint NOT NULL CHECK (bytes>0), content_type text NOT NULL,
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,uploaded_by) REFERENCES workspace_members
);
CREATE TABLE task_watchers (
  workspace_id uuid, project_id uuid, task_id uuid, user_id uuid,
  PRIMARY KEY (workspace_id,project_id,task_id,user_id),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members
);
CREATE TABLE time_entries (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL, project_id uuid NOT NULL, task_id uuid NOT NULL,
  user_id uuid NOT NULL, started_at timestamptz NOT NULL, finished_at timestamptz, note text,
  CHECK (finished_at IS NULL OR finished_at>=started_at),
  FOREIGN KEY (workspace_id,project_id,task_id) REFERENCES tasks ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members
);
CREATE UNIQUE INDEX one_running_timer ON time_entries(workspace_id,user_id) WHERE finished_at IS NULL;
CREATE TABLE conversations (
  workspace_id uuid REFERENCES workspaces ON DELETE CASCADE, id uuid,
  name text NOT NULL, kind text NOT NULL CHECK(kind IN ('room','direct')),
  PRIMARY KEY (workspace_id,id)
);
CREATE TABLE conversation_members (
  workspace_id uuid, conversation_id uuid, user_id uuid,
  PRIMARY KEY (workspace_id,conversation_id,user_id),
  FOREIGN KEY (workspace_id,conversation_id) REFERENCES conversations ON DELETE CASCADE,
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members
);
CREATE TABLE messages (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL, conversation_id uuid NOT NULL,
  author_id uuid NOT NULL, body text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (workspace_id,conversation_id,author_id) REFERENCES conversation_members
);
CREATE TABLE activity_events (
  workspace_id uuid REFERENCES workspaces ON DELETE CASCADE, id uuid,
  project_id uuid, task_id uuid, actor_id uuid NOT NULL, kind text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,id),
  FOREIGN KEY (workspace_id,actor_id) REFERENCES workspace_members
);
-- Activity uses immutable target snapshots so historical events survive task deletion.
CREATE INDEX activity_by_project ON activity_events(workspace_id,project_id,created_at DESC);
CREATE TABLE notifications (
  workspace_id uuid, user_id uuid, event_id uuid, read_at timestamptz,
  PRIMARY KEY (workspace_id,user_id,event_id),
  FOREIGN KEY (workspace_id,user_id) REFERENCES workspace_members,
  FOREIGN KEY (workspace_id,event_id) REFERENCES activity_events ON DELETE CASCADE
);
CREATE TABLE outbox (
  id uuid PRIMARY KEY, workspace_id uuid NOT NULL REFERENCES workspaces ON DELETE CASCADE,
  kind text NOT NULL, payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
  sent_at timestamptz
);
COMMIT;