/*
 * Copyright (C) 2006 SIPfoundry Inc.
 * Licensed by SIPfoundry under the LGPL license.
 * 
 * Copyright (C) 2006 Pingtel Corp.
 * Licensed to SIPfoundry under a Contributor Agreement.
 */

/* 
 * Schema for Call Resolver data, including Call State Events (CSE) and
 * Call Detail Records (CDR).
 */

---------------------------------- CSE Tables ----------------------------------

/*
 * The call_state_events holds events that describe the lifecycle of a call:
 * call request, call setup, call end or call failure.
 *
 * Values for event_type are
 *   'R' - Call Request, generated by the proxy server when an INVITE request is
 *         received that does not have a tag parameter on the TO field value.
 *   'S' - Call Setup, generated when an ACK is received.
 *   'E' - Call End, generated by any BYE request.
 *   'T' - Call Transfer, generated by an REFER request.
 *   'F' - Call Failure, generated by any 5xx or 6xx response to an INVITE
 *         request, or any 4xx response to an INVITE - except
 *                    
 *           401 Authentication Required
 *           407 Proxy Authentication Required
 *           408 Request timeout
 */
create table call_state_events (
   id              serial8 not null primary key,
   observer        text not null,       /* DNS name of the system that observed the event */
   event_seq       int8 not null,       /* Sequence number of this event at the observer */
   event_time      timestamp not null,  /* Observer (UTC) time when this event occurred */
   event_type      char(1) not null,
   cseq            int4 not null,       /* Value from CSEQ header */ 
   call_id         text not null,       /* Value from SIP Call-Id header */
   from_tag        text,                /* Value from tag attribute of the SIP From header */
   to_tag          text,                /* Value from tag attribute of the SIP To header */
   from_url        text not null,       /* Full From header field value */
   to_url          text not null,       /* Full To header field value */
   contact         text,    /* Contact header field value. For Call Request this is the
                               calling party, for Call Setup this is the called party. */
   refer_to        text,                /* Refer-To header field value */
   referred_by     text,                /* Referred-By header field value */
   failure_status  int2,    /* For Call Failure events, holds 4xx, 5xx, or 6xx status code */
   failure_reason  text,    /* For Call Failure events, holds error text */
   request_uri     text     /* URI from the request header */
);