17 lines
1005 B
MySQL
17 lines
1005 B
MySQL
|
-- Additive: standalone work and plans never create a case.
|
||
|
|
CREATE TABLE IF NOT EXISTS quick_time_batches (
|
||
|
|
request_id UUID PRIMARY KEY, actor_id INTEGER NOT NULL REFERENCES users(user_id),
|
||
|
|
payload JSONB NOT NULL, result JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE TABLE IF NOT EXISTS quick_time_items (
|
||
|
|
id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(user_id),
|
||
|
|
customer_id INTEGER REFERENCES customers(id), contact_id INTEGER REFERENCES contacts(id),
|
||
|
|
description TEXT NOT NULL, priority TEXT NOT NULL DEFAULT 'normal',
|
||
|
|
starts_at TIMESTAMPTZ NOT NULL, ends_at TIMESTAMPTZ NOT NULL,
|
||
|
|
state TEXT NOT NULL CHECK(state IN ('planned','registered')),
|
||
|
|
time_entry_id INTEGER UNIQUE REFERENCES tmodule_times(id),
|
||
|
|
created_by INTEGER NOT NULL REFERENCES users(user_id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CHECK(ends_at>starts_at)
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS quick_time_items_period_idx ON quick_time_items(user_id,starts_at) WHERE state='planned';
|