26 lines
1.1 KiB
MySQL
26 lines
1.1 KiB
MySQL
|
-- Комментарии к заявкам (внутренние и с жителем)
|
|||
|
|
CREATE TABLE IF NOT EXISTS application_comments (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
application_id BIGINT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
|
|||
|
|
author_name TEXT NOT NULL,
|
|||
|
|
type VARCHAR(20) NOT NULL DEFAULT 'internal',
|
|||
|
|
text TEXT NOT NULL,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_application_comments_app ON application_comments(application_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_application_comments_type ON application_comments(type);
|
|||
|
|
|
|||
|
|
-- История изменений заявки
|
|||
|
|
CREATE TABLE IF NOT EXISTS application_history (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
application_id BIGINT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
|
|||
|
|
changed_by TEXT NOT NULL,
|
|||
|
|
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
field_name TEXT NOT NULL,
|
|||
|
|
old_value TEXT,
|
|||
|
|
new_value TEXT
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_application_history_app ON application_history(application_id);
|