f3f1e7f6bc
Per #114 Custom POI weights — Макс feedback "садики и Мегамарт должны иметь разные веса". Sub-PR 1/4 per feedback_split_big_issues: 1. **#114 sub-PR 1 (this)**: schema migration 2. #114 sub-PR 2: backend Pydantic models + CRUD service 3. #114 sub-PR 3: POST /parcels/{cad}/analyze?profile= override + admin endpoint 4. #114 sub-PR 4: frontend 25 sliders UI + live preview ## Schema `data/sql/90_user_weight_profiles.sql`: - Table `user_weight_profiles` — SERIAL PK + user_id + profile_name + weights JSONB + is_default + timestamps - Unique constraint (user_id, profile_name) - B-tree index user_idx - Partial index `WHERE is_default = TRUE` (fast default lookup) - Trigger auto-updated_at via `set_updated_at()` function ## Compat with existing POI categories `weights` JSONB compatible с current `_POI_WEIGHTS` const в `backend/app/api/v1/parcels.py`: - school 1.5, kindergarten 1.5, pharmacy 0.8, hospital 0.6 - shop_mall 1.2, shop_supermarket 1.0, shop_small 0.5 - park 1.8, bus_stop 0.3, metro_stop 1.5, tram_stop -0.5 ## Idempotency - BEGIN/COMMIT обёртка - IF NOT EXISTS на CREATE TABLE/INDEX - DROP TRIGGER IF EXISTS → CREATE TRIGGER (safe re-run) - CREATE OR REPLACE FUNCTION (idempotent) ## Apply ```bash psql postgresql://gendesign:...@localhost:15432/gendesign \ -f data/sql/90_user_weight_profiles.sql ``` После apply таблица inert (нет backend code yet). Sub-PR 2 добавит CRUD. ## Vault `code/schemas/Schema_User_Weight_Profiles.md` — created (status: ready_to_apply, full sub-PR plan). Refs: #114 Co-authored-by: lekss361 <claudestars@proton.me>
66 lines
2.8 KiB
PL/PgSQL
66 lines
2.8 KiB
PL/PgSQL
-- 90_user_weight_profiles.sql
|
||
-- User-defined POI weight profiles для site_finder/analyze_parcel.
|
||
-- Per #114 (Макс feedback): разные веса для разных категорий (садики ≠ Мегамарт).
|
||
--
|
||
-- Apply order: после 89_drop_dead_brin_rosreestr_deals.sql
|
||
-- Dependencies: нет (standalone table, no FK to other tables)
|
||
--
|
||
-- POI categories compatible with _POI_WEIGHTS in backend/app/api/v1/parcels.py:
|
||
-- school, kindergarten, pharmacy, hospital, shop_mall, shop_supermarket,
|
||
-- shop_small, park, bus_stop, metro_stop, tram_stop
|
||
--
|
||
-- Idempotent: safe to run multiple times.
|
||
|
||
BEGIN;
|
||
|
||
CREATE TABLE IF NOT EXISTS user_weight_profiles (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id TEXT NOT NULL, -- пока admin token / простой ID; auth в Sprint 5
|
||
profile_name TEXT NOT NULL,
|
||
weights JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
||
description TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
UNIQUE (user_id, profile_name)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS user_weight_profiles_user_idx
|
||
ON user_weight_profiles (user_id);
|
||
|
||
-- Partial index для быстрого fetch default profile per user
|
||
CREATE INDEX IF NOT EXISTS user_weight_profiles_default_idx
|
||
ON user_weight_profiles (user_id) WHERE is_default = TRUE;
|
||
|
||
-- Trigger function для auto-update updated_at (shared, reusable across tables)
|
||
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- Drop trigger если ранее существовал (idempotent)
|
||
DROP TRIGGER IF EXISTS user_weight_profiles_updated_at ON user_weight_profiles;
|
||
CREATE TRIGGER user_weight_profiles_updated_at
|
||
BEFORE UPDATE ON user_weight_profiles
|
||
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
||
|
||
COMMENT ON TABLE user_weight_profiles IS
|
||
'POI weight profiles для site_finder. Per #114 (Макс feedback). '
|
||
'weights = {"school": 1.5, "kindergarten": 1.5, "shop_mall": 1.2, ...}';
|
||
|
||
COMMENT ON COLUMN user_weight_profiles.weights IS
|
||
'JSONB map: poi_category → weight (float, typically -1.5..2.5). '
|
||
'Совместимо с _POI_WEIGHTS default в backend/app/api/v1/parcels.py. '
|
||
'Категории: school, kindergarten, pharmacy, hospital, shop_mall, '
|
||
'shop_supermarket, shop_small, park, bus_stop, metro_stop, tram_stop.';
|
||
|
||
COMMENT ON COLUMN user_weight_profiles.user_id IS
|
||
'Простой строковый ID пользователя. Full auth integration — Sprint 5.';
|
||
|
||
COMMENT ON COLUMN user_weight_profiles.is_default IS
|
||
'TRUE = профиль используется по умолчанию для этого user_id. '
|
||
'Partial index user_weight_profiles_default_idx ускоряет lookup.';
|
||
|
||
COMMIT;
|