Creates database table and model to aggregate AI events count

What does this MR do and why?

Creates database table and model to aggregate AI events count.

The database table is supposed to be used as data source of code suggestions acceptance rate metric. To achieve this we could use queries like:

WITH project_namespaces AS (
    SELECT id 
    FROM namespaces 
    WHERE type = 'Project' AND traversal_ids @> '{9970}'
    OFFSET 0
)
SELECT
    SUM(CASE WHEN event = 3 THEN total_occurrences ELSE 0 END)::float /
    NULLIF(SUM(CASE WHEN event = 2 THEN total_occurrences ELSE 0 END), 0) * 100
    AS acceptance_rate
FROM ai_events_counts
WHERE namespace_id IN (SELECT id FROM project_namespaces)
  AND event IN (2,3)
  AND events_date >= DATE '2025-07-01'
  AND events_date < DATE '2025-07-30';

Query plan - https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43695/commands/133525

Part of the work to complete https://gitlab.com/gitlab-org/gitlab/-/issues/558280 MRs:

1. Create database table and model - this MR
2. Aggregate values on the table using background job - TBD
3. Add GraphQL endpoint - TBD

References

https://gitlab.com/gitlab-org/gitlab/-/issues/558280

How to set up and validate locally

Check proposed query plans using database lab:

1. Create table indexes and records using database lab exec command

exec

CREATE TABLE ai_events_counts (
    events_date date NOT NULL,
    namespace_id bigint DEFAULT 0 NOT NULL,
    user_id bigint NOT NULL,
    organization_id bigint NOT NULL,
    event smallint NOT NULL,
    total_occurrences integer DEFAULT 0 NOT NULL
)
PARTITION BY RANGE (events_date);

ALTER TABLE ONLY ai_events_counts
    ADD CONSTRAINT ai_events_counts_pkey PRIMARY KEY (namespace_id, events_date, user_id, event);

CREATE INDEX idx_shown_and_accepted_events_on_events_date ON ONLY ai_events_counts USING btree (events_date, namespace_id, event) INCLUDE (total_occurrences) WHERE (event = ANY (ARRAY[2, 3]));

CREATE INDEX index_ai_events_counts_on_organization_id ON ONLY ai_events_counts USING btree (organization_id);

CREATE INDEX index_ai_events_counts_on_user_id ON ONLY ai_events_counts USING btree (user_id);

ALTER TABLE ai_events_counts
    ADD CONSTRAINT fk_rails_99a3f4a8f1 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;


-- July 2025 partition
CREATE TABLE ai_events_counts_2025_07 PARTITION OF ai_events_counts
FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

-- August 2025 partition  
CREATE TABLE ai_events_counts_2025_08 PARTITION OF ai_events_counts
FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');

-- September 2025 partition (current month)
CREATE TABLE ai_events_counts_2025_09 PARTITION OF ai_events_counts
FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');


INSERT INTO ai_events_counts (
    events_date,
    namespace_id, 
    user_id,
    organization_id,
    event,
    total_occurrences
)
SELECT
    date_trunc('day', timestamp)::date AS events_date,
    COALESCE(namespace_id, 0) AS namespace_id,  -- Set NULL namespace_id to 0
    user_id,
    1 AS organization_id,  -- Default organization_id as requested
    event,
    count(*) AS total_occurrences
FROM ai_usage_events
WHERE timestamp >= date_trunc('month', CURRENT_DATE - interval '2 months')  -- Last 3 months
GROUP BY
    events_date,
    COALESCE(namespace_id, 0),  -- Group by the coalesced value too
    user_id,
    event
ORDER BY events_date, namespace_id, user_id, event;

2. Execute the query from the MR description - https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43695/commands/133525

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.