Spaces:

ASown
/

agent-iot

Sleeping

agent-iot / schema.sql

Consolidate project history under ASown

f368481 4 months ago

2.22 kB

-- Snapshot schema cho docker-entrypoint-initdb (chạy lần đầu khi tạo volume DB).
-- App cũng gọi Base.metadata.create_all() lúc khởi động nên đây chủ yếu là tham
-- chiếu + tạo sẵn database 'keycloak' (Phase 5 dùng chung Postgres này).

CREATE TABLE IF NOT EXISTS users (
    id            SERIAL PRIMARY KEY,
    is_deleted    BOOLEAN NOT NULL DEFAULT false,
    created_at    TIMESTAMP NOT NULL DEFAULT now(),
    updated_at    TIMESTAMP,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255),
    display_name  VARCHAR(100),
    role          VARCHAR(16) NOT NULL DEFAULT 'user',
    is_active     BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS conversations (
    id         SERIAL PRIMARY KEY,
    is_deleted BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP,
    user_id    INTEGER REFERENCES users (id),
    title      VARCHAR(255),
    model      VARCHAR(32)
);
CREATE INDEX IF NOT EXISTS conversations_user_idx
    ON conversations (user_id) WHERE is_deleted = false;

CREATE TABLE IF NOT EXISTS messages (
    id              SERIAL PRIMARY KEY,
    is_deleted      BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    conversation_id INTEGER NOT NULL REFERENCES conversations (id),
    role            VARCHAR(32) NOT NULL,
    blocks          JSONB NOT NULL
);
CREATE INDEX IF NOT EXISTS messages_conversation_idx ON messages (conversation_id);

CREATE TABLE IF NOT EXISTS artifacts (
    id              SERIAL PRIMARY KEY,
    is_deleted      BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    conversation_id INTEGER REFERENCES conversations (id),
    type            VARCHAR(20) NOT NULL,
    title           VARCHAR(255),
    local_path      VARCHAR(512),
    preview         JSONB
);
CREATE INDEX IF NOT EXISTS artifacts_conversation_idx
    ON artifacts (conversation_id) WHERE is_deleted = false;

-- Database riêng cho Keycloak (Phase 5) — bỏ qua lỗi nếu đã tồn tại.
SELECT 'CREATE DATABASE keycloak'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'keycloak')\gexec

	-- Snapshot schema cho docker-entrypoint-initdb (chạy lần đầu khi tạo volume DB).
	-- App cũng gọi Base.metadata.create_all() lúc khởi động nên đây chủ yếu là tham
	-- chiếu + tạo sẵn database 'keycloak' (Phase 5 dùng chung Postgres này).

	CREATE TABLE IF NOT EXISTS users (
	id SERIAL PRIMARY KEY,
	is_deleted BOOLEAN NOT NULL DEFAULT false,
	created_at TIMESTAMP NOT NULL DEFAULT now(),
	updated_at TIMESTAMP,
	email VARCHAR(255) NOT NULL UNIQUE,
	password_hash VARCHAR(255),
	display_name VARCHAR(100),
	role VARCHAR(16) NOT NULL DEFAULT 'user',
	is_active BOOLEAN NOT NULL DEFAULT true
	);

	CREATE TABLE IF NOT EXISTS conversations (
	id SERIAL PRIMARY KEY,
	is_deleted BOOLEAN NOT NULL DEFAULT false,
	created_at TIMESTAMP NOT NULL DEFAULT now(),
	updated_at TIMESTAMP,
	user_id INTEGER REFERENCES users (id),
	title VARCHAR(255),
	model VARCHAR(32)
	);
	CREATE INDEX IF NOT EXISTS conversations_user_idx
	ON conversations (user_id) WHERE is_deleted = false;

	CREATE TABLE IF NOT EXISTS messages (
	id SERIAL PRIMARY KEY,
	is_deleted BOOLEAN NOT NULL DEFAULT false,
	created_at TIMESTAMP NOT NULL DEFAULT now(),
	conversation_id INTEGER NOT NULL REFERENCES conversations (id),
	role VARCHAR(32) NOT NULL,
	blocks JSONB NOT NULL
	);
	CREATE INDEX IF NOT EXISTS messages_conversation_idx ON messages (conversation_id);

	CREATE TABLE IF NOT EXISTS artifacts (
	id SERIAL PRIMARY KEY,
	is_deleted BOOLEAN NOT NULL DEFAULT false,
	created_at TIMESTAMP NOT NULL DEFAULT now(),
	conversation_id INTEGER REFERENCES conversations (id),
	type VARCHAR(20) NOT NULL,
	title VARCHAR(255),
	local_path VARCHAR(512),
	preview JSONB
	);
	CREATE INDEX IF NOT EXISTS artifacts_conversation_idx
	ON artifacts (conversation_id) WHERE is_deleted = false;

	-- Database riêng cho Keycloak (Phase 5) — bỏ qua lỗi nếu đã tồn tại.
	SELECT 'CREATE DATABASE keycloak'
	WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'keycloak')\gexec