trip_schema.sql - master - Andromeda141/Adventure-service

trip_schema.sql
226 строк10 KB
andrewvnukov
feat: access control, notification bell, participants tab, direct invite
08 июн 2026, 10:07
08 июн 2026, 10:075b8fed8
-- ==========================================
-- Database Schema v3.0 (canonical)
-- Supersedes all prior migrations:
--   - OAuth support: email, provider, provider_id columns; nullable password_hash
--   - activities: nullable start_at / end_at (polls create activities without times)
--   - activity_polls: required_percentage column removed (threshold fixed at 51% in backend)
-- ==========================================

-- 0. Enums
CREATE TYPE trip_status_enum   AS ENUM ('drafting', 'planning', 'ready', 'active', 'completed', 'cancelled', 'archived');
CREATE TYPE activity_type_enum AS ENUM ('sightseeing', 'restaurant', 'hotel', 'transport', 'shopping', 'other');
CREATE TYPE poll_status_enum   AS ENUM ('active', 'passed', 'rejected', 'expired');
CREATE TYPE poll_choice_enum   AS ENUM ('for', 'against');
CREATE TYPE user_gender_enum   AS ENUM ('male', 'female', 'other');
CREATE TYPE member_role_enum   AS ENUM ('owner', 'admin', 'member');

