Upgrade Guide CX5.1.0 to Surveys/Campaigns

k exec -it -n ef-external ef-postgresql-0 -- psql -U postgres

CREATE DATABASE "ciscoConnector";

\connect "ciscoConnector"

CREATE TABLE call_legs (
    id                  UUID            NOT NULL UNIQUE,
    call_id             VARCHAR(255)    NOT NULL,
    start_reason        VARCHAR(50)     NOT NULL,
    end_reason          VARCHAR(50),
    global_call_id      VARCHAR(255)    NOT NULL,
    jtapi_id            VARCHAR(255)    NOT NULL,
    connection_id       VARCHAR(255),
    agent_extension     VARCHAR(255),
    agent_id            VARCHAR(255),
    agent_name          VARCHAR(255),
    duration            BIGINT          NOT NULL DEFAULT 0,
    conversation_type   VARCHAR(50),
    call_type           VARCHAR(50),
    start_time          TIMESTAMPTZ     NOT NULL,
    end_time            TIMESTAMPTZ,
    wrap_ups            VARCHAR(255),
    created_at          TIMESTAMPTZ     NOT NULL,
    sync_with_cx        BOOLEAN         NOT NULL DEFAULT false,
    sync_with_cisco     BOOLEAN         NOT NULL DEFAULT false,
    service_identifier  VARCHAR(255),
    customer_identifier VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE jtapi_events (
    id                        BIGSERIAL       PRIMARY KEY,
    event_type                VARCHAR(50)     NOT NULL,
    created_at                TIMESTAMPTZ     NOT NULL,
    global_call_id            VARCHAR(255)    NOT NULL,
    jtapi_id                  VARCHAR(255)    NOT NULL,
    connection_id             VARCHAR(255),
    previous_global_call_Id   VARCHAR(255),
    calling_terminal          VARCHAR(255),
    called_terminal           VARCHAR(255),
    calling_extension         VARCHAR(255),
    called_extension          VARCHAR(255),
    event_status              VARCHAR(50)     NOT NULL DEFAULT 'PENDING',
    call_direction            VARCHAR(50)     NOT NULL
);

CREATE TABLE call_leg_history (
    history_id  BIGSERIAL       PRIMARY KEY,
    event_type  VARCHAR(50)     NOT NULL,
    timestamp   TIMESTAMPTZ     NOT NULL,
    call_leg_id UUID            NOT NULL,
    CONSTRAINT fk_call_leg_history_call_leg_id FOREIGN KEY (call_leg_id) REFERENCES call_legs(id)
);

-- 1. Ensure the group role exists
DO $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_users') THEN
      CREATE ROLE app_users;
   END IF;
END
$$;

-- 2. Grant USAGE on the schema.
--    It allows the role to "see" objects inside the schema.
GRANT USAGE ON SCHEMA public TO app_users;

-- 3. Grant CREATE on the schema.
--    Required for Hibernate to add new columns or alter tables on future startups.
GRANT CREATE ON SCHEMA public TO app_users;

-- 4. Grant table permissions for the app_users role.
--    This covers all tables that CURRENTLY exist in the schema.
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO app_users;

-- 5. IMPORTANT: Set default permissions for FUTURE tables.
--    This ensures that if you create new tables later, app_users automatically gets access.
--    NOTE: This only applies to tables created by the user running this command (postgres).
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO app_users;

-- 6. Grant permissions on sequences for future objects.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_users;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_users;

exit

cd pre-deployment/service-based-accounts

chmod +x service-accounts.sh

./service-accounts.sh