--- ticket-states

ALTER TABLE tickets
  ADD state VARCHAR(32);

UPDATE tickets
SET [state] = (
  CASE
  WHEN [open] = 0
    THEN 'closed'
  ELSE
    (
      CASE
      WHEN assignee_id IS NOT NULL
        THEN 'work_in_progress'
      ELSE 'pending'
      END
    )
  END
);

CREATE INDEX tickets_project_id_state_created_at_ix
  ON tickets (project_id, [state], created_at);
