31 lines
1.3 KiB
SQL
31 lines
1.3 KiB
SQL
|
-- Document ↔ klant/project koppelingen + auto-ingest log
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS document_links (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
storage_path TEXT NOT NULL,
|
||
|
|
is_folder BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
client_id INT REFERENCES clients(id) ON DELETE CASCADE,
|
||
|
|
project_id INT REFERENCES cockpit_projects(id) ON DELETE SET NULL,
|
||
|
|
link_type VARCHAR(32) NOT NULL DEFAULT 'nas_share',
|
||
|
|
notes TEXT DEFAULT '',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
created_by VARCHAR(64) DEFAULT 'cockpit'
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_document_links_unique
|
||
|
|
ON document_links (storage_path, COALESCE(client_id, 0), COALESCE(project_id, 0));
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_document_links_client ON document_links (client_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_document_links_project ON document_links (project_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_document_links_path ON document_links (storage_path);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS ingest_automation_log (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
source VARCHAR(64) NOT NULL,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'ok',
|
||
|
|
details JSONB NOT NULL DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_ingest_automation_log_at ON ingest_automation_log (created_at DESC);
|