26 lines
1022 B
MySQL
26 lines
1022 B
MySQL
|
-- AP A1.1 — CadenceInstance (minimal): eine offene Instanz pro Recurring, Tages-Tracking
|
||
|
|
|
||
|
|
CREATE TABLE cadence_instances (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
recurring_element_id UUID NOT NULL REFERENCES recurring_elements(id) ON DELETE CASCADE,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'open'
|
||
|
|
CHECK (status IN ('open', 'completed')),
|
||
|
|
due_at TIMESTAMPTZ NOT NULL,
|
||
|
|
completed_at TIMESTAMPTZ NULL,
|
||
|
|
measurement_note TEXT NOT NULL DEFAULT '',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_cadence_instances_tenant_recurring
|
||
|
|
ON cadence_instances(tenant_id, recurring_element_id);
|
||
|
|
|
||
|
|
CREATE INDEX idx_cadence_instances_open_due
|
||
|
|
ON cadence_instances(tenant_id, due_at)
|
||
|
|
WHERE status = 'open';
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX idx_cadence_instances_one_open_per_recurring
|
||
|
|
ON cadence_instances(recurring_element_id)
|
||
|
|
WHERE status = 'open';
|