Circle V2 API Docs
    Preparing search index...

    Function buildBaseCtes

    • Shared CTE foundation for analytics queries.

      Produces three CTEs:

      • base_runs — filtered, deduplicated (latest run per patient × template), with metadata
      • run_scores — per-run pass/total/pct aggregates (excludes summary + not_applicable)
      • run_data — base_runs enriched with score columns

      Both the metrics and review-dashboard analytics layers build on top of run_data.

      Parameters

      Returns QueryCreatorWithCommonTableExpression<
          DrizzleToKyesely & {
              base_runs: Partial<Omit<unknown, never>> & {
                  audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                  audit_template_name: string;
                  case_id: CaseId;
                  client_id: PatientId;
                  client_name: string;
                  creator_name: string;
                  creator_user_id: UserId | null;
                  facility_id: FacilityId | null;
                  facility_name: string;
                  is_patient_case: boolean;
                  primary_provider_name: string;
                  primary_provider_user_id: UserId | null;
                  provider_name: string;
                  provider_user_id: UserId | null;
                  run_created_at: DbDate;
                  run_id: AuditRunId;
                  trigger_id: string | null;
              };
          } & {
              run_scores: {
                  pass_count: unknown;
                  run_id: string & { __brand: "AuditRunId" }
                  | null;
                  score_pct: unknown;
                  total_count: unknown;
              };
          },
          "run_data",
          (
              qb: QueryCreator<
                  DrizzleToKyesely & {
                      base_runs: Partial<Omit<unknown, never>> & {
                          audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                          audit_template_name: string;
                          case_id: CaseId;
                          client_id: PatientId;
                          client_name: string;
                          creator_name: string;
                          creator_user_id: UserId | null;
                          facility_id: FacilityId | null;
                          facility_name: string;
                          is_patient_case: boolean;
                          primary_provider_name: string;
                          primary_provider_user_id: UserId | null;
                          provider_name: string;
                          provider_user_id: UserId | null;
                          run_created_at: DbDate;
                          run_id: AuditRunId;
                          trigger_id: string | null;
                      };
                  } & {
                      run_scores: {
                          pass_count: unknown;
                          run_id: string & { __brand: "AuditRunId" }
                          | null;
                          score_pct: unknown;
                          total_count: unknown;
                      };
                  },
              >,
          ) => SelectQueryBuilder<
              DrizzleToKyesely & {
                  base_runs: Partial<Omit<unknown, never>> & {
                      audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                      audit_template_name: string;
                      case_id: CaseId;
                      client_id: PatientId;
                      client_name: string;
                      creator_name: string;
                      creator_user_id: UserId | null;
                      facility_id: FacilityId | null;
                      facility_name: string;
                      is_patient_case: boolean;
                      primary_provider_name: string;
                      primary_provider_user_id: UserId | null;
                      provider_name: string;
                      provider_user_id: UserId | null;
                      run_created_at: DbDate;
                      run_id: AuditRunId;
                      trigger_id: string | null;
                  };
              } & {
                  run_scores: {
                      pass_count: unknown;
                      run_id: string & { __brand: "AuditRunId" }
                      | null;
                      score_pct: unknown;
                      total_count: unknown;
                  };
              } & {
                  br: Partial<Omit<unknown, never>> & {
                      audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                      audit_template_name: string;
                      case_id: CaseId;
                      client_id: PatientId;
                      client_name: string;
                      creator_name: string;
                      creator_user_id: UserId | null;
                      facility_id: FacilityId | null;
                      facility_name: string;
                      is_patient_case: boolean;
                      primary_provider_name: string;
                      primary_provider_user_id: UserId | null;
                      provider_name: string;
                      provider_user_id: UserId | null;
                      run_created_at: DbDate;
                      run_id: AuditRunId;
                      trigger_id: string | null;
                  };
              } & {
                  rs: Nullable<
                      {
                          pass_count: unknown;
                          run_id: string & { __brand: "AuditRunId" }
                          | null;
                          score_pct: unknown;
                          total_count: unknown;
                      },
                  >;
              },
              "rs"
              | "br",
              {
                  audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                  audit_template_name: string;
                  case_id: CaseId;
                  client_id: PatientId;
                  client_name: string;
                  creator_name: string;
                  creator_user_id: UserId | null;
                  facility_id: FacilityId | null;
                  facility_name: string;
                  is_patient_case: boolean;
                  primary_provider_name: string;
                  primary_provider_user_id: UserId | null;
                  provider_name: string;
                  provider_user_id: UserId | null;
                  run_created_at: DbDate;
                  run_id: AuditRunId;
                  trigger_id: string | null;
              } & { pass_count: number; score_pct: number; total_count: number },
          >,
      >

    Settings

    Member Visibility