docker/postgres/init/01_schema.sql - master - Vibek/Agent_GG

docker/postgres/init/01_schema.sql

56 строк2 KB

Vladimir

P0 безопасность API, auth и прод-конфиг

26 май 2026, 19:02

26 май 2026, 19:02ca07c54

CREATE TABLE IF NOT EXISTS listings (
    id SERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    link VARCHAR(1000) NOT NULL UNIQUE,
    address VARCHAR(500) NULL,
    full_description TEXT NULL,
    price DECIMAL(15,2) NULL,
    operation_type VARCHAR(50) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    source VARCHAR(100) NOT NULL DEFAULT 'avito'
);

CREATE TABLE IF NOT EXISTS listing_params (
    id SERIAL PRIMARY KEY,
    listing_id INTEGER NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
    area_total DECIMAL(10,2) NULL,
    area_rent DECIMAL(10,2) NULL,
    floor INTEGER NULL,
    floors_total INTEGER NULL,
    price_total DECIMAL(15,2) NULL,
    price_per_sqm DECIMAL(10,2) NULL,
    power_kw DECIMAL(8,2) NULL,
    ceiling_height DECIMAL(5,2) NULL,
    params_extra JSONB NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(320) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS ix_users_email_unique
    ON users(email);

CREATE INDEX IF NOT EXISTS ix_listings_price
    ON listings(price);

CREATE INDEX IF NOT EXISTS ix_listings_operation_type
    ON listings(operation_type);

CREATE INDEX IF NOT EXISTS ix_listings_title_fts
    ON listings USING GIN (to_tsvector('russian', coalesce(title, '')));

CREATE INDEX IF NOT EXISTS ix_listings_address_fts
    ON listings USING GIN (to_tsvector('russian', coalesce(address, '')));

CREATE INDEX IF NOT EXISTS ix_listings_full_description_fts
    ON listings USING GIN (to_tsvector('russian', coalesce(full_description, '')));

CREATE INDEX IF NOT EXISTS ix_listing_params_params_extra_gin
    ON listing_params USING GIN (params_extra);