Alireza HassaniandCursor
a6c2d4764e
Lets owners/admins track start/stop sessions and task lists per business day. Co-authored-by: Cursor <cursoragent@cursor.com>
63 lines
2.5 KiB
SQL
63 lines
2.5 KiB
SQL
-- Roll call / daily work log for business admins (customer dashboard)
|
|
|
|
CREATE TABLE roll_calls (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
work_date DATE NOT NULL,
|
|
start_time VARCHAR(5) NOT NULL,
|
|
end_time VARCHAR(5) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT roll_calls_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
|
|
CONSTRAINT roll_calls_user_id_fkey
|
|
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
|
|
CONSTRAINT roll_calls_business_user_date_unique
|
|
UNIQUE (business_id, user_id, work_date),
|
|
CONSTRAINT roll_calls_start_time_format
|
|
CHECK (start_time ~ '^\d{2}:\d{2}$'),
|
|
CONSTRAINT roll_calls_end_time_format
|
|
CHECK (end_time ~ '^\d{2}:\d{2}$')
|
|
);
|
|
|
|
CREATE INDEX idx_roll_calls_business_id ON roll_calls (business_id);
|
|
CREATE INDEX idx_roll_calls_user_id ON roll_calls (user_id);
|
|
CREATE INDEX idx_roll_calls_business_user_date
|
|
ON roll_calls (business_id, user_id, work_date DESC);
|
|
|
|
CREATE TRIGGER roll_calls_set_updated_at
|
|
BEFORE UPDATE ON roll_calls
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TABLE roll_call_tasks (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
roll_call_id BIGINT NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT roll_call_tasks_roll_call_id_fkey
|
|
FOREIGN KEY (roll_call_id) REFERENCES roll_calls (id) ON DELETE CASCADE,
|
|
CONSTRAINT roll_call_tasks_title_nonempty
|
|
CHECK (char_length(trim(title)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_roll_call_tasks_roll_call_id ON roll_call_tasks (roll_call_id);
|
|
|
|
INSERT INTO permissions (name, slug, group_name, description) VALUES
|
|
('View roll calls', 'roll_calls.read', 'roll_calls', 'View roll call / work day logs'),
|
|
('Create roll calls', 'roll_calls.create', 'roll_calls', 'Create roll call / work day logs'),
|
|
('Update roll calls', 'roll_calls.update', 'roll_calls', 'Update roll call / work day logs'),
|
|
('Delete roll calls', 'roll_calls.delete', 'roll_calls', 'Delete roll call / work day logs')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug LIKE 'roll_calls.%'
|
|
WHERE r.slug IN ('business_owner', 'owner', 'admin')
|
|
ON CONFLICT DO NOTHING;
|