-- 1. Users (password_hash nullable for OAuth users)
CREATE TABLE users (
    id            UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
    login         VARCHAR(50)      UNIQUE NOT NULL,
    password_hash VARCHAR(255),
    email         VARCHAR(255)     UNIQUE,
    provider      VARCHAR(20)      NOT NULL DEFAULT 'local',
    provider_id   VARCHAR(255),
    name          VARCHAR(100)     NOT NULL,
    surname       VARCHAR(100)     NOT NULL,
    gender        user_gender_enum NOT NULL,
    age           INTEGER          NOT NULL CHECK (age >= 13),
    photo_url     TEXT,
    created_at    TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ      NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_login ON users(login);
CREATE UNIQUE INDEX idx_users_provider_id ON users(provider, provider_id) WHERE provider_id IS NOT NULL;

-- 2. Trips
CREATE TABLE trips (
    id            UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
    title         VARCHAR(255)     NOT NULL,
    description   TEXT,
    start_date    DATE             NOT NULL,
    end_date      DATE,
    base_currency CHAR(3)          NOT NULL DEFAULT 'RUB' CHECK (base_currency ~ '^[A-Z]{3}$'),
    status        trip_status_enum NOT NULL DEFAULT 'drafting',
    created_at    TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_trip_dates CHECK (end_date >= start_date)
);
CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_trips_dates  ON trips(start_date, end_date);

-- 2.1. Trip Members
CREATE TABLE trip_members (
    trip_id   UUID             NOT NULL,
    user_id   UUID             NOT NULL,
    role      member_role_enum NOT NULL DEFAULT 'member',
    joined_at TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    PRIMARY KEY (trip_id, user_id),
    CONSTRAINT fk_trip_members_trip FOREIGN KEY (trip_id) REFERENCES trips(id)  ON DELETE CASCADE,
    CONSTRAINT fk_trip_members_user FOREIGN KEY (user_id) REFERENCES users(id)  ON DELETE CASCADE
);
CREATE INDEX idx_trip_members_user_id ON trip_members(user_id);
CREATE INDEX idx_trip_members_owner   ON trip_members(user_id) WHERE role = 'owner';

-- 3. Activities
-- start_at / end_at nullable: polls create pending activities before times are confirmed
-- is_pending = TRUE means the activity was created by a poll and not yet confirmed:
--   passed  → is_pending = FALSE (activity visible in trip)
--   rejected/expired → row is deleted
CREATE TABLE activities (
    id                    UUID               PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id               UUID               NOT NULL,
    title                 VARCHAR(255)       NOT NULL,
    type                  activity_type_enum NOT NULL,
    location_name         VARCHAR(255),
    latitude              DECIMAL(9, 6),
    longitude             DECIMAL(9, 6),
    address               TEXT,
    start_at              TIMESTAMPTZ,
    end_at                TIMESTAMPTZ,
    cost                  DECIMAL(10, 2),
    currency              CHAR(3)            CHECK (currency ~ '^[A-Z]{3}$'),
    cost_in_base_currency DECIMAL(10, 2),
    notes                 TEXT,
    day_number            INTEGER            NOT NULL DEFAULT 1,
    is_pending            BOOLEAN            NOT NULL DEFAULT FALSE,
    created_by            UUID               REFERENCES users(id) ON DELETE SET NULL,
    created_at            TIMESTAMPTZ        NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ        NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_activities_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT chk_activity_time  CHECK (end_at IS NULL OR start_at IS NULL OR end_at >= start_at)
);
CREATE INDEX idx_activities_trip_id    ON activities(trip_id);
CREATE INDEX idx_activities_time       ON activities(trip_id, start_at) WHERE start_at IS NOT NULL;
CREATE INDEX idx_activities_is_pending ON activities(trip_id, is_pending);
CREATE INDEX idx_activities_created_by ON activities(created_by);

-- 3.1. Activity Assignees
CREATE TABLE activity_assignees (
    activity_id UUID NOT NULL,
    user_id     UUID NOT NULL,
    PRIMARY KEY (activity_id, user_id),
    CONSTRAINT fk_assignees_activity FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignees_user     FOREIGN KEY (user_id)     REFERENCES users(id)      ON DELETE CASCADE
);
CREATE INDEX idx_assignees_user ON activity_assignees(user_id);

-- 4. Invite Links
CREATE TABLE invite_links (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id    UUID        NOT NULL,
    code       VARCHAR(12) UNIQUE NOT NULL,
    created_by UUID        NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    is_active  BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_invite_trip FOREIGN KEY (trip_id)    REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_invite_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_invite_code ON invite_links(code);
CREATE INDEX idx_invite_trip ON invite_links(trip_id);

-- 5. Activity Polls
-- required_percentage removed; threshold is fixed at 51% in backend logic
CREATE TABLE activity_polls (
    id                   UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id              UUID             NOT NULL,
    proposed_activity_id UUID             NOT NULL,
    title                VARCHAR(255)     NOT NULL,
    description          TEXT,
    created_at           TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    expires_at           TIMESTAMPTZ      NOT NULL,
    status               poll_status_enum NOT NULL DEFAULT 'active',
    created_by           UUID             REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_polls_trip     FOREIGN KEY (trip_id)              REFERENCES trips(id)      ON DELETE CASCADE,
    CONSTRAINT fk_polls_activity FOREIGN KEY (proposed_activity_id) REFERENCES activities(id) ON DELETE CASCADE
);
CREATE INDEX idx_activity_polls_trip_id    ON activity_polls(trip_id);
CREATE INDEX idx_activity_polls_created_by ON activity_polls(created_by);
CREATE INDEX idx_activity_polls_status     ON activity_polls(status);

-- 5.1. Poll Votes
CREATE TABLE poll_votes (
    poll_id  UUID             NOT NULL,
    user_id  UUID             NOT NULL,
    choice   poll_choice_enum NOT NULL,
    voted_at TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    PRIMARY KEY (poll_id, user_id),
    CONSTRAINT fk_votes_poll FOREIGN KEY (poll_id) REFERENCES activity_polls(id) ON DELETE CASCADE,
    CONSTRAINT fk_votes_user FOREIGN KEY (user_id) REFERENCES users(id)          ON DELETE CASCADE
);
CREATE INDEX idx_poll_votes_user ON poll_votes(user_id);

-- ==========================================
-- VIEWS
-- ==========================================

CREATE OR REPLACE VIEW v_poll_results AS
SELECT
    p.id      AS poll_id,
    p.trip_id,
    p.status,
    COUNT(v.user_id)                                                    AS voter_count,
    COALESCE(SUM(CASE WHEN v.choice = 'for'     THEN 1 ELSE 0 END), 0) AS votes_for,
    COALESCE(SUM(CASE WHEN v.choice = 'against' THEN 1 ELSE 0 END), 0) AS votes_against
FROM activity_polls p
LEFT JOIN poll_votes v ON p.id = v.poll_id
GROUP BY p.id, p.trip_id, p.status;

-- ==========================================
-- TRIGGERS
-- ==========================================

CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at_users      BEFORE UPDATE ON users      FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at_trips      BEFORE UPDATE ON trips      FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at_activities BEFORE UPDATE ON activities FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- Keep at most 10 finished polls per trip (auto-pruning)
CREATE OR REPLACE FUNCTION fn_limit_poll_history()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = OLD.status OR NEW.status = 'active' THEN
        RETURN NEW;
    END IF;

    DELETE FROM activity_polls
    WHERE id IN (
        SELECT id FROM activity_polls
        WHERE trip_id = NEW.trip_id
          AND status != 'active'
        ORDER BY expires_at DESC
        OFFSET 10
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_limit_poll_history
AFTER UPDATE OF status ON activity_polls
FOR EACH ROW
EXECUTE FUNCTION fn_limit_poll_history();

-- 6. Notifications (in-app bell icon)
CREATE TABLE notifications (
    id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    trip_id    UUID         REFERENCES trips(id) ON DELETE CASCADE,
    type       VARCHAR(50)  NOT NULL,
    title      VARCHAR(255) NOT NULL,
    message    TEXT,
    link       TEXT,
    read       BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_notifications_user_unread
    ON notifications(user_id, created_at DESC)
    WHERE read = FALSE;