rcx sql data warehouse model 1.16.0

Tables

rcxbid_db.account_members
rcxbid_db.accounts
rcxbid_db.daily_aggregates
rcxbid_db.deleted_records
rcxbid_db.enums
rcxbid_db.halfyearly_aggregates
rcxbid_db.lifetime_aggregates
rcxbid_db.limits
rcxbid_db.location_overrides
rcxbid_db.locations
rcxbid_db.loyalty_accrual_items
rcxbid_db.loyalty_activities
rcxbid_db.loyalty_activity_best_offers
rcxbid_db.loyalty_activity_line_items
rcxbid_db.loyalty_activity_results_errors
rcxbid_db.loyalty_activity_tender_items
rcxbid_db.loyalty_cards
rcxbid_db.loyalty_programs
rcxbid_db.loyalty_redemption_items
rcxbid_db.member_badges
rcxbid_db.member_loyalty_ids
rcxbid_db.member_notes
rcxbid_db.member_offer_usage_histories
rcxbid_db.member_offers
rcxbid_db.member_packaged_redemptions
rcxbid_db.member_preferences
rcxbid_db.member_purses
rcxbid_db.member_referrals
rcxbid_db.member_reward_usage_histories
rcxbid_db.member_rewards
rcxbid_db.member_segments
rcxbid_db.member_streak_actions
rcxbid_db.member_streaks
rcxbid_db.member_tier_histories
rcxbid_db.member_tiers
rcxbid_db.member_transient_activities
rcxbid_db.members
rcxbid_db.members_pii
rcxbid_db.members_streak
rcxbid_db.members_streak_event_logs
rcxbid_db.members_streak_goals
rcxbid_db.merge_histories
rcxbid_db.merged_members
rcxbid_db.monthly_aggregates
rcxbid_db.namedlists
rcxbid_db.namedlists_data
rcxbid_db.orgs
rcxbid_db.partners
rcxbid_db.product_types
rcxbid_db.products
rcxbid_db.program_rule_actions
rcxbid_db.program_rule_availability
rcxbid_db.program_rule_conditions
rcxbid_db.program_rule_control_groups
rcxbid_db.program_rule_folders
rcxbid_db.program_rule_limits
rcxbid_db.program_rule_locations
rcxbid_db.program_rule_mandatory_segments
rcxbid_db.program_rule_member_statuses
rcxbid_db.program_rule_products
rcxbid_db.program_rule_segments
rcxbid_db.program_rule_tier_levels
rcxbid_db.program_rules
rcxbid_db.promo_code_defs
rcxbid_db.promo_codes
rcxbid_db.purse_policies
rcxbid_db.quarterly_aggregates
rcxbid_db.reward_policies
rcxbid_db.reward_policy_availability
rcxbid_db.reward_policy_control_groups
rcxbid_db.reward_policy_locations
rcxbid_db.reward_policy_mandatory_segments
rcxbid_db.reward_policy_member_statuses
rcxbid_db.reward_policy_products
rcxbid_db.reward_policy_segments
rcxbid_db.reward_policy_tier_levels
rcxbid_db.reward_upc_mapping
rcxbid_db.rule_match
rcxbid_db.rule_match_allocations
rcxbid_db.rule_match_lines
rcxbid_db.segments
rcxbid_db.streak_goal_policies
rcxbid_db.streak_policies
rcxbid_db.streak_policy_optin_segments
rcxbid_db.tier_policies
rcxbid_db.tier_policy_levels
rcxbid_db.users
rcxbid_db.weekly_aggregates
rcxbid_db.yearly_aggregates

Schema data.rcxbid_db

Table account_members

Idx	Field Name	Data Type
*	account_id	varchar(50) encode raw
*	member_id	varchar(50) encode raw
	program_id	varchar(50) encode zstd
*	org_id	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_account_members	ON account_id, member_id
	account_members_dist_key	ON member_id
	account_members_sort_key	ON updated_utc_ts, account_id, member_id
Foreign Keys
	fk_account_members_accounts_account_id	(account_id) → accounts(id)
	fk_account_members_loyalty_programs_program_id	(program_id) → loyalty_programs(id)
	fk_account_members_orgs_org_id	(org_id) → orgs(id)
	fk_account_members_users_created_by	(created_by) → users(id)
	fk_account_members_users_updated_by	(updated_by) → users(id)
	fk_account_members_members	(member_id) → members(id)
	fk_account_members_members_pii_member_id	(member_id) → members_pii(id)

Table accounts

Idx	Field Name	Data Type
*	id	varchar(50) encode raw
	active_member_id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_accounts	ON id
	accounts_dist_key	ON active_member_id
	accounts_sort_key	ON updated_utc_ts, id, active_member_id
Foreign Keys
	fk_accounts_orgs	(org_id) → orgs(id)
	fk_accounts_members_active_member_id	(active_member_id) → members(id)
	fk_accounts_users_created_by	(created_by) → users(id)
	fk_accounts_users_updated_by	(updated_by) → users(id)
	fk_accounts_members_pii_member_id	(active_member_id) → members_pii(id)
Referring Foreign Keys
	fk_account_members_accounts_account_id	(id) ← account_members(account_id)

Table daily_aggregates

The total activity points accumulated throughout the day are saved in a collection known as daily aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	day	timestamp ENCODE zstd	Contains the timestamp value of the current day
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	day_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the day
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_daily_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_daily_aggregates_users_created_by	(created_by) → users(id)
	fk_daily_aggregates_users_updated_by	(updated_by) → users(id)
	fk_daily_aggregates_org	(org_id) → orgs(id)
	fk_daily_aggregates_members	(member_id) → members(id)
Options
DISTSTYLE KEY

Table deleted_records

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	deleted_id	varchar(50) encode raw
	collection_name	varchar(50) encode zstd
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_deleted_records	ON id
	deleted_records_dist_key	ON deleted_id
	deleted_records_sort_key	ON updated_utc_ts, deleted_id

Table enums

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	enum_type	varchar(50) encode zstd	Type of the enum - for example ActivityType
*	enum_value	varchar(250) encode lzo	Enum value represnting the possible values of that type - e.g. Accrual, Redemption, etc. for ActivityType type.
*	value_type	varchar(50) encode zstd	required name of the loyalty id - e.g. SVC, Card, Phone, etc. - validated by Enums with type=LoyaltyID.
*	lang	varchar(2) encode zstd	language in which the enum is aavailable
	enum_description	varchar(1000) encode lzo	What the enum functinaliy
*	label	varchar(100) encode zstd	option in dropdown
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	enums_data	varchar(65535) encode zstd
	status	varchar(50) encode zstd	By Status we are restricting Inactive Enums not to show in dropdown fields.
	context	varchar(50) encode zstd	Context field is designed to provide filtering Enums based on contexts.
	par_val	varchar(50) encode zstd	Parent value and Parent Type fields are added to to determine sub reason codes based on selected reasonCode.
	par_type	varchar(50) encode zstd	Parent value and Parent Type fields are added to to determine sub reason codes based on selected reasonCode.
Indexes
	pk_enums	ON id
Foreign Keys
	fk_enums_orgs	(org_id) → orgs(id)

Table halfyearly_aggregates

The total activity points accumulated throughout the respective halfyear of the current year are saved in a collection known as halfyearly aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	half_year	integer ENCODE zstd	Contains the value of the current half of the year
	year	integer ENCODE zstd	Contains the value of the current year number
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	halfyear_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the half year
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_halfyearly_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_halfyearly_aggregates_orgs	(org_id) → orgs(id)
	fk_halfyearly_aggregates_members	(member_id) → members(id)
	fk_halfyearly_aggregates_users_created_by	(created_by) → users(id)
	fk_halfyearly_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table lifetime_aggregates

The total activity points accumulated throughout the lifetime are saved in a collection known as lifetime aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	lifetime_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the lifetime
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_lifetime_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_lifetime_aggregates_orgs	(org_id) → orgs(id)
	fk_lifetime_aggregates_members	(member_id) → members(id)
	fk_lifetime_aggregates_users_created_by	(created_by) → users(id)
	fk_lifetime_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table limits

Limits are constraints attached to MCP users/roles related to performing certain tasks. For example, appeasing points to members.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	Unique ID
*	org_id	varchar(50) encode zstd	Unique ID of the Organization
*	principal_type	varchar(50) encode zstd	Determined whether the limit is applicable for a user or certain role
*	principal_id	varchar(50) encode zstd	_id of user or role
*	limit_spec_id	varchar(50) encode zstd	_id of limits specification
*	limit_spec_type	varchar(20) ENCODE zstd	Entity for which the limit is applicable for. Example, Purse/Coupon/Reward
	limit_spec_target	varchar(50) encode zstd	_id of the entity in RCX
*	limit_spec_name	varchar(100) encode zstd	Name of the specification. Example, maximum points added to a purse - MaxAddPoints
*	limit_spec_value	varchar(50) encode zstd	Value associated to the limit. Example, 10000
*	limit_spec_scope	varchar(20) encode zstd	Action/Entity/Time period the limit is applicable for. Example, Action/Rule/Activity/Day/Week/Month
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint encode zstd
*	limit_type	varchar(50) encode zstd DEFAULT MaxCountLimit	It defines the type of limit
	limit_spec_target_set	varchar(50) encode zstd
Indexes
	pk_limits_id	ON id
	sort_limits	ON updated_utc_ts, id
Foreign Keys
	fk_limits_users	(principal_id) → users(id)

Table location_overrides

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	location_id	varchar(50) encode zstd	It is the id of the location
*	target_id	varchar(50) encode raw	To which object we are applying location overrides
*	target_type	varchar(50) encode zstd	To which the targetId is pointing to (RewardPolicy/Rule)
	optin_flag	boolean encode raw	For location opt in:true, opt out:false
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	price	decimal(18,6) ENCODE RAW	If policy discount is of mix and match type, customized discount will be saved against this field.
	primary_product_disc_value	decimal(18,6) encode raw	If the discount given for primary product group is customized, it will be saved against this field.
	secondary_product_disc_value	decimal(18,6) encode raw	If the discount given for secondary product group is customized, it will be saved against this field.
	tertiary_product_disc_value	decimal(18,6) encode raw	If the discount given for tertiary product group is customized, it will be saved against this field.
Indexes
	pk_location_overrides	ON id
	location_overrides_sort_key	ON target_id
Foreign Keys
	fk_location_overrides_locations	(location_id) → locations(id)
	fk_location_overrides_orgs	(org_id) → orgs(id)
	fk_location_overrides_users_created_by	(created_by) → users(id)
	fk_location_overrides_users_updated_by	(updated_by) → users(id)

Table locations

Stores a list of physical or virtual locations at which business is transacted.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	URL to a picture resource for this product
*	location_name	varchar(50) encode zstd	Location name/or street address.
	location_description	varchar(1000) encode lzo	Detailed description of location.
	location_number	varchar(50) encode zstd	Store number.
	location_city	varchar(50) encode zstd	Location City name.
	location_state	varchar(50) encode zstd	Location State code/name (depending on country).
	location_country	varchar(50) encode zstd	Location Country name.
	location_zip_code	varchar(50) encode zstd	Location zip code or postal code.
	location_time_zone	varchar(50) encode zstd	Timezone string, in the long textual format - e.g. 'America/Los Angeles'.
	longitude	decimal(18,6) encode raw	Geographic coordinate that specifies longitude of a location
	latitude	decimal(18,6) encode raw	Geographic coordinate that specifies latitude of a location
	status	varchar(50) encode zstd	Status of the location.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_locations	ON id
	locations_sort_key	ON id
Foreign Keys
	fk_location_org	(org_id) → orgs(id)
	fk_locations_users_created_by	(created_by) → users(id)
	fk_locations_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_location_overrides_locations	(id) ← location_overrides(location_id)
	fk_loyalty_activities_locations	(id) ← loyalty_activities(location_id)
	fk_members_locations	(id) ← members(enrollment_location_id)
	fk_namedlists_data_locations	(id) ← namedlists_data(model_id)
	fk_program_rule_locations_locations	(id) ← program_rule_locations(location_id)
	fk_reward_policies_locations_locations	(id) ← reward_policy_locations(location_id)

Table loyalty_accrual_items

Stores accrual items generated as a result of an activity. An accrual item is generated separately for each action within a rule that results in accruals.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	_id of the Program that this accrual item was generated from.
	loyalty_activity_id	varchar(50) encode raw	parent activity
*	member_id	varchar(50) encode raw	The _id field from Member. Activities can be submitted directly without a Loyalty ID if we know this value.
	rule_id	varchar(50) encode raw	the _id of the Rule which awarded the accrual item.
	member_purse_id	varchar(50) encode raw	_id of the Purse which has the accrual item.
	purse_policy_id	varchar(50) encode raw
	accrual_date	timestamptz encode raw	Date/time the accrual happened.
	escrow_date	timestamptz encode zstd	Date/time the accrual will escrow.
	expiration_date	timestamptz encode zstd	Date/time the accrual will expire.
	accrued_pts	bigint ENCODE zstd	Points accrued for this accrual.
	available_pts	bigint ENCODE zstd	Points available to burn on this accrual. That means they have escrowed and are not expired.
	redeemed_pts	bigint ENCODE zstd	Points redeemed from this accrual.
	is_escrowed	boolean encode raw	Whether the accrual has escrowed, meaning that its escrow period has passed and its points are available for redemption. Domain: [t/f]
	is_expired	boolean encode raw	Whether the points in this accrual have expired. Domain: [t/f]
	is_redeemed	boolean encode raw	Whether the points in this accrual have been fully redeemed.
	accounting_type	varchar(250) encode zstd	Indicates the bucket into which the accrued points will fall under.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
	expiry_activity_id	varchar(50) encode zstd
Indexes
	loyalty_accrual_items_dist_key	ON member_id
	pk_loyalty_accrual_items	ON id
	loyalty_accrual_items_sort_key	ON updated_utc_ts, member_id, purse_policy_id, accrual_date, loyalty_activity_id, member_purse_id
Foreign Keys
	fk_loyalty_accrual_items_loyalty_activity	(loyalty_activity_id) → loyalty_activities(id)
	fk_loyalty_accrual_items_members	(member_id) → members(id)
	fk_loyalty_accrual_items_program_rules	(rule_id) → program_rules(id)
	fk_loyalty_accrual_items_member_purses	(member_purse_id) → member_purses(id)
	fk_loyalty_accrual_items_purse_policies	(purse_policy_id) → purse_policies(id)
	fk_loyalty_accrual_items_users_created_by	(created_by) → users(id)
	fk_loyalty_accrual_items_users_updated_by	(updated_by) → users(id)
	fk_loyalty_accrual_items_orgs	(org_id) → orgs(id)
	fk_loyalty_accrual_items_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_loyalty_redemption_items	(id) ← loyalty_redemption_items(loyalty_accrual_item_id)

Table loyalty_activities

Stores activity history for any loyalty activities submitted for a member.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	The unique id of the Activity.
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	Program ObjectId
	target_program_id	varchar(50) encode zstd
	member_id	varchar(50) encode raw	The _id field from Member. Activities can be submitted directly without a Loyalty ID if we know this value.
	orgnl_member_id	varchar(50) encode zstd	Member id by whom activity was originally inititated. This is needed for pooling - it is possible that a Loyalty ID is designated to accrue points to a pooled member (e.g. a Charity), in which case originalMemberID will contain the original member who performed the activity, even though the memberID will refect the pooled member.
	loyalty_id	varchar(100) encode raw	A valid loyalty id which can be looked up to find out which Member is submitting the Activity.
*	activity_type	varchar(50) encode zstd	Activity types govern the type of transaction that resulted in a change in loyalty rewards. For example, it could be a purchase, or a redemption, or some other non-purchase behavior. The domain is configurable, with some default out-of-box values provided. Domain: [Accrual/Redemption/Cancellation/Requalify/Reprice Ticket]
*	activity_date	timestamptz encode raw	Date/time when the activity occured.
	activity_status	varchar(50) encode zstd	Processing status for the activity. Domain: [Processed/Error]
	coupon_code	varchar(65535) ENCODE zstd	The coupon code, if a coupon was specified for the entire activity.
	currency_code	varchar(100) encode zstd
	amount	decimal(18,6) ENCODE RAW	The amount field is flexible and will be utilized by the rules. In many cases this ist he total spend on a transaction, although it is possible that this contains loyalty currency to be awarded directly ( without $ -> point conversion).
*	src_channel_type	varchar(50) encode zstd	What channel was the activity submitted on. Configurable domain. Default domain: [Web/Mobile/Staff/Social/POS]
	src_channel_id	varchar(50) encode zstd	Depends on the channel - for POS, this will be the store number, which can be mached against locations. For other channels, it may be a different value.
	location_id	varchar(50) encode raw	This will contain the location id if it was identified through either src_channel_id, or directly specified by the client submitting an Activity.
	partner_id	varchar(50) encode raw
	partner_code	varchar(100) encode zstd	Unique partner code that can be used in Activity to validate partner transactions and route them to the correct rules.
	orgnl_txn_id	varchar(50) encode zstd	Unique transaction identifier for a transaction using which refunds can be performed.
	extrnl_txn_id	varchar(65535) encode zstd	external Transaction Id. This is used to allow submitting transactions with external ids, possibly asynchronously, and then using that external ID to query processing status.
	referral_code	varchar(100) encode zstd	Referral Code. This should correspond to the code field in the Refferal for a Member
	referred_member	boolean encode raw	Referred Member. Whether this activity is posted for a referred member. (e.g. referral bonus)
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	is_wrong_country	boolean encode raw	Indicates the mis-match between the country code comes through the activity does not match with the member's affiliated country
	base_points	bigint ENCODE zstd	Indicates the standard points awarded after processing the activity
	bonus_points	bigint ENCODE zstd	Indicates the additional points awarded after processing the activity excluding base points
	daily_txn_cap_reached	boolean encode raw	Indicates if the member has crossed the daily transactions limit that award loyalty incentives.
	txn_qual_purchase_amt	decimal(18,6) ENCODE RAW	Indicates the transaction value that is considered while calculating the base points that can be awarded.
	point_snapshot	bigint ENCODE zstd
	reason_code	varchar(250) encode zstd	Whenever customer represetative does adjustment through member portal they need to specify reson_code
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
	sub_reason_code	varchar(50) ENCODE zstd	For selected reasonCodse we may have to show SubReasonCode on MCP. (ReasonCode and SubReasonCode have parent child relation )
Indexes
	pk_loyalty_actvities	ON id
	loyalty_activities_dist_key	ON member_id
	loyalty_activities_sort_key	ON updated_utc_ts, id, activity_date, member_id, loyalty_id, location_id, partner_id
Foreign Keys
	fk_loyalty_activities_member_loyalty_ids	(loyalty_id) → member_loyalty_ids
	fk_loyalty_activities_member_member_id	(member_id) → members(id)
	fk_loyalty_activities_members_orgnl_member_id	(orgnl_member_id) → members(id)
	fk_loyalty_activities_locations	(location_id) → locations(id)
	fk_loyalty_activities_orgs	(org_id) → orgs(id)
	fk_loyalty_activities_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_loyalty_activities_self	(orgnl_txn_id) → loyalty_activities(id)
	fk_loyalty_activities_users_created_by	(created_by) → users(id)
	fk_loyalty_activities_users_updated_by	(updated_by) → users(id)
	fk_loyalty_activities_partners	(partner_id) → partners(id)
Referring Foreign Keys
	fk_loyalty_accrual_items_loyalty_activity	(id) ← loyalty_accrual_items(loyalty_activity_id)
	fk_loyalty_activities_self	(id) ← loyalty_activities(orgnl_txn_id)
	fk_loyalty_activity_best_offers_loyalty_activity	(id) ← loyalty_activity_best_offers(loyalty_activity_id)
	fk_loyalty_activity_line_items_loyalty_activity	(id) ← loyalty_activity_line_items(loyalty_activity_id)
	fk_loyalty_activity_results_errors_loyalty_activities	(id) ← loyalty_activity_results_errors
	fk_loyalty_activity_tender_items_loyalty_activity	(id) ← loyalty_activity_tender_items(loyalty_activity_id)
	fk_loyalty_redemptions_items_loyalty_activity	(id) ← loyalty_redemption_items(loyalty_activity_id)
	fk_member_offer_usage_histories_loyalty_activities	(id) ← member_offer_usage_histories(loyalty_activity_id)
	fk_member_offers_loyalty_activities_id	(id) ← member_offers(loyalty_activity_id)
	fk_member_packaged_redemptions_loyalty_activities	(id) ← member_packaged_redemptions(loyalty_activity_id)
	fk_member_reward_usage_history_loyalty_activity_id	(id) ← member_reward_usage_histories(loyalty_activity_id)
	fk_member_rewards_loyalty_activities_id	(id) ← member_rewards(loyalty_activity_id)
	fk_member_streak_actions_loyalty_activities	(id) ← member_streak_actions(activity_id)
	fk_member_transient_activities_loyatly_activities	(id) ← member_transient_activities
	fk_member_transient_activities_loyalty_activities_id	(id) ← member_transient_activities
	fk_streak_event_logs_loyalty_activities_id	(id) ← members_streak_event_logs(loyalty_activity_id)
	fk_rule_match_loyalty_activities_loyalty_activity_id	(id) ← rule_match(loyalty_activity_id)
	fk_rule_match_allocations_loyalty_activitie_id	(id) ← rule_match_allocations(loyalty_activity_id)
	fk_rule_match_lines_loyalty_activities_id	(id) ← rule_match_lines(loyalty_activity_id)

Table loyalty_activity_best_offers

Idx	Field Name	Data Type	Description
*	loyalty_activity_id	varchar(50) encode raw	Unique id
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	Reference to Program
	member_id	varchar(50) encode zstd	Member's ID.
	policy_id	varchar(50) encode zstd	_id of the Purse Policy
	is_global	boolean encode raw	Whether the offer is global or not
	type	varchar(50) encode zstd	type of offer(Offer/Global Offer)
	upc	varchar(65535) encode zstd	UPC code of the discount lineItem to be given when this offer is applied. Should usually exist in Products.
	discount	decimal(18,6) ENCODE RAW	Discount amount awarded as part of Global Offer redemption.
	code	varchar(250) encode raw	offer code.
	member_coupon_id	varchar(50) encode zstd	_id of the member coupon
	member_reward_id	varchar(50) encode zstd	_id of the member reward
	txn_header_id	varchar(250) encode zstd
	txn_offer_redeem_id	varchar(250) encode zstd
	reward_item_list	varchar(65535) encode zstd
	reward_def_id	varchar(50) encode zstd	Unique id of the reward policy.
	coupon_def_id	varchar(50) encode zstd	Unique id of the reward policy.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode raw
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw	Indicates the activity date on which it is processed
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	line_item_number	integer ENCODE zstd	Line Order Number
	eligible_items	super ENCODE zstd
Indexes
	loyalty_activity_best_offers_dist_key	ON member_id
	loyalty_activity_best_offers_sort_key	ON updated_utc_ts, member_id, loyalty_activity_id, policy_id
Foreign Keys
	fk_loyalty_activity_best_offers_loyalty_activity	(loyalty_activity_id) → loyalty_activities(id)
	fk_loyalty_activity_best_offers_orgs	(org_id) → orgs(id)
	fk_loyalty_activity_best_offers_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_loyalty_activity_best_offers_members	(member_id) → members(id)
	fk_loyalty_activity_best_offers_created_by	(created_by) → users(id)
	fk_loyalty_activity_best_offers_updated_by	(updated_by) → users(id)
	fk_loyalty_activity_best_offers_reward_policies	(policy_id) → reward_policies(id)

Table loyalty_activity_line_items

Stores the individual line items purchased with an activity.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
	member_id	varchar(50) encode zstd	Member's ID.
	loyalty_activity_id	varchar(50) encode raw	_id of the activity
	product_id	varchar(50) encode raw	Associated Product. This field is populated if we have performed a Lookup Products action within the rules of the program.
	line_item_type	varchar(50) encode zstd	This type captures a few retail situations which will be handled differently by loyalty rules. Usually those would be set by the ordering channel integration such as POS. Domain: [normal/void/discount/return/tax]
	line_item_number	integer encode raw	Line item number.
	item_price	decimal(18,6) ENCODE zstd	Individual item sales price.
	quantity	integer encode zstd	Quantity of line item.
	item_uom	varchar(250) encode zstd	Item unit of measure. Configurable domain. Default domain: [each/6-Pak/box/carton/case/gram/kg/oz/lb/ml/cc/cubicMeter/liter/fluidOunce/pint/quart/gallonUS/gallonUK/EA]
	total_amount	decimal(18,6) ENCODE RAW	Sub-total of the line item (usually price x quantity).
	discount_amount	decimal(18,6) ENCODE RAW	Pre-applied line item discount.
	coupon_code	varchar(100) encode zstd	Optional coupon to be applied directly to the item.
	coupon	varchar(100) encode zstd	Populates applied reward policy from offers or rewards collection with limited data. Refer to Offer Structure/Reward Structure for more details.
	offer_id	varchar(50) encode raw	Offer id.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw	Indicates the activity date on which it is processed
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	product_sku	varchar(75) encode zstd	Stock Keeping Unit number. This will match the sku field of Product.
Indexes
	pk_loyalty_activity_line_items	ON id
	loyalty_activity_line_items_dist_key	ON member_id
	loyalty_activity_line_items_sort_key	ON updated_utc_ts, loyalty_activity_id, activity_date, product_id, line_item_number, offer_id
Foreign Keys
	fk_loyalty_activity_line_items_loyalty_activity	(loyalty_activity_id) → loyalty_activities(id)
	fk_loyalty_activity_line_items_products	(product_id) → products(id)
	fk_loyalty_activity_line_items_created_by	(created_by) → users(id)
	fk_loyalty_activity_line_items_updated_by	(updated_by) → users(id)
	fk_loyalty_activity_line_items_orgs	(org_id) → orgs(id)
	fk_loyalty_activity_line_items_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_loyalty_activity_line_items_members	(member_id) → members(id)

Table loyalty_activity_results_errors

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW	readonly unique Unique ID
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE RAW
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
	message	varchar(65535) ENCODE zstd	Validation Error
	status	bigint ENCODE zstd	Activity Status. Could be one of Processed, Error, or Cancelled.
	code	bigint ENCODE zstd
Indexes
	pk_loyalty_activity_results_errors	ON id
	distribution	ON id
	sorting	ON updated_utc_ts, id
Foreign Keys
	fk_loyalty_activity_results_errors_loyalty_activities	(id) → loyalty_activities
	fk_loyalty_activity_results_errors_users_created_by	(created_by) → users(id)
	fk_loyalty_activity_results_errors_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table loyalty_activity_tender_items

Stores the tender items associated with an activity where a purchase was made.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	Id of the item
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
	member_id	varchar(50) encode zstd	The _id field from Member. Activities can be submitted directly without a Loyalty ID if we know this value.
	loyalty_activity_id	varchar(50) encode raw	_id of the activity
	tender_item_type	varchar(50) encode zstd	The tender item can be of several types, indicating payment method. Configurable domain. Default domain: [VISA/Mastercard/SVC/Cash/Credit Card]
	tender_item_sub_type	varchar(250) encode zstd
	tender_item_number	varchar(50) encode zstd	This can be used to store a hash of a credit card or other type of payment method.
	line_number	integer encode zstd	Line item number of the tender item on the ticket.
	value	decimal(18,6) ENCODE RAW	Amount of currency paid via this line item.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw	Indicates the activity date on which it is processed
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
Indexes
	pk_loyalty_activity_tender_items	ON id
	loyalty_activity_tender_items_dist_key	ON member_id
	loyalty_activity_tender_items_sort_key	ON updated_utc_ts, loyalty_activity_id, activity_date
Foreign Keys
	fk_loyalty_activity_tender_items_loyalty_activity	(loyalty_activity_id) → loyalty_activities(id)
	fk_loyalty_activity_tender_items_created_by	(created_by) → users(id)
	fk_loyalty_activity_tender_items_updated_by	(updated_by) → users(id)
	fk_loyalty_activity_tender_items_orgs	(org_id) → orgs(id)
	fk_loyalty_activity_tender_items_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_loyalty_activity_tender_items_members	(member_id) → members(id)

Table loyalty_cards

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	card_type	varchar(50) encode zstd	Indicates the type of loyalty id
*	loyalty_type	varchar(50) encode zstd
	status	varchar(250) encode zstd
	channel	varchar(250) encode zstd
*	loyalty_id	varchar(100) encode raw	Value of the loyalty id.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_loyalty_cards	ON id
	loyalty_cards_dist_key	ON loyalty_id
	loyalty_cards_sort_key	ON updated_utc_ts, loyalty_id
Foreign Keys
	fk_loyalty_cards_users_created_by	(created_by) → users(id)
	fk_loyalty_cards_users_updated_by	(updated_by) → users(id)
	fk_loyalty_cards_orgs	(org_id) → orgs(id)

Table loyalty_programs

Activity records will be stored in the database according to the selected timezone.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	program_name	varchar(50) encode zstd	Program name - e.g. 'Starwood Rewards'
	program_description	varchar(1000) encode lzo	Detailed description of the program.
	is_multinational	boolean encode raw	It specifies whether it’s part of multiple countries
	share_ids	boolean encode raw	It identicates shared loyalty ids for hop account
	week_start_day	varchar(50) encode zstd	It is designated start of week
	is_merge_an_activity	boolean encode raw	if merge is to be considered an activity
	redemption_order	varchar(50) encode zstd	The order of the points redemptions
	restrict_manual_adds_to_appeasements	boolean encode raw
	has_changes	boolean encode raw	Does it have unpublished changes.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	check_duplicate_txn	boolean encode raw	If check_duplicate_txn is true, it won't allow duplicate transactions
	override_save_on_activity	boolean	This flag overrides the default Activity/Member save behaviour available for the rule actions
	optimize_act_log	boolean	This flag is to optimize the activity log.'
	raise_activity_log_event	boolean	This flag is to raise the events for activity log.'
	aggregate_offset_timezone	varchar(50) ENCODE zstd	Activity records will be stored in the database according to the selected timezone.
	raise_metric_event_per_instance	boolean ENCODE RAW	This is to prevent the generation of multiple events in the Kafka L2 topic.
Indexes
	pk_loyalty_programs	ON id
	loyalty_programs_sort_key	ON id
Foreign Keys
	fk_loyalty_programs_orgs	(org_id) → orgs(id)
	fk_loyalty_programs_users_created_by	(created_by) → users(id)
	fk_loyalty_programs_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_account_members_loyalty_programs_program_id	(id) ← account_members(program_id)
	fk_loyalty_accrual_items_loyalty_programs	(id) ← loyalty_accrual_items(loyalty_program_id)
	fk_loyalty_activities_loyalty_programs	(id) ← loyalty_activities(loyalty_program_id)
	fk_loyalty_activity_best_offers_loyalty_programs	(id) ← loyalty_activity_best_offers(loyalty_program_id)
	fk_loyalty_activity_line_items_loyalty_programs	(id) ← loyalty_activity_line_items(loyalty_program_id)
	fk_loyalty_activity_tender_items_loyalty_programs	(id) ← loyalty_activity_tender_items(loyalty_program_id)
	fk_member_badges_loyalty_programs	(id) ← member_badges(loyalty_program_id)
	fk_member_purses_loyalty_programs	(id) ← member_purses(loyalty_program_id)
	fk_member_loyalty_tiers_loyalty_programs	(id) ← member_tiers(loyalty_program_id)
	fk_member_transient_activities	(id) ← member_transient_activities(loyalty_program_id)
	fk_member_loyalty_program	(id) ← members(loyalty_program_id)
	fk_merged_members_loyalty_programs	(id) ← merged_members(loyalty_program_id)
	fk_partners_loyalty_programs	(id) ← partners(loyalty_program_id)
	fk_partners_loyalty_program	(id) ← partners(loyalty_program_id)
	fk_program_rule_actions_loyalty_programs	(id) ← program_rule_actions(loyalty_program_id)
	fk_program_rule_availability_loyalty_programs	(id) ← program_rule_availability(loyalty_program_id)
	fk_program_rule_conditions_loyalty_programs	(id) ← program_rule_conditions(loyalty_program_id)
	fk_program_rule_control_groups	(id) ← program_rule_control_groups(loyalty_program_id)
	fk_program_rule_folders_loyalty_programs	(id) ← program_rule_folders(loyalty_program_id)
	fk_program_rule_limits_loyalty_programs	(id) ← program_rule_limits(loyalty_program_id)
	fk_program_rule_locations_loyalty_programs	(id) ← program_rule_locations(loyalty_program_id)
	fk_program_rule_segments_loyalty_programs_loyalty_program_id	(id) ← program_rule_mandatory_segments(loyalty_program_id)
	fk_program_rule_products_loyalty_programs	(id) ← program_rule_products(loyalty_program_id)
	fk_program_rule_segments_loyalty_programs	(id) ← program_rule_segments(loyalty_program_id)
	fk_program_rule_tier_levels_loyalty_programs	(id) ← program_rule_tier_levels(loyalty_program_id)
	fk_program_rules_loyalty_programs	(id) ← program_rules(loyalty_program_id)
	fk_promo_code_defs	(id) ← promo_code_defs(loyalty_program_id)
	fk_purse_policies_loyalty_programs	(id) ← purse_policies(loyalty_program_id)
	fk_reward_policies_loyalty_programs	(id) ← reward_policies(loyalty_program_id)
	fk_reward_policy_availability_loyalty_programs	(id) ← reward_policy_availability(loyalty_program_id)
	fk_reward_policy_control_groups_loyalty_programs	(id) ← reward_policy_control_groups(loyalty_program_id)
	fk_reward_policy_locations_loyalty_programs	(id) ← reward_policy_locations(loyalty_program_id)
	fk_reward_policy_mandatory_segments_loyalty_programs_loyalty_program_id	(id) ← reward_policy_mandatory_segments(loyalty_program_id)
	fk_reward_policy_products_loyalty_programs	(id) ← reward_policy_products(loyalty_program_id)
	fk_reward_policy_segments_loyalty_programs	(id) ← reward_policy_segments(loyalty_program_id)
	fk_reward_policy_tier_levels_loyalty_programs	(id) ← reward_policy_tier_levels(loyalty_program_id)
	fk_reward_upc_mapping_loyalty_programs	(id) ← reward_upc_mapping(loyalty_program_id)
	fk_streak_goal_policies_loyalty_programs	(id) ← streak_goal_policies(loyalty_program_id)
	fk_streak_policies_loyalty_programs	(id) ← streak_policies(loyalty_program_id)
	fk_streak_policy_optin_segments_loyalty_programs	(id) ← streak_policy_optin_segments(loyalty_program_id)
	fk_tier_policies_loyalty_programs	(id) ← tier_policies(loyalty_program_id)
	fk_tier_policy_levels_loyalty_programs	(id) ← tier_policy_levels(loyalty_program_id)

Table loyalty_redemption_items

Stores redemption items for any point redemptions done in an activity. Each action in a rule will generate a separate redemption item, with the redemption item pointing to exactly one accrual item form which it has burned points. Redemption items can further be split up depending on whether or not we have to split a single redemption among different accrual items from which we are withdrawing.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_accrual_item_id	varchar(50) encode raw	The _id of the accrual from which points were redeemed in this redemption
	loyalty_activity_id	varchar(50) encode raw	The _id of the parent activity that generated this redemption
*	member_id	varchar(50) encode raw	_id of the Member for which the redemption was generated.
	rule_id	varchar(50) encode raw	Unique ID of rule that triggered the offer utility
	member_purse_id	varchar(50) encode raw	Purse _id for which this redemption was done
	purse_policy_id	varchar(50) encode raw	_id of the purse policy
	redemption_date	timestamptz encode raw	Date/time when the redemption item was recorded.
	redeemed_pts	bigint ENCODE zstd	Number of points redeemed in this redemption.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	packaged_redemption_id	varchar(50) encode lzo	We populate _id of Member.purse.redemptions value in redemptionItems.packagedRedemption
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
	target_act	varchar(50) encode lzo	whenever a member perform multiple activity to redeem from a particular activity we use target_act column
	awaiting_accrual	boolean encode raw	This field will never make it to redemptionitems collection
Indexes
	loyalty_redemption_items_dist_key	ON member_id
	pk_loyalty_redemption_items	ON id
	loyalty_redemption_items_sort_key	ON updated_utc_ts, member_id, purse_policy_id, redemption_date, loyalty_activity_id, member_purse_id
Foreign Keys
	fk_loyalty_redemptions_items_loyalty_activity	(loyalty_activity_id) → loyalty_activities(id)
	fk_loyalty_redemption_items_members	(member_id) → members(id)
	fk_loyalty_redemption_items_program_rules	(rule_id) → program_rules(id)
	fk_loyalty_redemption_items_member_purses	(member_purse_id) → member_purses(id)
	fk_loyalty_redemption_items	(loyalty_accrual_item_id) → loyalty_accrual_items(id)
	fk_loyalty_redemption_items_created_by	(created_by) → users(id)
	fk_loyalty_redemption_items_updated_by	(updated_by) → users(id)
	fk_loyalty_redemption_items_orgs	(org_id) → orgs(id)
	fk_loyalty_redemption_items_purse_policies	(purse_policy_id) → purse_policies(id)

Table member_badges

Stores badges earned by a member.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	member_id	varchar(50) encode raw	readonly Unique value for every record
*	badge_name	varchar(50) encode zstd	Name of the badge achieved.
*	achieved_on_date	timestamptz encode raw	Date/time of the achievement.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	member_badges_dist_key	ON member_id
	pk_member_badges	ON id
	member_badges_sort_key	ON updated_utc_ts, member_id, achieved_on_date
Foreign Keys
	fk_account_badges_members	(member_id) → members(id)
	fk_member_badges_users_created_by	(created_by) → users(id)
	fk_member_badges_users_updated_by	(updated_by) → users(id)
	fk_member_badges_orgs	(org_id) → orgs(id)
	fk_member_badges_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table member_loyalty_ids

Stores the active registered loyalty ids for a member.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_id	varchar(100)	Value of the loyalty id - for example, it could be a card number, a barcode number, a phone number, email address, or any other identifier.
*	member_id	varchar(50) encode raw	required _id field of the Member that is associated with this id.
*	loyalty_id_name	varchar(50) encode zstd	The name of the loyalty id - e.g. 'Loyalty Card'.
	accrue_to	varchar(50) encode zstd	id of the group member to whom points to be added
	status	varchar(250) encode zstd	status of the loyalty id(Active/Disabled/Locked/New/PreEnrolled)
	is_primary	boolean encode raw	whether loyalty id is primary or not
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	member_loyalty_ids_dist_key	ON member_id
	pk_member_loyalty_ids	ON id
	member_loyalty_ids_sort_key	ON updated_utc_ts, member_id, id
	unq_member_loyalty_ids_loyalty_id	ON loyalty_id
Foreign Keys
	fk_member_loyalty_ids_members	(member_id) → members(id)
	fk_member_loyalty_ids_users_created_by	(created_by) → users(id)
	fk_member_loyalty_ids_users_updated_by	(updated_by) → users(id)
	fk_member_loyalty_ids_orgs	(org_id) → orgs(id)
Referring Foreign Keys
	fk_loyalty_activities_member_loyalty_ids	(loyalty_id) ← loyalty_activities
	fk_member_streaks_member_loyalty_ids	(id) ← member_streaks(loyalty_id)
	fk_member_transient_activities_member_loyalty+ids	(loyalty_id) ← member_transient_activities

Table member_notes

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
*	member_id	varchar(50) encode zstd
*	note	varchar(65535) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
Indexes
	pk_member_notes_id	ON id
	member_notes_dist_key	ON member_id
	member_notes_sort_key	ON updated_utc_ts, member_id, id
Foreign Keys
	fk_member_notes_members	(member_id) → members(id)
	fk_member_notes_orgs	(org_id) → orgs(id)
	fk_member_notes_users_created_by	(created_by) → users(id)
	fk_member_notes_users_updated_by	(updated_by) → users(id)

Table member_offer_usage_histories

This table keeps information about member offer usage

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	member_offer_id	varchar(50) encode raw	_id of the member offer
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	Member ID for which history is collected
*	loyalty_activity_id	varchar(50) encode raw	Activity ID which used/reverted the reward
*	rule_id	varchar(50) encode raw	Rule ID which used/reverted the reward
*	event_id	varchar(50) encode zstd	Event ID, from which the data was collected
	activity_date	timestamptz encode raw	The activity date using/reverting the reward
	local_date	timestamptz encode zstd	The local date on which activity using/reverting the reward
	times_used	integer encode zstd	The number of times the offer was used
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
Indexes
	member_offer_usage_histories_dist_key	ON member_id
	pk_member_offer_usage_histories	ON id
	member_offer_usage_histories_sort_key	ON updated_utc_ts, member_offer_id, member_id, loyalty_activity_id, activity_date, rule_id
Foreign Keys
	fk_member_offer_usage_histories_member_offers	(member_offer_id) → member_offers(id)
	fk_member_offer_usage_histories_members	(member_id) → members(id)
	fk_member_offer_usage_histories_loyalty_activities	(loyalty_activity_id) → loyalty_activities(id)
	fk_member_offer_usage_histories_program_rules	(rule_id) → program_rules(id)
	fk_member_offer_usage_histories_created_by	(created_by) → users(id)
	fk_member_offer_usage_histories_updated_by	(updated_by) → users(id)
	fk_member_offer_usage_histories_orgs	(org_id) → orgs(id)

Table member_offers

Stores the offers present in the member offer wallet. If a global offer is used, it will be present in the wallet and marked used.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	offer_name	varchar(250) encode zstd	Offer name, for example 'Free KitKat with any 2 Gatorade Purchases'.
	offer_code	varchar(100) encode zstd	Unique offer code.
	offer_description	varchar(1000) encode zstd	Offer detailed description.
	times_used	integer encode zstd	number of times used in the offer.
	uses_left_number	integer encode zstd	Number of uses left on the offer.
	effective_utc_ts	timestamptz encode raw	Offer effective date/time.
	expires_on_date	timestamptz encode raw	Offer expiration date/time.
*	member_id	varchar(50) encode raw	required _id of the Member to which member the offer belongs to.
	is_global	boolean encode raw	Whether the offer is global or not. Domain: [true/false]
*	reward_policy_id	varchar(50) encode raw	required _id of the Reward Policy associated with this offer.
	upc	varchar(50) encode zstd	Universal product code
	redemption_date	timestamptz encode raw	UTC date on which the offer is utilized
	redemption_local_date	timestamptz encode zstd	Local date on which the offer is utilized
	activity_utc_offset	integer encode zstd	Difference between redemption_date and redemption_local_date
	program_rule_id	varchar(50) encode zstd	Unique ID of rule that triggered the offer utility
	loyalty_activity_id	varchar(50) encode zstd	Unique ID of the activity where the offer is utilized
	discount	decimal(18,6) ENCODE RAW	Discount amount awarded as part of Global Offer redemption.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	is_cancelled	boolean encode raw	Indicates if a member offer is cancelled
	can_preview	boolean encode raw	Whether the offer can be used in arbitration before the effective date, only for members who have canPreview as true.
	activity_burn	varchar(50) encode zstd	_id of activity where the respective member offer has been utilized
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
	single_use	boolean ENCODE RAW	Indicates if the offer added to member is meant for a one time use
Indexes
	member_offers_dist_key	ON member_id
	pk_member_offers	ON id
	member_offers_sort_key	ON updated_utc_ts, member_id, reward_policy_id, redemption_date, effective_utc_ts, expires_on_date, id
Foreign Keys
	fk_member_offers_members	(member_id) → members(id)
	fk_member_offers_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_member_offers_users_created_by	(created_by) → users(id)
	fk_member_offers_users_updated_by	(updated_by) → users(id)
	fk_member_offers_orgs	(org_id) → orgs(id)
	fk_member_offers_loyalty_activities_id	(loyalty_activity_id) → loyalty_activities(id)
	fk_member_offers_program_rules_id	(program_rule_id) → program_rules(id)
Referring Foreign Keys
	fk_member_offer_usage_histories_member_offers	(id) ← member_offer_usage_histories(member_offer_id)

Table member_packaged_redemptions

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd
*	org_id	varchar(50) encode zstd
	loyalty_accrual_item_id	varchar(50) encode raw
	loyalty_activity_id	varchar(50) encode raw
*	member_id	varchar(50) encode raw
	rule_id	varchar(50) encode raw
	member_purse_id	varchar(50) encode raw
	purse_policy_id	varchar(50) encode raw
	redemption_date	timestamptz encode raw
	redeemed_pts	bigint ENCODE zstd
	activity_utc_offset	integer encode raw
	state	varchar(50) encode zstd
	packaged_redemption_id	varchar(50) encode zstd	Unique id is generated while a new record inserted in packaged_redemption.
	delete_flag	varchar(1) DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	target_act	varchar(50) encode zstd	whenever a member perform multiple activity to redeem from a particular activity we use target_act column
	awaiting_accrual	boolean encode raw	To identify if the redemptionItem is packaged or Not. For packaged redemptions we are populating awaiting_accrual as True. This flag will help reporting to eliminate the redemptions that are about to be associated with an accrual and moved to the actual redemptionItemts collection and consider only the ones that are going to remain there.
Indexes
	pk_member_packaged_redemptions_id	ON id
Foreign Keys
	fk_member_packaged_redemptions_loyalty_activities	(loyalty_activity_id) → loyalty_activities(id)
	fk_member_packaged_redemptions_members	(member_id) → members(id)
	fk_member_packaged_redemptions_program_rules	(rule_id) → program_rules(id)
	fk_member_packaged_redemptions_member_purses	(member_purse_id) → member_purses(id)
	fk_member_packaged_redemptions_users	(created_by) → users(id)
	fk_member_packaged_redemptions	(updated_by) → users(id)
	fk_member_packaged_redemptions_orgs	(org_id) → orgs(id)
	fk_member_packaged_redemptions_purse_policies	(purse_policy_id) → purse_policies(id)

Table member_preferences

Stores member preferences which can be either inferred through analysis, or specified by the member via opt-in, etc.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	required Member mongoDB ID.
*	preference_name	varchar(50) encode zstd	The name of the preference, e.g. 'Interest in Cold Drinks'.
	preference_type	varchar(50) encode zstd	Preference type. [String/Number/Boolean/Date]
*	preference_value	varchar(50) encode zstd	Value of the preference. Type Boolean has domain of [Yes/No].
	inferred_flag	boolean encode raw	Determines if a preference was inferred or directly specified by the member. Domain: [true/false]
*	optin_date	timestamptz encode raw	Date/time when the member opted in to a preference.
	expires_on_date	timestamptz encode zstd	The date/time when the preference will expire. This is only used for things like auto-opt-in preferences where a preference is assumed, and a legal period is given to opt out.
*	category	varchar(50) ENCODE zstd	Category under which the preference belongs to
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	member_preferences_dist_key	ON member_id
	pk_member_preferences	ON id
	member_preferences_sort_key	ON updated_utc_ts, member_id, optin_date
Foreign Keys
	fk_member_preference_members	(member_id) → members(id)
	fk_member_preferences_users_created_by	(created_by) → users(id)
	fk_member_preferences_users_updated_by	(updated_by) → users(id)
	fk_member_preferences_orgs	(org_id) → orgs(id)

Table member_purses

Stores the active member purses, which store balances of reward currencies, such as points, frequency club counts, etc.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	member_id	varchar(50) encode raw	required Member mongoDB ID.
*	purse_policy_id	varchar(50) encode raw	Id of the purse policy to which the purse belongs to
*	purse_name	varchar(250) encode zstd	Name of the purse (per the policy). E.g. 'Points', 'Free Drinks', etc.
	purse_balance	bigint ENCODE zstd	Point balance of the purse.
	available_balance	bigint ENCODE zstd	Available purse balance which can be redeemed. It is different from balance if there is an escrow period configured on the purse policy.
	expires_in_days	integer encode zstd	Number of days that points in the purse expire (from the time of the accrual).
	escrows_in_days	integer encode zstd	Number of days that points in the purse escrow (from the time of the accrual). Points in escrow are "pending" and can't be spent.
	overdraft_limit	integer encode zstd	Amount by which a purse can be overdrawn.
*	primary_flag	boolean encode raw	Whether the purse is primary or not. Domain: [true/false]
	expired_points	bigint ENCODE zstd	Number of points in the purse that are expired.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	last_expiration_date	timestamptz encode zstd	The latest date on which points are expired from this purse.
	accrued_pts	bigint encode zstd	Total number of accrued points of a member
	redeemed_pts	bigint encode zstd	Total number of redeemed points of a member
	locked_points	super encode zstd	number of points locked
	expiry_pending_date	timestamptz encode zstd	Date of the expiration activity performed
	expiry_pending_activity_id	varchar(50) encode zstd	Activity id of the expiration is populated on all accruals which are expired
Indexes
	member_purses_dist_key	ON member_id
	pk_member_purses	ON id
	member_purses_sort_key	ON updated_utc_ts, member_id, purse_policy_id, id
Foreign Keys
	fk_account_purses_member	(member_id) → members(id)
	fk_member_purse_purse_policies	(purse_policy_id) → purse_policies(id)
	fk_member_purses_users_created_by	(created_by) → users(id)
	fk_member_purses_users_updated_by	(updated_by) → users(id)
	fk_member_purses_orgs	(org_id) → orgs(id)
	fk_member_purses_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_loyalty_accrual_items_member_purses	(id) ← loyalty_accrual_items(member_purse_id)
	fk_loyalty_redemption_items_member_purses	(id) ← loyalty_redemption_items(member_purse_id)
	fk_member_packaged_redemptions_member_purses	(id) ← member_packaged_redemptions(member_purse_id)

Table member_referrals

Stores referrals made by a member to refer other members.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	referral_code	varchar(100) encode zstd	Unqiue code used by the referred member to enroll.
*	referral_status	varchar(50) encode zstd	Status of the referral. Domain: [pending/completed]
	referrer_member_id	varchar(50) encode raw	_id of the member, that created/initiated the referral code.
*	referred_member_name	varchar(200) encode lzo	Name of person referred to the loyalty program.
	referred_member_handle	varchar(50) encode zstd	Social handle of the person referred to the loyalty program.
	referred_member_email	varchar(200) encode lzo	Email address of the person referred to the loyalty program.
	referral_completion_utc_ts	timestamptz encode zstd	Date/time of referral completion. A referral is completed when the referred person enrolls with the referral code.
*	referral_expiration_utc_ts	timestamptz encode zstd	Date/time the unique referral code expires.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	referred_member_id	varchar(50) encode zstd	A valid _id of the member, which was newly enrolled with a valid referral code.
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
Indexes
	member_referrals_dist_key	ON referrer_member_id
	pk_member_referrals	ON id
	member_referrals_sort_key	ON updated_utc_ts, referrer_member_id
Foreign Keys
	fk_member_referrals_members	(referrer_member_id) → members(id)
	fk_member_referrals_users_created_by	(created_by) → users(id)
	fk_member_referrals_users_updated_by	(updated_by) → users(id)
	fk_member_referrals_orgs	(org_id) → orgs(id)

Table member_reward_usage_histories

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	member_reward_id	varchar(50) encode raw	_id of the member reward
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	Member ID for which history is collected
*	loyalty_activity_id	varchar(50) encode raw	Activity ID which used/reverted the reward
*	rule_id	varchar(50) encode raw	Rule ID which used/reverted the reward
*	event_id	varchar(50) encode zstd	Event ID, from which the data was collected
	activity_date	timestamptz encode raw	The activity date using/reverting the reward
	times_used	integer encode zstd	The number of times the reward was used
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	merge_ids	super encode zstd
	issue_value	decimal(18,6) ENCODE zstd	As we are maintaining limits for purses, same way we need to do it for Rewards. It tells what was the issue value of the reward.
	redm_value	decimal(18,6) ENCODE zstd	As we are maintaining limits for purses, same way we need to do it for Rewards. It tells what was the redeemed value of the reward.
Indexes
	member_reward_usage_histories_dist_key	ON member_id
	pk_member_reward_usage_histories	ON id
	member_reward_usage_histories_sort_key	ON updated_utc_ts, member_reward_id, member_id, loyalty_activity_id, activity_date, rule_id
Foreign Keys
	fk_member_reward_usage_history_reward_id	(member_reward_id) → member_rewards(id)
	fk_member_reward_usage_history_member_id	(member_id) → members(id)
	fk_member_reward_usage_history_loyalty_activity_id	(loyalty_activity_id) → loyalty_activities(id)
	fk_member_reward_usage_history_rule_id	(rule_id) → program_rules(id)
	fk_member_reward_usage_histories_created_by	(created_by) → users(id)
	fk_member_reward_usage_histories_updated_by	(updated_by) → users(id)
	fk_member_reward_usage_histories_orgs	(org_id) → orgs(id)

Table member_rewards

Stores the rewards present in the member offer wallet.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	required _id of the Member to which member the reward belongs to
*	reward_name	varchar(250) encode zstd	Reward name, for example 'Free Drink'
	reward_description	varchar(1000) encode lzo	Reward detailed description.
	reward_code	varchar(100) encode zstd	Reward unique code.
	reward_type	varchar(50) encode zstd	Reward type - legacy field. This is always set to 'Reward'.
	upc	varchar(50) encode zstd	If the reward maps to a UPC this is stored here.
	uses_left_number	integer encode zstd	Number of uses left on the reward.
*	reward_policy_id	varchar(50) encode raw	required _id of the Reward Policy associated with this reward.
	image_url	varchar(65535) encode lzo	URL pointing to an image of the Reward (for display in various types of apps, advertising, etc.)
	expires_on_date	timestamptz encode raw	Date/time the reward expires.
	effective_utc_ts	timestamptz encode raw	Date/time the reward is effective.
	redemption_date	timestamptz encode raw	UTC date on which the offer is utilized
	redemption_local_date	timestamptz encode zstd	Local date on which the reward is utilized
	activity_utc_offset	integer encode zstd	Difference between redemption_date and redemption_local_date
	program_rule_id	varchar(50) encode zstd	Unique ID of rule that triggered the offer utility
	loyalty_activity_id	varchar(50) encode zstd	Snapshot of victim loyalty ids
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	is_cancelled	boolean encode raw	Indicates if a member offer is cancelled
	can_preview	boolean encode raw	Whether the offer can be used in arbitration before the effective date, only for members who have canPreview as true.
	locked_till	timestamptz encode zstd	The reward will be locked till the timestamp populated as a part of this field
	activity_burn	varchar(50) encode zstd	_id of the activity where the respective member reward has been utilized
	activity_lock	varchar(50) encode zstd	_id of the activity where the respective member reward has been locked
	activity_unlock	varchar(50) encode zstd	_id of the activity where the respective member reward has been unlocked
	activity_cancel	varchar(50) encode zstd	_id of the Cancellation activity which marks the respective member reward as Cancelled.
	merge_ids	super encode zstd	_id of the Merge operation, Updated for the respective victims records.
	issue_value	decimal(18,6) ENCODE zstd	As we are maintaining limits for purses, same way we need to do it for Rewards. It tells what was the issue value of the reward.
	redm_value	decimal(18,6) ENCODE zstd	As we are maintaining limits for purses, same way we need to do it for Rewards. It tells what was the redeemed value of the reward.
Indexes
	member_rewards_dist_key	ON member_id
	pk_member_rewards	ON id
	member_rewards_sort_key	ON updated_utc_ts, member_id, reward_policy_id, redemption_date, effective_utc_ts, expires_on_date, id
Foreign Keys
	fk_member_rewards_members	(member_id) → members(id)
	fk_account_rewards_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_member_rewards_users_created_by	(created_by) → users(id)
	fk_member_rewards_users_updated_by	(updated_by) → users(id)
	fk_member_rewards_orgs	(org_id) → orgs(id)
	fk_member_rewards_loyalty_activities_id	(loyalty_activity_id) → loyalty_activities(id)
	fk_member_rewards_program_rules_id	(program_rule_id) → program_rules(id)
Referring Foreign Keys
	fk_member_reward_usage_history_reward_id	(id) ← member_reward_usage_histories(member_reward_id)

Table member_segments

Stores segments in which the member is included for the purposes of personalization.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	required Member mongoDB ID.
*	segment_id	varchar(50) encode raw	required name of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	member_segments_dist_key	ON member_id
	pk_member_segments	ON id
	member_segments_sort_key	ON updated_utc_ts, member_id, segment_id
Foreign Keys
	fk_member_segments_members	(member_id) → members(id)
	fk_member_segments_segments	(segment_id) → segments(id)
	fk_member_segments_users_created_by	(created_by) → users(id)
	fk_member_segments_users_updated_by	(updated_by) → users(id)
	fk_member_segments_orgs	(org_id) → orgs(id)
Referring Foreign Keys
	fk_program_rule_segments_member_segments_segment_id	(id) ← program_rule_mandatory_segments(mandatory_segment_id)
	fk_reward_policy_mandatory_segments_member_segments	(id) ← reward_policy_mandatory_segments(mandatory_segment_id)

Table member_streak_actions

Idx	Field Name	Data Type	Description
	activity_id	varchar(50) ENCODE raw	_id of the activity
*	member_streak_id	varchar(50) ENCODE raw	_id of the member streak
*	member_id	varchar(50) ENCODE raw	_id of the member
	activity_type	varchar(50) ENCODE zstd
	activity_date	timestamptz ENCODE zstd	Date/time when the activity occured.
	value	decimal(18,6) ENCODE raw
	count	integer ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamptz ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamptz ENCODE raw
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
*	event_id	varchar(250) ENCODE zstd
	activity_utc_offset	integer ENCODE zstd
Indexes
	pk_member_streak_actions	ON member_streak_id, event_id
	member_streak_actions_dist_key	ON member_id
	member_streak_actions_sort_key	ON updated_utc_ts, activity_id, member_id, member_streak_id
Foreign Keys
	fk_member_streak_actions_loyalty_activities	(activity_id) → loyalty_activities(id)
	fk_member_streak_actions_member_streaks	(member_streak_id) → member_streaks(id)
	fk_member_streak_actions_users_created_by	(created_by) → users(id)
	fk_member_streak_actions_users_updated_by	(updated_by) → users(id)

Table member_streaks

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	_id of the member streak
*	member_id	varchar(50) encode raw	_id of the member
	loyalty_id	varchar(50) encode zstd	_id of the loyaltyids
*	streak_code	varchar(65535) ENCODE zstd
*	status	varchar(50) ENCODE zstd
	value	decimal(18,6) ENCODE raw
	count	integer ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamptz ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamptz ENCODE raw
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_member_streaks	ON id
	sort_member_streaks	ON id, member_id, updated_utc_ts
	member_streaks_dist_key	ON member_id
Foreign Keys
	fk_member_streaks_members_id	(member_id) → members(id)
	fk_member_streaks_member_loyalty_ids	(loyalty_id) → member_loyalty_ids(id)
	fk_member_streaks_users_created_by	(created_by) → users(id)
	fk_member_streaks_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_member_streak_actions_member_streaks	(id) ← member_streak_actions(member_streak_id)

Table member_tier_histories

Stores the changes in tier levels as member tiers get upgraded/downgraded.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	member_id	varchar(50) encode raw	required Member mongoDB ID.
	event_id	varchar(50) encode zstd	Unique event id. This is used internally and is unique for each tier transition.
*	tier_id	varchar(50) encode raw	_id of the Tier
	prev_tier_level	varchar(50) encode zstd	Previous tier level name.
	current_tier_level	varchar(50) encode zstd	Current tier level name.
	assign_date	timestamptz encode raw	Date/time when the tier was assiged.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	requals_on_utc_ts	timestamptz encode zstd	when ever tier is upgraded/downgraded, we populated requal date
	lock_date	timestamptz encode zstd	While adjusting tier we can lock the Adjusted_tier for some days(we don't have the implementation )
	locked_by	varchar(50) encode zstd	we populated user_id whos locks the tier.
	reason	varchar(1000) encode zstd	Reason for tier change
	activity_id	varchar(50) encode zstd	Activity ID that caused the tier change
	sub_reason	varchar(1000) ENCODE zstd	Reason for tier change
Indexes
	member_tier_histories_dist_key	ON member_id
	pk_member_tier_histories	ON id
	member_tier_histories_sort_key	ON updated_utc_ts, member_id, assign_date, tier_id
Foreign Keys
	fk_tier_level_histories_member	(member_id) → members(id)
	fk_member_tier_histories_users_created_by	(created_by) → users(id)
	fk_member_tier_histories_users_updated_by	(updated_by) → users(id)
	fk_member_tier_histories_orgs	(org_id) → orgs(id)
	fk_member_tier_histories_member_tiers_id	(tier_id) → member_tiers(id)

Table member_tiers

Stores active tiers within a membership. Each tier has a current tier level stored in member_tier_levels.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
*	loyalty_program_id	varchar(50) encode zstd
*	tier_policy_id	varchar(50) encode raw
*	tier_name	varchar(250) encode zstd	Identifies the name of the tier structure. Most programs track a single tier structure, so this name is usually called something like 'Standard'.
*	member_id	varchar(50) encode raw
	achieved_on_utc_ts	timestamptz encode raw	Records the date/time when the current level of this tier was achieved.
	requal_on_utc_ts	timestamptz encode zstd	Records the date/time when the current level of this tier will requalify.
*	primary_flag	boolean encode raw	Indicates whether the tier is the primary. Domain: [true/false]
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	level_name	varchar(50) encode zstd	Name of the tier level
	tier_policy_level_id	varchar(50) encode zstd	_id of the tier policy level
	lock_date	timestamptz encode zstd	While adjusting tier we can lock the Adjusted_tier for some days(we don't have the implementation )
	locked_by	varchar(50) encode zstd	we populated user_id whos locks the tier.
	activity_id	varchar(50) encode zstd	Activity ID that caused the tier change
	prev_level_name	varchar(50) encode zstd	Name of the previous tier
	reason	varchar(1000) encode zstd	Reason for tier change
	level_number	integer encode zstd	Level number within the tier
	level_threshold	integer encode zstd	threshold field is added to configure Tier policy rules with which we can identify total number of points that are need for upgrading member to next tier level
	sub_reason	varchar(1000) ENCODE zstd
Indexes
	member_loyalty_tiers_dist_key	ON member_id
	pk_member_tiers	ON id
	member_loyalty_tiers_sort_key	ON updated_utc_ts, member_id, id, tier_policy_id, achieved_on_utc_ts
Foreign Keys
	fk_member_loyalty_tiers_members	(member_id) → members(id)
	fk_member_loyalty_tiers_tier_policies	(tier_policy_id) → tier_policies(id)
	fk_member_loyalty_tiers_users_created_by	(created_by) → users(id)
	fk_member_loyalty_tiers_users_updated_by	(updated_by) → users(id)
	fk_member_loyalty_tiers_orgs	(org_id) → orgs(id)
	fk_member_loyalty_tiers_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_member_tier_histories_member_tiers_id	(id) ← member_tier_histories(tier_id)

Table member_transient_activities

Idx	Field Name	Data Type
*	id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
*	loyalty_program_id	varchar(50) encode zstd
*	member_id	varchar(50) encode raw
	loyalty_id	varchar(100) ENCODE zstd
	external_txn_id	varchar(65535) encode zstd
	correlation_id	varchar(65535) encode zstd
	txn_header_id	varchar(65535) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_member_transient_activities_id	ON id
	member_transient_activities_dist_key	ON member_id
	member_transient_activities_sort_key	ON updated_utc_ts, member_id, id
Foreign Keys
	fk_member_transient_activities_members	(member_id) → members(id)
	fk_member_transient_activities_orgs	(org_id) → orgs(id)
	fk_member_transient_activities	(loyalty_program_id) → loyalty_programs(id)
	fk_member_transient_activities_users_created_by	(created_by) → users(id)
	fk_member_transient_activities_users_updated_by	(updated_by) → users(id)
	fk_member_transient_activities_loyatly_activities	(id) → loyalty_activities
	fk_member_transient_activities_member_loyalty+ids	(loyalty_id) → member_loyalty_ids
	fk_member_transient_activities_loyalty_activities_id	(id) → loyalty_activities

Table members

Stores information about a membership within a program.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	enrollment_date	timestamptz encode raw	Date/time of member enrollment.
	enrollment_source	varchar(50) encode zstd	The marketing source (campaign, offer, etc.) which resulted in the enrollment of this member. This usually a code. For example, 'FALL2016-RECDRV' could be a campaign code for a recruitment drive.
	enrollment_location_id	varchar(50) encode raw	the _id of the Location at which the enrollment happened
*	enrollment_channel	varchar(50) encode zstd	Enrollment channel. Domain is configurable. Default domain: [Web/Mobile/Staff/Social/POS]
*	member_status	varchar(50) encode zstd	Status of the membership record. <br> Domain: [Active/Inactive]
	first_name	varchar(400) encode zstd	Member first name.
	city	varchar(200) encode zstd	Member City name.
	state	varchar(50) encode zstd	Member State code/name (depending on country).
	country	varchar(50) encode zstd	Member Country name.
	zip_code	varchar(50) encode zstd	Member zip code or postal code.
	acquisition_date	timestamptz encode zstd	Date/time when member was acquired.
*	acquisition_channel	varchar(50) encode zstd	Member acquisition channel. Default domain: [Web/Mobile/Staff/Social/POS]
	social_id_type	varchar(50) encode zstd	Social handle type. Confiurable domain. Default domain: [LinkedIn/FaceBook/Twitter/Google/Other].
	last_activity_date	timestamptz encode zstd	Last activity date of the member
	referral_code	varchar(100) encode zstd	The referral code used in this enrollment (benefiting the member who referred)
	gender	varchar(50) encode zstd	Gender of the member
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	can_preview	boolean encode raw	Whether rules with canPreview as true and reward policies with canPreview as true can be applied for the member before their effective date
	merge_pending_flag	boolean encode raw	Flag indicating that a merge process with another member is in progress.
	unmerge_pending_flag	boolean	If set to True, indicates the member is part of an ongoing unmerge operation
Indexes
	pk_members	ON id
	members_dist_key	ON id
	members_sort_key	ON updated_utc_ts, id, enrollment_date, enrollment_location_id
Foreign Keys
	fk_member_loyalty_program	(loyalty_program_id) → loyalty_programs(id)
	fk_members_orgs	(org_id) → orgs(id)
	fk_members_locations	(enrollment_location_id) → locations(id)
	fk_members_users_created_by	(created_by) → users(id)
	fk_members_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_account_members_members	(id) ← account_members(member_id)
	fk_accounts_members_active_member_id	(id) ← accounts(active_member_id)
	fk_daily_aggregates_members	(id) ← daily_aggregates(member_id)
	fk_halfyearly_aggregates_members	(id) ← halfyearly_aggregates(member_id)
	fk_lifetime_aggregates_members	(id) ← lifetime_aggregates(member_id)
	fk_loyalty_accrual_items_members	(id) ← loyalty_accrual_items(member_id)
	fk_loyalty_activities_member_member_id	(id) ← loyalty_activities(member_id)
	fk_loyalty_activities_members_orgnl_member_id	(id) ← loyalty_activities(orgnl_member_id)
	fk_loyalty_activity_best_offers_members	(id) ← loyalty_activity_best_offers(member_id)
	fk_loyalty_activity_line_items_members	(id) ← loyalty_activity_line_items(member_id)
	fk_loyalty_activity_tender_items_members	(id) ← loyalty_activity_tender_items(member_id)
	fk_loyalty_redemption_items_members	(id) ← loyalty_redemption_items(member_id)
	fk_account_badges_members	(id) ← member_badges(member_id)
	fk_member_loyalty_ids_members	(id) ← member_loyalty_ids(member_id)
	fk_member_notes_members	(id) ← member_notes(member_id)
	fk_member_offer_usage_histories_members	(id) ← member_offer_usage_histories(member_id)
	fk_member_offers_members	(id) ← member_offers(member_id)
	fk_member_packaged_redemptions_members	(id) ← member_packaged_redemptions(member_id)
	fk_member_preference_members	(id) ← member_preferences(member_id)
	fk_account_purses_member	(id) ← member_purses(member_id)
	fk_member_referrals_members	(id) ← member_referrals(referrer_member_id)
	fk_member_reward_usage_history_member_id	(id) ← member_reward_usage_histories(member_id)
	fk_member_rewards_members	(id) ← member_rewards(member_id)
	fk_member_segments_members	(id) ← member_segments(member_id)
	fk_member_streaks_members_id	(id) ← member_streaks(member_id)
	fk_tier_level_histories_member	(id) ← member_tier_histories(member_id)
	fk_member_loyalty_tiers_members	(id) ← member_tiers(member_id)
	fk_member_transient_activities_members	(id) ← member_transient_activities(member_id)
	fk_member_pii_members	(id) ← members_pii
	fk_members_streak_members	(id) ← members_streak(member_id)
	fk_member_streak_goals_members	(id) ← members_streak_goals(member_id)
	fk_merge_histories_members	(id) ← merge_histories(survivor_id)
	fk_merged_members_members	(id) ← merged_members(survivor_id)
	fk_monthly_aggregates_members	(id) ← monthly_aggregates(member_id)
	fk_promo_codes_members	(id) ← promo_codes(member_id)
	fk_quarterly_aggregates_members	(id) ← quarterly_aggregates(member_id)
	fk_rule_match_members_member_id	(id) ← rule_match(member_id)
	fk_rule_match_allocation_members_member_id	(id) ← rule_match_allocations(member_id)
	fk_rule_match_lines_members_member_id	(id) ← rule_match_lines(member_id)
	fk_weekly_aggregates_members	(id) ← weekly_aggregates(member_id)
	fk_yearly_aggregates_members	(id) ← yearly_aggregates(member_id)

Table members_pii

Stores personally identifiable information.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
*	loyalty_program_id	varchar(50) encode zstd
	last_name	varchar(400) encode zstd	Member last name.
	birth_date	timestamptz encode zstd	Member birth date.
	address_line	varchar(250) encode zstd	Address line for member physical address.
	email	varchar(65535) encode zstd	Member primary contact email address.
	phone	varchar(50) encode zstd	Member primary contact phone number.
	social_id	varchar(50) encode zstd	Member Social handle.
	secondary_email	varchar(65535) encode zstd
	address_2	varchar(65535) encode zstd
	mobile_number	varchar(65535) encode zstd
	facebook_id	varchar(65535) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_member_pii	ON id
	member_pii_dist_key	ON id
	member_pii_sort_key	ON updated_utc_ts, id
Foreign Keys
	fk_member_pii_members	(id) → members
Referring Foreign Keys
	fk_account_members_members_pii_member_id	(id) ← account_members(member_id)
	fk_accounts_members_pii_member_id	(id) ← accounts(active_member_id)

Table members_streak

Idx	Field Name	Data Type	Description
*	id	varchar(50)	readonly unique Unique ID
*	streak_policy_id	varchar(50) encode zstd	_id of the Reward Policy associated with this offer.
*	member_id	varchar(50) encode zstd	Member's ID.
	started_date	timestamptz encode zstd
	changed_date	timestamptz encode zstd
	ended_date	timestamptz encode zstd
	status	varchar(50) encode zstd
	value	decimal(18,6)
	current_goal	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'::character varying
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	control_group	varchar(50) ENCODE zstd
Indexes
	Sorting	ON id, streak_policy_id, member_id, updated_utc_ts
	Distribution	ON member_id
	pk_members_streaks	ON id
Foreign Keys
	fk_members_streak_members	(member_id) → members(id)
	fk_members_streak_updated_by	(updated_by) → users(id)
	fk_members_streak_users	(created_by) → users(id)
	fk_members_streak_streak_policies	(streak_policy_id) → streak_policies(id)
Referring Foreign Keys
	fk_streak_event_logs_members_streak_id	(id) ← members_streak_event_logs(member_streak_id)
	fk_members_streak_goals_members_streak	(id) ← members_streak_goals(member_streak_id)

Table members_streak_event_logs

Idx	Field Name	Data Type
*	id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
	loyalty_activity_id	varchar(50) encode zstd
	event_id	varchar(50) encode zstd
*	event_name	varchar(50) encode zstd
	event_date	timestamptz encode zstd
	member_streak_id	varchar(50) encode zstd
	streak_policy_id	varchar(50) encode zstd
	goal_id	varchar(50) encode zstd
	goal_name	varchar(50) encode zstd
	goal_prev_value	decimal(18,6) encode zstd
	goal_new_value	decimal(18,6) encode zstd
	goal_prev_target	decimal(18,6) encode zstd
	goal_new_target	decimal(18,6) encode zstd
	goal_prev_status	varchar(50) encode zstd
	goal_new_status	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_streak_event_logs	ON id
	sort_streak_event_logs	ON updated_utc_ts, loyalty_activity_id, streak_policy_id
	cluster_members_streak_event_logs	ON loyalty_activity_id
Foreign Keys
	fk_streak_event_logs_loyalty_activities_id	(loyalty_activity_id) → loyalty_activities(id)
	fk_streak_event_logs_members_streak_id	(member_streak_id) → members_streak(id)
	fk_streak_event_logs_streak_policies_id	(streak_policy_id) → streak_policies(id)
	fk_streak_event_logs_members_streak_goals_name	(goal_name) → members_streak_goals
	fk_members_streak_event_logs_users_created_by	(created_by) → users(id)
	fk_members_streak_event_logs_users_updated_by	(updated_by) → users(id)
	fk_members_streak_event_logs_orgs	(org_id) → orgs(id)

Table members_streak_goals

Idx	Field Name	Data Type
*	id	varchar(50) ENCODE raw
*	member_streak_id	varchar(50) ENCODE zstd
*	streak_policy_id	varchar(50) ENCODE zstd
*	member_id	varchar(50) ENCODE zstd
*	goal_name	varchar(50) ENCODE zstd
	started_date	timestamptz encode zstd
	changed_date	timestamptz encode zstd
	ended_date	timestamptz encode zstd
*	status	varchar(50) ENCODE zstd
*	"value"	decimal(18,6) ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamptz ENCODE raw
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
	target	decimal(18,6) ENCODE zstd
Indexes
	pk_member_streak_goals_id	ON id
	dist_member_streak_goals	ON member_id
	sort_member_streak_goals	ON updated_utc_ts, member_streak_id, member_id
	unq_members_streak_goals_goal_name	ON goal_name
Foreign Keys
	fk_member_streak_goals_created_by	(created_by) → users(id)
	fk_member_streak_goals_updated_by	(updated_by) → users(id)
	fk_member_streak_goals_members	(member_id) → members(id)
	fk_members_streak_goals_streak_policies	(streak_policy_id) → streak_policies(id)
	fk_members_streak_goals_members_streak	(member_streak_id) → members_streak(id)
Referring Foreign Keys
	fk_streak_event_logs_members_streak_goals_name	(goal_name) ← members_streak_event_logs

Table merge_histories

Idx	Field Name	Data Type	Description
*	id	varchar(50)	readonly unique Unique ID
*	victim_id	varchar(50) encode zstd
*	survivor_id	varchar(50) encode zstd	Reference to "_id" field from Member
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	merge_req_date	timestamptz encode zstd	Merge processing the data
	merge_completion_date	timestamptz encode zstd	Merge completed date
	unmerge_req_date	timestamptz encode zstd	It identicates the date when survivor request the unmerge process
	unmerge_completion_date	timestamptz encode zstd	It identicates the date when survivor request the unmerge process completed
*	merge_id	varchar(50) encode zstd	_id of the Merge operation, Updated for the respective victim's records
	ext_merge_id	varchar(50) encode zstd	This field is the unique mergeId that patron is generating.
	merge_status	varchar(50) encode zstd	Status of the Merge (eg: Pending, Completed)
	unmerge_status	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'::character varying
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	Sorting	ON updated_utc_ts, victim_id, survivor_id
	Distribution	ON survivor_id
	pk_merge_histories_id	ON id
Foreign Keys
	fk_merge_histories_members	(survivor_id) → members(id)
	fk_merge_histories_orgs	(org_id) → orgs(id)
	fk_merge_histories_users_created_by	(created_by) → users(id)
	fk_merge_histories_users_updated_by	(updated_by) → users(id)

Table merged_members

Idx	Field Name	Data Type	Description
*	victim_id	varchar(50) encode raw	readonly Unique value for every record
	survivor_id	varchar(50) encode raw	readonly Unique value for every record
*	loyalty_program_id	varchar(50) encode zstd	required the _id of the Program in which the member is enrolled
	country	varchar(50) encode zstd	Country of member
	customer_key	varchar(50) encode zstd	Customer Key of member
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	victim_member_status	varchar(50) encode zstd	Status Codes
	victim_ip_code	decimal(18,6) ENCODE RAW
	merge_status	varchar(50) encode zstd	Status of the Merge (eg: Pending, Completed)
	merge_pending_flag	boolean encode raw	Flag indicating that a merge process with another member is in progress.
	merge_id	varchar(50) encode zstd	_id of the Merge operation, Updated for the respective victims records.
	unmerge_pending_flag	boolean	If set to True, indicates the member is part of an ongoing unmerge operation
	merge_date	timestamptz encode zstd	it identicates the merge date when victim merge with survivor
	ext_merge_id	varchar(50) encode zstd	This field is the unique mergeId that patron is generating.
Indexes
	pk_merged_members	ON victim_id
	merged_members_dist_key	ON survivor_id
	merged_members_sort_key	ON updated_utc_ts, survivor_id, victim_id
Foreign Keys
	fk_merged_members_members	(survivor_id) → members(id)
	fk_merged_members_users_created_by	(created_by) → users(id)
	fk_merged_members_users_updated_by	(updated_by) → users(id)
	fk_merged_members_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table monthly_aggregates

The total activity points accumulated throughout the month are saved in a collection known as monthly aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	month	integer ENCODE zstd	Contains the value of the current month number in the year
	year	integer ENCODE zstd	Consists of the value of current year
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	month_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the month
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_monthly_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_monthly_aggregates_orgs	(org_id) → users(id)
	fk_monthly_aggregates_members	(member_id) → members(id)
	fk_monthly_aggregates_users_created_by	(created_by) → users(id)
	fk_monthly_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table namedlists

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	required unique Unique ID
	name	varchar(65535) encode zstd	name of the named list
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	type	varchar(50) encode zstd	type of classification ex: location, product etc.
	refresh_date	timestamptz encode zstd	It is update date
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	refreshed_by	varchar(50) encode zstd	person who updated
	count	integer ENCODE zstd	Returns list count
Indexes
	pk_namedlists	ON id
	namedlists_sort_key	ON id
Foreign Keys
	fk_namedlists_users_updated_by	(updated_by) → users(id)
	fk_namedlists_users_created_by	(created_by) → users(id)
	fk_namedlists_orgs	(org_id) → orgs(id)
Referring Foreign Keys
	fk_namedlists_data_namedlists	(id) ← namedlists_data
	fk_program_rules_namedlists_locations_namedlist_id	(id) ← program_rules(locations_namedlist_id)
	fk_program_rules_namedlists_primary_products_namedlist_id	(id) ← program_rules(primary_products_namedlist_id)
	fk_program_rules_namedlists_secondary_products_namedlist_id	(id) ← program_rules(secondary_products_namedlist_id)
	fk_program_rules_namedlists_tertiary_products_namedlist_id	(id) ← program_rules(tertiary_products_namedlist_id)
	fk_program_rules_namedlists_mandatory_products_namedlist_id	(id) ← program_rules(mandatory_products_namedlist_id)
	fk_reward_policies_namedlists_locations_namedlist_id	(id) ← reward_policies(locations_namedlist_id)
	fk_reward_policies_namedlists_primary_products_namedlist_id	(id) ← reward_policies(primary_products_namedlist_id)
	fk_reward_policies_namedlists_secondary_products_namedlist_id	(id) ← reward_policies(secondary_products_namedlist_id)
	fk_reward_policies_namedlists_tertiary_product_namedlist_id	(id) ← reward_policies(tertiary_products_namedlist_id)
	fk_reward_policies_namedlists_mandatory_products_namedlist_id	(id) ← reward_policies(mandatory_products_namedlist_id)
	fk_reward_policies_namedlists_excluded_products_namedlist_id	(id) ← reward_policies(excluded_products_namedlist_id)
	fk_reward_upc_mapping_namedlists	(id) ← reward_upc_mapping(namedlist_id)

Table namedlists_data

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	required unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	model_id	varchar(50) encode raw
	model_type	varchar(50) encode raw	Name of Associated data Collection on which named list is created
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_namedlists_data	ON id, model_id
	namedlists_data_sort_key	ON updated_utc_ts, model_type, model_id
Foreign Keys
	fk_namedlists_data_namedlists	(id) → namedlists
	fk_namedlists_data_locations	(model_id) → locations(id)
	fk_namedlists_data_products	(model_id) → products(id)
	fk_namedlists_data_program_rules	(model_id) → program_rules(id)
	fk_namedlists_data_reward_policies	(model_id) → reward_policies(id)

Table orgs

Tenant organization record.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
	org_name	varchar(50) encode zstd	Tenant organization name. This is usually a company name.
	org_description	varchar(1000) encode zstd	Tenant organization description.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_orgs	ON id
	orgs_sort_key	ON id
Foreign Keys
	fk_orgs_users_created_by	(created_by) → users(id)
	fk_orgs_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_account_members_orgs_org_id	(id) ← account_members(org_id)
	fk_accounts_orgs	(id) ← accounts(org_id)
	fk_daily_aggregates_org	(id) ← daily_aggregates(org_id)
	fk_enums_orgs	(id) ← enums(org_id)
	fk_halfyearly_aggregates_orgs	(id) ← halfyearly_aggregates(org_id)
	fk_lifetime_aggregates_orgs	(id) ← lifetime_aggregates(org_id)
	fk_location_overrides_orgs	(id) ← location_overrides(org_id)
	fk_location_org	(id) ← locations(org_id)
	fk_loyalty_accrual_items_orgs	(id) ← loyalty_accrual_items(org_id)
	fk_loyalty_activities_orgs	(id) ← loyalty_activities(org_id)
	fk_loyalty_activity_best_offers_orgs	(id) ← loyalty_activity_best_offers(org_id)
	fk_loyalty_activity_line_items_orgs	(id) ← loyalty_activity_line_items(org_id)
	fk_loyalty_activity_tender_items_orgs	(id) ← loyalty_activity_tender_items(org_id)
	fk_loyalty_cards_orgs	(id) ← loyalty_cards(org_id)
	fk_loyalty_programs_orgs	(id) ← loyalty_programs(org_id)
	fk_loyalty_redemption_items_orgs	(id) ← loyalty_redemption_items(org_id)
	fk_member_badges_orgs	(id) ← member_badges(org_id)
	fk_member_loyalty_ids_orgs	(id) ← member_loyalty_ids(org_id)
	fk_member_notes_orgs	(id) ← member_notes(org_id)
	fk_member_offer_usage_histories_orgs	(id) ← member_offer_usage_histories(org_id)
	fk_member_offers_orgs	(id) ← member_offers(org_id)
	fk_member_packaged_redemptions_orgs	(id) ← member_packaged_redemptions(org_id)
	fk_member_preferences_orgs	(id) ← member_preferences(org_id)
	fk_member_purses_orgs	(id) ← member_purses(org_id)
	fk_member_referrals_orgs	(id) ← member_referrals(org_id)
	fk_member_reward_usage_histories_orgs	(id) ← member_reward_usage_histories(org_id)
	fk_member_rewards_orgs	(id) ← member_rewards(org_id)
	fk_member_segments_orgs	(id) ← member_segments(org_id)
	fk_member_tier_histories_orgs	(id) ← member_tier_histories(org_id)
	fk_member_loyalty_tiers_orgs	(id) ← member_tiers(org_id)
	fk_member_transient_activities_orgs	(id) ← member_transient_activities(org_id)
	fk_members_orgs	(id) ← members(org_id)
	fk_members_streak_event_logs_orgs	(id) ← members_streak_event_logs(org_id)
	fk_merge_histories_orgs	(id) ← merge_histories(org_id)
	fk_namedlists_orgs	(id) ← namedlists(org_id)
	fk_partners_orgs	(id) ← partners(org_id)
	fk_products_orgs	(id) ← products(org_id)
	fk_program_rule_actions_orgs	(id) ← program_rule_actions(org_id)
	fk_program_rule_availability_orgs	(id) ← program_rule_availability(org_id)
	fk_program_rule_conditions_orgs	(id) ← program_rule_conditions(org_id)
	fk_program_rule_control_groups_orgs	(id) ← program_rule_control_groups(org_id)
	fk_program_rule_folders_orgs	(id) ← program_rule_folders(org_id)
	fk_program_rule_limits_orgs	(id) ← program_rule_limits(org_id)
	fk_program_rule_locations_orgs	(id) ← program_rule_locations(org_id)
	fk_program_rule_segments_0rgs_org_id	(id) ← program_rule_mandatory_segments(org_id)
	fk_program_rule_products_orgs	(id) ← program_rule_products(org_id)
	fk_program_rule_segments_orgs	(id) ← program_rule_segments(org_id)
	fk_program_rule_tier_levels_orgs	(id) ← program_rule_tier_levels(org_id)
	fk_program_rules_orgs	(id) ← program_rules(org_id)
	fk_promo_code_defs_orgs	(id) ← promo_code_defs(org_id)
	fk_promo_codes_orgs	(id) ← promo_codes(org_id)
	fk_purse_policies_orgs	(id) ← purse_policies(org_id)
	fk_quarterly_aggregates_orgs	(id) ← quarterly_aggregates(org_id)
	fk_reward_policies_orgs	(id) ← reward_policies(org_id)
	fk_reward_policy_availability_orgs	(id) ← reward_policy_availability(org_id)
	fk_reward_policy_control_groups_orgs	(id) ← reward_policy_control_groups(org_id)
	fk_reward_policy_locations_orgs	(id) ← reward_policy_locations(org_id)
	fk_reward_policy_mandatory_segments_orgs_org_id	(id) ← reward_policy_mandatory_segments(org_id)
	fk_reward_policy_products_orgs	(id) ← reward_policy_products(org_id)
	fk_reward_policy_segments_orgs	(id) ← reward_policy_segments(org_id)
	fk_reward_policy_tier_levels_orgs	(id) ← reward_policy_tier_levels(org_id)
	fk_reward_upc_mapping_orgs	(id) ← reward_upc_mapping(org_id)
	fk_segments_orgs	(id) ← segments(org_id)
	fk_streak_goal_policies_orgs	(id) ← streak_goal_policies(org_id)
	fk_streak_policies_orgs	(id) ← streak_policies(org_id)
	fk_streak_policy_optin_segments_orgs	(id) ← streak_policy_optin_segments(org_id)
	fk_tier_policies_orgs	(id) ← tier_policies(org_id)
	fk_tier_policy_levels_orgs	(id) ← tier_policy_levels(org_id)
	fk_weekly_aggregates_orgs	(id) ← weekly_aggregates(org_id)
	fk_yearly_aggregates_orgs	(id) ← yearly_aggregates(org_id)

Table partners

Stores a list of all registered loyalty coalition partners for the program. This enables linking accounts across organizations to earn rewards outside the hosting organization.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	unique The id of the record
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Reference to Program
	partner_name	varchar(250) encode zstd	Partner company name.
	partner_code	varchar(65535) encode zstd	Unique partner code that can beused in Activity to validate partner transactions and route them to the correct rules.
	partner_status	varchar(250) encode zstd	Partner status. Domain: [Active/Inactive]
	contact_first_name	varchar(250) encode zstd	First name of contact representing partner.
	contact_last_name	varchar(250) encode zstd	Last name of contact representing partner.
	contact_email	varchar(65535) encode zstd	Email of contact representing partner.
	contact_phone	varchar(50) encode zstd	Phone of contact representing partner.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_partners	ON id
	partners_sort_key	ON id
Foreign Keys
	fk_partners_users_created_by	(created_by) → users(id)
	fk_partners_users_updated_by	(updated_by) → users(id)
	fk_partners_orgs	(org_id) → orgs(id)
	fk_partners_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_partners_loyalty_program	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_loyalty_activities_partners	(id) ← loyalty_activities(partner_id)

Table product_types

Stores the product types like "Primary, Secondary, Tertiory"

Idx	Field Name	Data Type	Description
	products	varchar(50) encode zstd	Default to products
*	product_type	varchar(50) encode zstd
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_product_types	ON product_type

Table products

Stores item master describing products available for sale in the enterprise.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	product_name	varchar(200) encode zstd	Product short name.
	product_description	varchar(1000) encode zstd	Product detailed description.
	product_category	varchar(50) encode zstd	Product category. Domain is usually up to item master of the tenant.
	product_sub_category	varchar(50) encode zstd	Product sub-category. Domain is usually up to item master of the tenant.
	product_style	varchar(50) encode zstd	Product style name. Domain is usually up to item master of the tenant.
*	product_sku	varchar(75) encode zstd	Stock keeping unit number (SKU). This is a unique identifier of the product within the tenant master.
	product_upc	varchar(65535) encode zstd	Standard UPC code.
	product_internal_code	varchar(65535) encode zstd	Internal codes are sometimes used to additionally distinguish products for certain situations.
	product_cost	decimal(18,6) ENCODE RAW	This is a generic field that can be used to attribute cost in points, or any other type of monetary cost.
	effective_date	timestamptz encode zstd	Reward effective date. It can not be redeemed before this date/time.
	expiration_date	timestamptz encode zstd	Expiration date associated with the product.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	product_url	varchar(65535) encode zstd	URL to a picture resource for this product
Indexes
	pk_products	ON id
	products_sort_key	ON updated_utc_ts, id
Foreign Keys
	fk_products_orgs	(org_id) → orgs(id)
	fk_products_users_created_by	(created_by) → users(id)
	fk_products_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_loyalty_activity_line_items_products	(id) ← loyalty_activity_line_items(product_id)
	fk_namedlists_data_products	(id) ← namedlists_data(model_id)
	fk_program_rule_products_products	(id) ← program_rule_products(product_id)
	fk_reward_policies_products_products	(id) ← reward_policy_products(product_id)
	fk_rule_match_allocation_products_product_id	(id) ← rule_match_allocations(product_id)
	fk_rule_match_lines_products_product_id	(id) ← rule_match_lines(product_id)

Table program_rule_actions

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	unique id of the rule
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	action_id	varchar(50) encode raw	Unique ID
	rule_name	varchar(250) encode zstd	Name of the rule.
	action_name	varchar(250) encode zstd	Name of the Action
	action_type	varchar(50) encode zstd	Set of objects referring to action schema.
	params	varchar(65535) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	program_rule_actions_sort_key	ON updated_utc_ts, rule_id
Foreign Keys
	fk_program_rule_actions_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_actions_users_created_by	(created_by) → users(id)
	fk_program_rule_actions_users_updated_by	(updated_by) → users(id)
	fk_program_rule_actions_orgs	(org_id) → orgs(id)
	fk_program_rule_actions_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_availability

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	unique id
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID.
*	day_availability	varchar(50) encode zstd	Indicates the day of the week. Ex., Sunday, Monday, etc.
	start_hours	integer encode zstd	Indicates the hours part of the timestamp from which the rule will become active in a given day of the week.
	start_mins	integer encode zstd	Indicates the minutes part of the timestamp from which the rule will become active in a given day of the week.
	end_hours	integer encode zstd	Indicates the hours part of the timestamp from which the rule will become inactive in a given day of the week.
	end_mins	integer encode zstd	Indicates the minutes part of the timestamp from which the rule will become inactive in a given day of the week.
	is_enabled	boolean encode raw	Indicates if the rule will be active in a given day of the week.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_availability	ON rule_id, day_availability
	program_rule_availability_sort_key	ON rule_id
Foreign Keys
	fk_program_rule_availability_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_availability_created_by	(created_by) → users(id)
	fk_program_rule_availability_updated_by	(updated_by) → users(id)
	fk_program_rule_availability_orgs	(org_id) → orgs(id)
	fk_program_rule_availability_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_conditions

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID.
*	condition_id	varchar(50) encode raw
	rule_name	varchar(250) encode zstd	Name of the rule.
	condition_name	varchar(250) encode zstd	Name of the condition.
	logic_json	varchar(65535) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_conditions_rule_id	ON rule_id, condition_id
	program_rule_conditions_sort_key	ON updated_utc_ts, rule_id, condition_id
Foreign Keys
	fk_program_rule_conditions_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_conditions_users_created_by	(created_by) → users(id)
	fk_program_rule_conditions_users_updated_by	(updated_by) → users(id)
	fk_program_rule_conditions_orgs	(org_id) → orgs(id)
	fk_program_rule_conditions_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_control_groups

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	Unique ID of the program rule
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	control_group_id	varchar(50) encode zstd	_id of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	program_rule_control_groups_sort_key	ON updated_utc_ts, rule_id, control_group_id
	pk_program_rule_control_groups_rule_id	ON rule_id, control_group_id
Foreign Keys
	fk_program_rule_control_groups_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_control_groups_segments	(control_group_id) → segments(id)
	fk_program_rule_control_groups_users_updated_by	(updated_by) → users(id)
	fk_program_rule_control_groups_orgs	(org_id) → orgs(id)
	fk_program_rule_control_groups	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rule_control_groups_users_created_by	(created_by) → users(id)

Table program_rule_folders

Stores information about folders in which rules are placed to keep the program organized.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
	parent_folder_id	varchar(50) encode zstd	Parent folder ID.
	folder_name	varchar(250) encode zstd	Folder name. For example, 'Base Rules', or 'Bonus Promotions'.
	folder_description	varchar(65535) encode zstd	Description of the contents of the folder.
	is_promo_folder	boolean encode raw	Indicates whether the folder is used for Promotions or other types of rules.
	is_trash	boolean encode raw	Determines whether the folder is a trash folder. Trash folders are used to automatically sweep expired rules, to reduce clutter.
	priority	integer encode zstd	Folder priority used during program execution.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	is_streak_folder	boolean	Indicates if the rules present in a folder are of a streak.
	streak_policy_id	varchar(50) encode zstd	Object id of the streak policy.
Indexes
	pk_program_rule_folders	ON id
	program_rule_folders_sort_key	ON id
Foreign Keys
	fk_program_rule_folders_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rule_folders_program_rule_folders	(parent_folder_id) → program_rule_folders(id)
	fk_program_rule_folders_orgs	(org_id) → orgs(id)
	fk_program_rule_folders_users_updated_by	(updated_by) → users(id)
	fk_program_rule_folders_users_created_by	(created_by) → users(id)
Referring Foreign Keys
	fk_program_rule_folders_program_rule_folders	(id) ← program_rule_folders(parent_folder_id)
	fk_program_rules_program_rules_folder	(id) ← program_rules(rule_parent_folder_id)

Table program_rule_limits

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	Unique Id of the program_rule_limits
*	org_id	varchar(50) encode zstd	Unique ID of the Organization
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	rule_id	varchar(50) encode raw	Unique ID of the program rule
	redemptions	integer encode zstd	Number of times the rule got triggered
	available_redemptions	integer encode zstd	Number of times the rule can be triggered
	budget_used	decimal(18,6) ENCODE RAW	Budged availed due to the program rule (Ex., Number of points accrued due to a bonus promotion)
	available_budget	decimal(18,6) ENCODE RAW	The amount of points that is available.
*	effective_from	timestamptz encode zstd	Start date of the rule
	can_preview	boolean encode raw	Status of can_preview flag for the program rule
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	effective_to	timestamptz encode zstd	End date of the program rule
	is_trashed	boolean encode raw	This flag will be set to TRUE if the program rule is moved to trash folder.
Indexes
	pk_program_rule_limits_id	ON id
	program_rule_limits_sort_key	ON updated_utc_ts, rule_id
	program_rule_limits_dist_key	ON rule_id
Foreign Keys
	fk_program_rule_limits_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_limits_orgs	(org_id) → orgs(id)
	fk_program_rule_limits_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rule_limits_users_created_by	(created_by) → users(id)
	fk_program_rule_limits_users_updated_by	(updated_by) → users(id)

Table program_rule_locations

Stores locations which are included/excluded for the program rule.

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID.
*	location_id	varchar(50) encode raw	_id of the location
	created_by	varchar(50) encode zstd
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_locations	ON rule_id, location_id
	program_rule_locations_sort_key	ON updated_utc_ts, rule_id, location_id
Foreign Keys
	fk_program_rule_locations_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_locations_locations	(location_id) → locations(id)
	fk_program_rule_locations_created_by	(created_by) → users(id)
	fk_program_rule_locations_updated_by	(updated_by) → users(id)
	fk_program_rule_locations_orgs	(org_id) → orgs(id)
	fk_program_rule_locations_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_mandatory_segments

Stores mandate segments which are included for the program rules.

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID.
*	mandatory_segment_id	varchar(50) encode zstd	Array of segment ids which are mandatory for this Rule.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_mandatory_segments	ON rule_id, mandatory_segment_id
	program_rule_mandatory_segments_sort_key	ON rule_id
Foreign Keys
	fk_program_rule_segments_program_rules_rule_id	(rule_id) → program_rules(id)
	fk_program_rule_segments_member_segments_segment_id	(mandatory_segment_id) → member_segments(id)
	fk_program_rule_segments_0rgs_org_id	(org_id) → orgs(id)
	fk_program_rule_segments_loyalty_programs_loyalty_program_id	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rule_segments_users_created_by	(created_by) → users(id)
	fk_program_rule_segments_users_updated_by	(updated_by) → users(id)

Table program_rule_member_statuses

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID.
*	member_status	varchar(50) encode zstd	Member statuses which is applicable for this offer
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_member_statuses	ON rule_id, member_status
	program_rule_member_statuses_sort_key	ON updated_utc_ts, rule_id
Foreign Keys
	fk_program_rule_member_statuses_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_member_statuses_users_created_by	(created_by) → users(id)
	fk_program_rule_member_statuses_users_updated_by	(updated_by) → users(id)

Table program_rule_products

Stores primary, secondary and tertiary product associations within the rules which govern the program.

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	product_type	varchar(50) encode zstd	Product type indicates which product group type is being described - primary, secondary or tertiary. Domain: [primary/secondary/tertiary]
*	product_id	varchar(50) encode raw	_id of the product SKU
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_products	ON rule_id, product_type, product_id
	program_rule_products_sort_key	ON updated_utc_ts, rule_id, product_id
Foreign Keys
	fk_program_rule_products_products	(product_id) → products(id)
	fk_program_rule_products_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_products_users_updated_by	(updated_by) → users(id)
	fk_program_rule_products_users_created_by	(created_by) → users(id)
	fk_program_rule_products_orgs	(org_id) → orgs(id)
	fk_program_rule_products_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_segments

Stores segments which are included for the program rules.

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	unique id
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program's ID
*	segment_id	varchar(50) encode raw	_id of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_program_rule_segments	ON rule_id, segment_id
	program_rule_segments_sort_key	ON updated_utc_ts, rule_id, segment_id
Foreign Keys
	fk_program_rule_segments_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_segments_segments	(segment_id) → segments(id)
	fk_program_rule_segments_users_created_by	(created_by) → users(id)
	fk_program_rule_segments_users_updated_by	(updated_by) → users(id)
	fk_program_rule_segments_orgs	(org_id) → orgs(id)
	fk_program_rule_segments_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table program_rule_tier_levels

Idx	Field Name	Data Type	Description
*	rule_id	varchar(50) encode raw	unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	tier_policy_id	varchar(50) encode raw	_id of the Tier Policy
*	level_name	varchar(50) encode zstd	Current level. All levels comply with the Tier Policy
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	program_rule_tier_levels_sort_key	ON updated_utc_ts, rule_id, tier_policy_id
	pk_program_rule_tier_levels_rule_id	ON rule_id, tier_policy_id, level_name
Foreign Keys
	fk_program_rule_tier_levels_program_rules	(rule_id) → program_rules(id)
	fk_program_rule_tier_levels_tier_policies	(tier_policy_id) → tier_policies(id)
	fk_program_rule_tier_levels_orgs	(org_id) → orgs(id)
	fk_program_rule_tier_levels_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rule_tier_levels_users_created_by	(created_by) → users(id)
	fk_program_rule_tier_levels_users_updated_by	(updated_by) → users(id)

Table program_rules

Stores program rules which reprsent the base rules, as well as the promotions (for points) which are available in the program.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	rule_name	varchar(250) encode zstd	The name of a rule. Rule names can be for base rules, and also for promotions - e.g. 'Base Accrual', 'Double-Points Day'.
	rule_description	varchar(65535) encode zstd	Detailed description for the Rule.
	program_rule_priority	integer encode zstd	Numeric priority of the rule in the program.
	rule_parent_folder_id	varchar(50) encode zstd	The id of a folder which contains this rule. It is possible that the rule is at the top level of the program, in which case this field is NULL.
*	effective_from_utc_ts	timestamptz encode zstd	Date/time when the rule becomes effective.
	effective_to_utc_ts	timestamptz encode zstd	Date/time when the rule expires.
	rule_execution_type	varchar(50) encode zstd	Generic or mix and match or combo
*	bonus_type	varchar(50) encode zstd	Type of bonus points or multiplier
	bonus_value	decimal(18,6) ENCODE RAW	Points awarded when the rule is triggered
	primary_expected_qty	integer encode zstd	Indicates the minimum quantity to be purchased which are part of primary products group for the rule to trigger.
	secondary_expected_qty	integer encode zstd	Indicates the minimum quantity to be purchased which are part of secondary products group for the rule to trigger.
	tertiary_expected_qty	integer encode zstd	Indicates the minimum quantity to be purchased which are part of tertiary products group for the rule to trigger.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	mandatory_expected_qty	integer encode zstd	Indicates the minimum quantity to be purchased which are part of mandatory products group for the rule to trigger.
	locations_namedlist_id	varchar(50) encode zstd	Name of the related locations named list
	primary_products_namedlist_id	varchar(50) encode zstd	Name of the related primary products named list
	secondary_products_namedlist_id	varchar(50) encode zstd	Name of the related secondary products named list
	tertiary_products_namedlist_id	varchar(50) encode zstd	Name of the related tertiary products named list
	mandatory_products_namedlist_id	varchar(50) encode zstd	Name of the related products named list
	can_preview	boolean encode raw	Status of can_preview flag for the program rule
	associated_id	varchar(50) encode zstd
	day_limit	integer encode zstd	Number of times a rule can be applied in a day at a member level
	week_limit	integer encode zstd	Number of times a rule can be applied in a week at a member level
	offer_limit	integer encode zstd	Number of times a rule can be applied during the effective period at a member level
	cnt_limit	integer encode zstd	Number of times a rule can applied at a program rule level
	budget	decimal(18,6) ENCODE RAW	Total number of points that can be awarded at a program rule level
	cool_off_period	integer encode zstd	cool_off_period - Indicates the time period for which the respective program rule cannot be triggered for the same member.
	is_gate_rule	boolean encode raw	Indicated if the respective program rule is a gate rule.
	streak_policy_id	varchar(50) ENCODE zstd	'" Indicates if the respective rule is a Gate rule"'
	goal_name	varchar(250) ENCODE zstd	Name of the goal associated with the streak.
	streak_rule_type	varchar(250) ENCODE zstd
Indexes
	pk_program_rules	ON id
	program_rules_sort_key	ON id
Foreign Keys
	fk_program_rules_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_program_rules_program_rules_folder	(rule_parent_folder_id) → program_rule_folders(id)
	fk_program_rules_users_updated_by	(updated_by) → users(id)
	fk_program_rules_users_created_by	(created_by) → users(id)
	fk_program_rules_orgs	(org_id) → orgs(id)
	fk_program_rules_namedlists_locations_namedlist_id	(locations_namedlist_id) → namedlists(id)
	fk_program_rules_namedlists_primary_products_namedlist_id	(primary_products_namedlist_id) → namedlists(id)
	fk_program_rules_namedlists_secondary_products_namedlist_id	(secondary_products_namedlist_id) → namedlists(id)
	fk_program_rules_namedlists_tertiary_products_namedlist_id	(tertiary_products_namedlist_id) → namedlists(id)
	fk_program_rules_namedlists_mandatory_products_namedlist_id	(mandatory_products_namedlist_id) → namedlists(id)
Referring Foreign Keys
	fk_loyalty_accrual_items_program_rules	(id) ← loyalty_accrual_items(rule_id)
	fk_loyalty_redemption_items_program_rules	(id) ← loyalty_redemption_items(rule_id)
	fk_member_offer_usage_histories_program_rules	(id) ← member_offer_usage_histories(rule_id)
	fk_member_offers_program_rules_id	(id) ← member_offers(program_rule_id)
	fk_member_packaged_redemptions_program_rules	(id) ← member_packaged_redemptions(rule_id)
	fk_member_reward_usage_history_rule_id	(id) ← member_reward_usage_histories(rule_id)
	fk_member_rewards_program_rules_id	(id) ← member_rewards(program_rule_id)
	fk_namedlists_data_program_rules	(id) ← namedlists_data(model_id)
	fk_program_rule_actions_program_rules	(id) ← program_rule_actions(rule_id)
	fk_program_rule_availability_program_rules	(id) ← program_rule_availability(rule_id)
	fk_program_rule_conditions_program_rules	(id) ← program_rule_conditions(rule_id)
	fk_program_rule_control_groups_program_rules	(id) ← program_rule_control_groups(rule_id)
	fk_program_rule_limits_program_rules	(id) ← program_rule_limits(rule_id)
	fk_program_rule_locations_program_rules	(id) ← program_rule_locations(rule_id)
	fk_program_rule_segments_program_rules_rule_id	(id) ← program_rule_mandatory_segments(rule_id)
	fk_program_rule_member_statuses_program_rules	(id) ← program_rule_member_statuses(rule_id)
	fk_program_rule_products_program_rules	(id) ← program_rule_products(rule_id)
	fk_program_rule_segments_program_rules	(id) ← program_rule_segments(rule_id)
	fk_program_rule_tier_levels_program_rules	(id) ← program_rule_tier_levels(rule_id)
	fk_rule_match_program_rules_program_rule_id	(id) ← rule_match(program_rule_id)
	fk_rule_match_allocation_program_rules_program_rule_id	(id) ← rule_match_allocations(program_rule_id)
	fk_rule_match_lines_program_rules_program_rule_id	(id) ← rule_match_lines(program_rule_id)

Table promo_code_defs

Idx	Field Name	Data Type	Description
*	id	varchar(50)	readonly Unique value for every record/_id of the campaign
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	_id of the program
*	promo_name	varchar(250) encode zstd	campaign
	promo_description	varchar(250) encode zstd	Description
*	campaign_code	varchar(65535) encode zstd	Unique code of the campaign
	promo_type	varchar(50) encode zstd	Type of the promocode. Currently accepts Single Use only.
	code_length	integer encode zstd	Indicates the length of unique code generated via the respective campaign
	code_alphabet	varchar(65535) encode zstd	Character set considered while generating the unique promo codes
	code_cap	integer encode zstd	Number of unique promocodes to be generated
	start_date	timestamptz encode zstd	Start date of the campaign
	end_date	timestamptz encode zstd	End date of the campaign
	delete_flag	varchar(1) encode zstd DEFAULT 'N'::character varying
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	Sorting	ON updated_utc_ts, id
	pk_promo_codes_def_id	ON id
Foreign Keys
	fk_promo_code_defs	(loyalty_program_id) → loyalty_programs(id)
	fk_promo_code_defs_orgs	(org_id) → orgs(id)
	fk_promo_code_defs_users	(updated_by) → users(id)
	fk_promo_code_defs_users_created_by	(created_by) → users(id)
Referring Foreign Keys
	fk_promo_codes_promo_code_defs	(id) ← promo_codes(def_id)
Options
DISTSTYLE ALL

Table promo_codes

Idx	Field Name	Data Type	Description
*	id	varchar(50)	_id of the promocode
*	org_id	varchar(50) ENCODE zstd	_id of the organization
	member_id	varchar(50) ENCODE zstd	_id of the member who redeemed the promocode
*	promo_code	varchar(50) ENCODE zstd	unique id of the promocode
*	def_id	varchar(50) ENCODE zstd	_id of the campaign to which the promocode is part of
	status	varchar(50) ENCODE zstd	status of the promocode. Will be either of - New/Assigned/Redeemed
	redemption_date	timestamptz ENCODE zstd	Date on which the promocode is redeemed
	assignment_date	timestamptz ENCODE zstd	Date on which the promocode is assigned
	expiration_date	timestamptz ENCODE zstd	Date on which promo code will be expired
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamptz ENCODE zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz ENCODE raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_promo_codes_id	ON id
	unq_promo_codes	ON promo_code
	dist_promo_codes	ON def_id
	sort_promo_codes	ON updated_utc_ts, def_id
Foreign Keys
	fk_promo_codes_orgs	(org_id) → orgs(id)
	fk_promo_codes_members	(member_id) → members(id)
	fk_promo_codes_users_created_by	(created_by) → users(id)
	fk_promo_codes_users_updated_by	(updated_by) → users(id)
	fk_promo_codes_promo_code_defs	(def_id) → promo_code_defs(id)

Table purse_policies

Stores information describing how a purse will accrue and manage currency once points are given to it by rules. Keeps track of things like expiration, escrow, warning intervals, etc.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program ObjectId
*	policy_name	varchar(250) encode zstd	Name of the purse e.g. 'Points', 'Free Drinks', etc.
	policy_description	varchar(1000) encode lzo	Detailed description of the purse.
	expiration_hours	integer encode zstd	Number of hours to expiration of the points in the purse from the time they are earned.
	escrow_days	integer encode zstd	Number of days to keep points in balance before moving them to available balance. Escrow resolution is only available to days.
	primary_flag	boolean encode raw	Whether this is the primary purse.
	activity_based_expiration	boolean encode raw	Whether point expiration is considered from the last activity date for a member.
	expiration_snap_to	varchar(50) encode zstd	How expiration is calculated from the date of accrual. Domain: [day/week start/week end/month start/month end/quarter start/quarter end/half start/half end/year start/year end]
	escrow_snap_to	varchar(50) encode zstd	How escrow is calculated from the date of accrual. Domain: [day/week start/week end/month start/month end/quarter start/quarter end/half start/half end/year start/year end]
	expiration_warning_days	varchar(100) encode zstd	A comma separated list of days out from expiration when warnings will be generated. For example, [5,10] means generate warnings 10 and 5 days before accruals are about to expire.
*	effective_from	timestamptz encode zstd	Effective(start) date of the purse policy.
*	expiration_dt	timestamptz encode zstd	Date/time of expiration
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	reverse_sign	boolean	when set to true should be used to reverse the sign on the Points by multiplying by -1 in MCP
	overdraft_limit	decimal(18,6) encode zstd	Limit set at purse policy level to decide maximum limit of purse.balance that can go negative.
	pt_multiplier	decimal(18,6) encode zstd	On RCX and MCP UI, In memberPurses, AccrualItems, RedeemptionItems, purse.balance will be divided with the value specified in ptmultifier and displayed on UI.
	policy_group_name	varchar(50) ENCODE zstd	'"when set to true should be used to reverse the sign on the Points by multiplying by -1 in MCP"'
	period_start_date	timestamp ENCODE zstd	'"Limit set at purse policy level to decide maximum limit of purse.balance that can go negative"'
	period_end_date	timestamp ENCODE zstd	'"On RCX and MCP UI, In memberPurses, AccrualItems, RedeemptionItems..purse.balance will be divided with the value specified in ptmultifier and displayed on UI"'
	period_close_date	timestamp ENCODE zstd	Timestamp of the close date (which is also the buffer period) of the current qualifying purse
	period_timezone	varchar(50) zstd	While creating purse policy the periodTimezone is says that purse policy belongs to which timezone
	aggregates	varchar(4000) ENCODE zstd	The Aggregates functionality is used to calculate the member-level aggregates on points being earned/redeemed, rewards being earned, or any other metric that can be aggregated.
Indexes
	pk_purse_policies	ON id
	purse_policies_sort_key	ON id
Foreign Keys
	fk_purse_policies_users_created_by	(created_by) → users(id)
	fk_purse_policies_users_updated_by	(updated_by) → users(id)
	fk_purse_policies_orgs	(org_id) → orgs(id)
	fk_purse_policies_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_loyalty_accrual_items_purse_policies	(id) ← loyalty_accrual_items(purse_policy_id)
	fk_loyalty_redemption_items_purse_policies	(id) ← loyalty_redemption_items(purse_policy_id)
	fk_member_packaged_redemptions_purse_policies	(id) ← member_packaged_redemptions(purse_policy_id)
	fk_member_purse_purse_policies	(id) ← member_purses(purse_policy_id)

Table quarterly_aggregates

The total activity points accumulated throughout the respective quarter of the current year are saved in a collection known as quarterly aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	quarter	integer ENCODE zstd	Contains the value of the current quarter number in a year
	year	integer ENCODE zstd	Contains the current year number
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	quarter_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the quarter
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_quarterly_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_quarterly_aggregates_orgs	(org_id) → orgs(id)
	fk_quarterly_aggregates_members	(member_id) → members(id)
	fk_quarterly_aggregates_users_created_by	(created_by) → users(id)
	fk_quarterly_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table reward_policies

Stores reward policies, which carry information about all possible reward types which can be thought of as coupons - wallet rewards, offers, global offers. Keeps track of eligibility and execution parameters for each reward/offer.

The key field used to determine what kind of reward this is, is intended_use, which can be [Offer/Reward/Both/Global Offer].

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	unique The id of the record
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program for which the policy is created.
*	policy_name	varchar(250) encode zstd	The name of the RewardPolicy, which typically is similar to an Offer or Reward name - e.g. '30% Off Coca Cola'.
	policy_description	varchar(1000) encode lzo	Reward policy detailed description.
	expiration_hours	integer encode zstd	Number of hours to expiration of a Reward of Offer. Not applicable for Global Offers as they are governed by effective dates.
	intended_use	varchar(250) encode zstd	Determines what kind of policy this is - Reward, Offer, Both or Global Offer. Domain: [Reward/Offer/Both/Global Offer]
	number_of_uses	integer encode zstd	Number of uses allowed on the reward.
	expiration_warning_days	varchar(100) encode zstd	A comma separated list of days out from expiration when warnings will be generated. For example, [5,10] means generate warnings 10 and 5 days before a reward is about to expire.
	expiration_snap_to	varchar(50) encode zstd	How expiration is calculated from the date of effectivity of the reward. Does not apply to Global Offers. Domain: [day/week start/week end/month start/month end/quarter start/quarter end/half start/half end/year start/year end]
	expiry_unit	varchar(50) encode zstd	Rewards are able to expire in units less than a day. Internally we store it in expiration hours, however, it is possible to specify it in days as well. The exipry unit controls this. Domain: [Hours/Days]
	expiry_value	integer encode zstd	What expiry value was entered that goes with the expiry_unit. Together these fields will determine expiration_hours calculation.
	primary_product_quantity	integer encode zstd	Specifies how many products need to match from the primary product group.
	primary_product_price_type	varchar(50) encode zstd	Determines what kind of pricing will be applied to the primary product group on the matching items. Domain: [Package Price/Package Amount Off/Package Percent Off]
	primary_product_disc_val	decimal(18,6) ENCODE RAW	Primary products discount value. This will be the discount used along with pricing type for the product group to determine pricing.
	primary_product_max_disc	decimal(18,6) ENCODE RAW	Maximum discount limit for pricing applied to the primary product group.
	secondary_product_quantity	integer encode zstd	Specifies how many products need to match from the secondary product group.
	secondary_product_price_type	varchar(50) encode zstd	Determines what kind of pricing will be applied to the secondary product group on the matching items. Domain: [Package Price/Package Amount Off/Package Percent Off]
	secondary_product_disc_val	decimal(18,6) ENCODE RAW	Secondary products discount value. This will be the discount used along with pricing type for the product group to determine pricing.
	secondary_product_max_disc	decimal(18,6) ENCODE RAW	Maximum discount limit for pricing applied to the secondary product group.
	tertiary_product_quantity	integer encode zstd	Specifies how many products need to match from the tertiary product group.
	tertiary_product_price_type	varchar(50) encode zstd	Determines what kind of pricing will be applied to the tertiary product group on the matching items. Domain: [Package Price/Package Amount Off/Package Percent Off]
	tertiary_product_disc_val	decimal(18,6) ENCODE RAW	Tertiary products discount value. This will be the discount used along with pricing type for the product group to determine pricing.
	tertiary_product_max_disc	decimal(18,6) ENCODE RAW	Maximum discount limit for pricing applied to the tertiary product group.
	reward_priority	integer encode zstd	Priority of reward consideration.
*	upc	varchar(50) encode zstd	Markdown UPC to be applied for reward.
	reward_url	varchar(65535) encode zstd	URL pointing to an image of the Reward (for display in various types of apps, advertising, etc.)
*	expiration_dt	timestamptz encode zstd	Date/time of expiration. This is applicable to Global Offers only. Expiration of Reward/Offer/Both types is calcualted based on when it is awarded.
*	disc_type	varchar(50) encode zstd	Type of discount to be applied. Currently we support several discounts as shown in the domain below. Domain: [Combo/Combo List/Mix and Match/Ticket]
	price	decimal(18,6) ENCODE RAW	This field is used to control either the amount or the percent value applied for pricing depending on the type of discount selected.
	activity_based_expiration	boolean encode raw	A flag indicating whether or not reward policy will expire based on its original date, or the date of the last activity for a member. Domain: [t/f]
*	effective_from	timestamptz encode zstd	Effective date of the reward policy.
	cnt_limit	integer encode zstd	Limit of how many rewards can be given out.
	price_type	varchar(50) encode zstd	Determines what kind of pricing will be applied to the reward. Domain: [Package Price/Package Amount Off/Package Percent Off]
	budget	decimal(18,6) ENCODE RAW	Total discount budget for the reward.
	budget_used	decimal(18,6) ENCODE RAW	How much has been used already
	redemptions	integer encode zstd	No of time the reward has been used
	available_redemptions	integer encode zstd	Number of times an offer can be availed for the discount type ticket.
	available_budget	decimal(18,6) ENCODE RAW	The amount of money that is available for providing the ticket type discounts.
	min_purchase_amount	decimal(18,6) ENCODE RAW	Minimum transaction amount to qualify for a ticket type discount.
	max_discount_value	decimal(18,6) ENCODE zstd	Maximum discount available
	locations_namedlist_id	varchar(50) encode zstd	Name of the related locations named list
	primary_products_namedlist_id	varchar(50) encode zstd	Name of the related products named list
	secondary_products_namedlist_id	varchar(50) encode zstd	Name of the related products named list
	tertiary_products_namedlist_id	varchar(50) encode zstd	Name of the related products named list
	day_limit	integer encode zstd	Number of times a rule can be applied in a day at a member level
	week_limit	integer encode zstd	Number of times a rule can be applied in a week at a member level
	offer_limit	integer encode zstd	Number of times a rule can be applied during the effective period at a member level
	is_appeasement	boolean encode raw	If true, there is a possibility to add rewards manually from UI
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	per_transaction_limit	integer encode zstd	Has integer validation
	mandatory_product_quantity	integer encode zstd	The minimum quantity of mandatory products to be purchased for the policy to be effective.
	mandatory_products_namedlist_id	varchar(50) encode zstd	name of the related products named list
	excluded_products_namedlist_id	varchar(50) encode zstd	_id of the namedlist
	allow_item_overlap	boolean encode raw	Determines whether this offer allows overlapping items with other offers
	can_preview	boolean encode raw	Can be used before start date
	cool_off_period	integer encode zstd	Default is 0. A time window frame (in minutes) where the member cannot use the same offer again within coolOffPeriod from his last used (for offer and global offer).
	is_lockable	boolean	Flag determines if the reward policy can be locked
	lock_snap_to	varchar(50) encode zstd	Determines how the lockedTill date is calculated
	lock_value	integer encode zstd	Timeperiod for which the reward stays Locked
	lock_unit	varchar(50) encode zstd	Units of measurement (hours/days/months)
	single_use	boolean ENCODE RAW	Indicates if the reward policy can be applied on a transaction only once upon adding to member wallet
Indexes
	pk_reward_policies	ON id
	reward_policies_sort_key	ON id
Foreign Keys
	fk_reward_policies_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_reward_policies_users_created_by	(created_by) → users(id)
	fk_reward_policies_users_updated_by	(updated_by) → users(id)
	fk_reward_policies_orgs	(org_id) → orgs(id)
	fk_reward_policies_namedlists_locations_namedlist_id	(locations_namedlist_id) → namedlists(id)
	fk_reward_policies_namedlists_primary_products_namedlist_id	(primary_products_namedlist_id) → namedlists(id)
	fk_reward_policies_namedlists_secondary_products_namedlist_id	(secondary_products_namedlist_id) → namedlists(id)
	fk_reward_policies_namedlists_tertiary_product_namedlist_id	(tertiary_products_namedlist_id) → namedlists(id)
	fk_reward_policies_namedlists_mandatory_products_namedlist_id	(mandatory_products_namedlist_id) → namedlists(id)
	fk_reward_policies_namedlists_excluded_products_namedlist_id	(excluded_products_namedlist_id) → namedlists(id)
Referring Foreign Keys
	fk_loyalty_activity_best_offers_reward_policies	(id) ← loyalty_activity_best_offers(policy_id)
	fk_member_offers_reward_policies	(id) ← member_offers(reward_policy_id)
	fk_account_rewards_reward_policies	(id) ← member_rewards(reward_policy_id)
	fk_namedlists_data_reward_policies	(id) ← namedlists_data(model_id)
	fk_reward_policies_availability_reward_policies	(id) ← reward_policy_availability(reward_policy_id)
	fk_reward_policy_control_groups_reward_policies	(id) ← reward_policy_control_groups(reward_policy_id)
	fk_reward_policies_locations_reward_policies	(id) ← reward_policy_locations(reward_policy_id)
	fk_reward_policy_mandatory_segments_reward_policies_id	(id) ← reward_policy_mandatory_segments(reward_policy_id)
	fk_reward_policy_member_statuses_reward_policies	(id) ← reward_policy_member_statuses(reward_policy_id)
	fk_reward_policies_products_reward_policies	(id) ← reward_policy_products(reward_policy_id)
	fk_reward_policies_segments_reward_policies	(id) ← reward_policy_segments(reward_policy_id)
	fk_reward_policy_tier_levels_reward_policies	(id) ← reward_policy_tier_levels(reward_policy_id)
	fk_reward_upc_mapping_reward_policies	(id) ← reward_upc_mapping(reward_policy_id)

Table reward_policy_availability

Stores reward policy times of availability to enable day part rewads.

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw
*	org_id	varchar(50) encode zstd
*	loyalty_program_id	varchar(50) encode zstd
*	day_availability	varchar(50) encode zstd	What day the availability hours apply to. Domain: [monday/tuesday/wednesday/thursday/friday/saturday/sundary]
	start_hours	integer encode zstd	Beginning hours (e.g. if a policy is available 13:30 - 14:30 on a specific day, this field will be set to 13).
	start_mins	integer encode zstd	Beginning minutes (e.g. if a policy is available 13:30 - 14:30 on a specific day, this field will be set to 30).
	end_hours	integer encode zstd	Ending hours (e.g. if a policy is available 13:30 - 14:30 on a specific day, this field will be set to 14).
	end_mins	integer encode zstd	Ending Minutes (e.g. if a policy is available 13:30 - 14:30 on a specific day, this field will be set to 30).
	is_enabled	boolean encode raw	Whether this availability constraint is enabled or not. Domain: [t/f]
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_availability	ON reward_policy_id, day_availability
	reward_policy_availability_sort_key	ON reward_policy_id
Foreign Keys
	fk_reward_policies_availability_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_availability_created_by	(created_by) → users(id)
	fk_reward_policy_availability_updated_by	(updated_by) → users(id)
	fk_reward_policy_availability_orgs	(org_id) → orgs(id)
	fk_reward_policy_availability_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_control_groups

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique id of a reward policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	control_group_id	varchar(50) encode zstd	_id of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	reward_policy_control_groups_sort_key	ON updated_utc_ts, reward_policy_id, control_group_id
	pk_reward_policy_control_groups_reward_policy_id	ON reward_policy_id, control_group_id
Foreign Keys
	fk_reward_policy_control_groups_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_control_groups_segments	(control_group_id) → segments(id)
	fk_reward_policy_control_groups_users_created_by	(created_by) → users(id)
	fk_reward_policy_control_groups_users_updated_by	(updated_by) → users(id)
	fk_reward_policy_control_groups_orgs	(org_id) → orgs(id)
	fk_reward_policy_control_groups_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_locations

Stores locations which are included/excluded for the reward policy.

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique id of a reward policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	location_id	varchar(50) encode raw	_id of the location
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_locations	ON reward_policy_id, location_id
	reward_policy_locations_sort_key	ON updated_utc_ts, reward_policy_id, location_id
Foreign Keys
	fk_reward_policies_locations_locations	(location_id) → locations(id)
	fk_reward_policies_locations_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_locations_created_by	(created_by) → users(id)
	fk_reward_policy_locations_updated_by	(updated_by) → users(id)
	fk_reward_policy_locations_orgs	(org_id) → orgs(id)
	fk_reward_policy_locations_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_mandatory_segments

Stores mandate segments which are included for the reward policy.

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique id of a reward policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	mandatory_segment_id	varchar(50) encode zstd	_id of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_mandatory_segments	ON reward_policy_id, mandatory_segment_id
	reward_policy_mandatory_segments_sort_key	ON reward_policy_id
Foreign Keys
	fk_reward_policy_mandatory_segments_reward_policies_id	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_mandatory_segments_member_segments	(mandatory_segment_id) → member_segments(id)
	fk_reward_policy_mandatory_segments_user_created_by	(created_by) → users(id)
	fk_reward_policy_mandatory_segments_user_updated_by	(updated_by) → users(id)
	fk_reward_policy_mandatory_segments_orgs_org_id	(org_id) → orgs(id)
	fk_reward_policy_mandatory_segments_loyalty_programs_loyalty_program_id	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_member_statuses

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique ID of the Reward Policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	member_status	varchar(50) encode zstd	Indicates the status of members who will be eligible for the respective reward policy
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_member_statuses	ON reward_policy_id, member_status
	reward_policy_member_statuses_sort_key	ON updated_utc_ts, reward_policy_id
Foreign Keys
	fk_reward_policy_member_statuses_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_member_statuses_users_created_by	(created_by) → users(id)
	fk_reward_policy_member_statuses_users_updated_by	(updated_by) → users(id)

Table reward_policy_products

Stores primary, secondary and tertiary product associations within the policy which govern product eligibility.

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	The id of the record
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Program for which the policy is created.
*	product_type	varchar(50) encode zstd
*	product_id	varchar(50) encode raw
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_products	ON reward_policy_id, product_type, product_id
	reward_policy_products_sort_key	ON updated_utc_ts, reward_policy_id, product_id
Foreign Keys
	fk_reward_policies_products_products	(product_id) → products(id)
	fk_reward_policies_products_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_products_created_by	(created_by) → users(id)
	fk_reward_policy_products_updated_by	(updated_by) → users(id)
	fk_reward_policy_products_orgs	(org_id) → orgs(id)
	fk_reward_policy_products_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_segments

Stores segments which are included for the reward policy.

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique ID of a reward policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	segment_id	varchar(50) encode raw	_id of the segment
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_reward_policy_segments	ON reward_policy_id, segment_id
	reward_policy_segments_sort_key	ON updated_utc_ts, reward_policy_id, segment_id
Foreign Keys
	fk_reward_policies_segments_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policies_segments_segments	(segment_id) → segments(id)
	fk_reward_policy_segments_created_by	(created_by) → users(id)
	fk_reward_policy_segments_updated_by	(updated_by) → users(id)
	fk_reward_policy_segments_orgs	(org_id) → orgs(id)
	fk_reward_policy_segments_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table reward_policy_tier_levels

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique ID of the reward Policy
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	tier_policy_id	varchar(50) encode raw	_id of the Tier Policy
*	level_name	varchar(50) encode zstd	Current level. All levels comply with the Tier Policy
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	reward_policy_tier_levels_sort_key	ON updated_utc_ts, reward_policy_id, tier_policy_id
	pk_reward_policy_tier_levels_reward_policy_id	ON reward_policy_id, tier_policy_id, level_name
Foreign Keys
	fk_reward_policy_tier_levels_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_policy_tier_levels_orgs	(org_id) → orgs(id)
	fk_reward_policy_tier_levels_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_reward_policy_tier_levels_users_created_by	(created_by) → users(id)
	fk_reward_policy_tier_levels_users_updated_by	(updated_by) → users(id)
	fk_reward_policy_tier_levels_tier_policies	(tier_policy_id) → tier_policies(id)

Table reward_upc_mapping

Idx	Field Name	Data Type	Description
*	reward_policy_id	varchar(50) encode raw	unique ID of the reward Policy (Reference to reward_policies)
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
*	namedlist_id	varchar(50) encode zstd
*	upc	varchar(50) encode zstd	UPC code of the discount lineItem to be given when this reward is applied. Should usually exist in Products.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	default_amount	decimal(18,6) ENCODE zstd
Indexes
	reward_upc_mapping_sort_key	ON reward_policy_id
	pk_reward_upc_mapping	ON reward_policy_id, upc, namedlist_id
Foreign Keys
	fk_reward_upc_mapping_reward_policies	(reward_policy_id) → reward_policies(id)
	fk_reward_upc_mapping_orgs	(org_id) → orgs(id)
	fk_reward_upc_mapping_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_reward_upc_mapping_namedlists	(namedlist_id) → namedlists(id)

Table rule_match

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	loyalty_activity_id	varchar(50) encode raw	_id of the activity
	program_rule_id	varchar(50) encode raw	Unique ID of the rule
	rule_run_id	varchar(50) encode raw	New objectId if same rule ran more than once
	member_id	varchar(50) encode raw	Member ID for which history is collected
	match_count	integer encode zstd	The matchCount is equal to the min( primaryQty/primaryExpectedQty, secondaryQty/secondaryExpectedQty, tertiaryQty/tertiaryExpectedQty)
	primary_qty	integer encode zstd	Actual matching quantity of products belonging to Primary Product Group from the context
	primary_req_qty	integer encode zstd	Minimum matching quantity of products specified at the rule, belonging to Primary Product Group
	secondary_qty	integer encode zstd	Actual matching quantity of products belonging to Secondary Product Group from the context
	secondary_req_qty	integer encode zstd	Minimum matching quantity of products specified at the rule, belonging to Secondary Product Group
	tertiary_qty	integer encode zstd	Actual matching quantity of products belonging to Tertiary Product Group from the context
	tertiary_req_qty	integer encode zstd	Minimum matching quantity of products specified at the rule, belonging to Tertiary Product Group
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw	Indicates the activity date on which it is processed
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	rule_fired	boolean encode raw	Indicates if the rule is triggered
	execution_cnt	integer encode zstd	Number of times the rule has to be triggered, this variables overrides the per member limits on the rule.
	member_offer_limit	integer encode zstd	Maximum number of times the respective Offer can be applied on the context.
Indexes
	pk_rule_match	ON id
	rule_match_dist_key	ON member_id
	rule_match_sort_key	ON updated_utc_ts, loyalty_activity_id, activity_date, member_id, program_rule_id, rule_run_id
Foreign Keys
	fk_rule_match_members_member_id	(member_id) → members(id)
	fk_rule_match_loyalty_activities_loyalty_activity_id	(loyalty_activity_id) → loyalty_activities(id)
	fk_rule_match_program_rules_program_rule_id	(program_rule_id) → program_rules(id)
	fk_rule_match_users_created_by	(created_by) → users(id)
	fk_rule_match_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_rule_match_allocation_rule_match	(id) ← rule_match_allocations
	fk_rule_match_lines_rule_match	(id) ← rule_match_lines

Table rule_match_allocations

Idx	Field Name	Data Type	Description
	id	varchar(50) encode zstd	readonly unique Unique ID
*	loyalty_activity_id	varchar(50) encode raw
	program_rule_id	varchar(50) encode raw	Unique ID of the rule
	rule_run_id	varchar(50) encode raw	New objectId if same rule ran more than once
	member_id	varchar(50) encode raw	Member ID for which history is collected
	product_type	varchar(50) encode zstd
	allocation_line_num	integer encode zstd
	product_id	varchar(50) encode raw	Unique code of the product.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
Indexes
	rule_match_allocation_dist_key	ON member_id
	rule_match_allocations_sort_ket	ON updated_utc_ts, loyalty_activity_id, activity_date, member_id, program_rule_id, rule_run_id, product_id
Foreign Keys
	fk_rule_match_allocation_products_product_id	(product_id) → products(id)
	fk_rule_match_allocation_users_created_by	(created_by) → users(id)
	fk_rule_match_allocation_users_updated_by	(updated_by) → users(id)
	fk_rule_match_allocation_rule_match	(id) → rule_match
	fk_rule_match_allocation_members_member_id	(member_id) → members(id)
	fk_rule_match_allocation_program_rules_program_rule_id	(program_rule_id) → program_rules(id)
	fk_rule_match_allocations_loyalty_activitie_id	(loyalty_activity_id) → loyalty_activities(id)

Table rule_match_lines

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
*	loyalty_activity_id	varchar(50) encode raw	_id of the activity
*	program_rule_id	varchar(50) encode raw	Unique ID of the rule
	rule_run_id	varchar(50) encode raw	New objectId if same rule ran more than once
	member_id	varchar(50) encode raw	Member ID
	product_type	varchar(50) encode zstd
*	line_item_number	integer encode raw	Line numbers from the context that belong to the Primary Product Group for which the rule is applicable for
	product_id	varchar(50) encode raw	Unique code of the product.
	quantity	integer encode zstd	The quantity of the item
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode raw
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
*	activity_date	timestamptz encode raw	Indicates the activity date on which it is processed
	activity_utc_offset	integer encode zstd	Difference in minutes from Universal Time (UTC) for a particular place
	allocation_cnt	integer encode zstd
Indexes
	rule_match_line_dist_key	ON member_id
	pk_rule_match_lines	ON id, loyalty_activity_id, program_rule_id, line_item_number
	rule_match_lines_sort_key	ON updated_utc_ts, loyalty_activity_id, activity_date, member_id, program_rule_id, rule_run_id, product_id
Foreign Keys
	fk_rule_match_lines_products_product_id	(product_id) → products(id)
	fk_rule_match_lines_users_created_by	(created_by) → users(id)
	fk_rule_match_lines_users_updated_by	(updated_by) → users(id)
	fk_rule_match_lines_rule_match	(id) → rule_match
	fk_rule_match_lines_members_member_id	(member_id) → members(id)
	fk_rule_match_lines_program_rules_program_rule_id	(program_rule_id) → program_rules(id)
	fk_rule_match_lines_loyalty_activities_id	(loyalty_activity_id) → loyalty_activities(id)

Table segments

Stores currently available segments that can be assigned to members, reward_policies, rules.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	segment_name	varchar(250) encode zstd	Name of the segment. For example, 'Power Moms' or 'Morning Coffee Drinkers'.
	segment_description	varchar(400) encode zstd	Describes the behavior of the segment in more detail if needed.
	segment_type	varchar(50) encode zstd	This allows further categorization of the segments for reporting purposes.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_segments	ON id
	segments_sort_key	ON updated_utc_ts, id
Foreign Keys
	fk_segments_orgs	(org_id) → orgs(id)
	fk_segments_users_created_by	(created_by) → users(id)
	fk_segments_users_updated_by	(updated_by) → users(id)
Referring Foreign Keys
	fk_member_segments_segments	(id) ← member_segments(segment_id)
	fk_program_rule_control_groups_segments	(id) ← program_rule_control_groups(control_group_id)
	fk_program_rule_segments_segments	(id) ← program_rule_segments(segment_id)
	fk_reward_policy_control_groups_segments	(id) ← reward_policy_control_groups(control_group_id)
	fk_reward_policies_segments_segments	(id) ← reward_policy_segments(segment_id)

Table streak_goal_policies

Idx	Field Name	Data Type	Description
*	id	varchar(50)	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	streak_policy_id	varchar(50) encode zstd	Id of the streak policy which it belongs to
*	goal_policy_name	varchar(250) encode zstd	Name of the goal policy
	goal_policy_description	varchar(250) encode zstd	Brief description about the goal policy
*	time_limit	integer encode zstd	goal should be reached within the time limit
	time_limit_span	varchar(50) encode zstd	similar to time limit with certain period
	cool_off_time	integer encode zstd	streak and goal will be active for certain time period every day as per configuartion
*	target	integer encode zstd	target is something like goal should be reached by performing activities and bonus points will be added to member
	acc_rule	varchar(50) encode zstd	this rule will trigger once goal is started
	bonus_rule	varchar(50) encode zstd	this rule will trigger once goal is completed and bonus points will be added to member
	expiration_penalty_rule	varchar(50) encode zstd	penalty will filed when streak is expired as per the configuration
	delete_flag	varchar(1) encode zstd DEFAULT 'N'::character varying
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	Sorting	ON updated_utc_ts, id
	pk_streak_goal_policies	ON id
Foreign Keys
	fk_streak_goal_policies_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_streak_goal_policies_orgs	(org_id) → orgs(id)
	fk_streak_goal_policies_streak_policies	(streak_policy_id) → streak_policies(id)
	fk_streak_goal_policies_updated_by	(updated_by) → users(id)
	fk_streak_goal_policies_users	(created_by) → users(id)
Options
DISTSTYLE ALL

Table streak_policies

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE raw	readonly unique Unique ID
*	org_id	varchar(50) ENCODE zstd	It defines organization Id
*	loyalty_program_id	varchar(50) ENCODE zstd	required Program's mongoDB ID.
*	streak_policy_name	varchar(100) ENCODE zstd	It defines name of the streak policy
*	rule_template	varchar(50) ENCODE zstd
	streak_policy_description	varchar(250) ENCODE zstd	It describes about the streak policy
	effective_from	timestamptz ENCODE zstd	It defines when streak policy will be available
	expiration_dt	timestamptz ENCODE zstd	It defines when streakpolicy will expire
	time_limit	integer ENCODE zstd	Number of days(in minutes) streak is active for the member from the start of streak
	cool_off_time	integer ENCODE zstd	Cool off time in minutes defined in the Streak Policy.
	instance_limit	integer ENCODE zstd	Instance limit of the streak.
	optin_rule	varchar(50) ENCODE zstd	It defines optinRule in streak policy
	acc_rule	varchar(50) ENCODE zstd	It defines Accumulation Rule in streak policy
	eval_rule	varchar(50) ENCODE zstd	It defines Evaluation Rule in streak policy
	bonus_rule	varchar(50) ENCODE zstd	It defines bonusRule in streak policy
	progress_rule	varchar(50) ENCODE zstd	It defines progressRule in streak policy
	expiration_penalty_rule	varchar(50) ENCODE zstd	It defines expirationPenaltyRule in streak policy
	precision	integer encode zstd
	streak_version	integer encode zstd	Version number.
	streak_status	varchar(50) encode zstd	It defines status of the streak policy either Draft or Deployed
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamptz ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamptz ENCODE raw
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
	optout_rule	varchar(50) encode zstd	refrering to streak opt out rule in rule template as like optin rule and eval rule, etc
	mem_level_override	boolean	if it true we will override policy fields of goal policy like target and bonus value with member specific values
	num_of_goals_needed	integer ENCODE zstd	Number of goals need to be completed to complete the streak
Indexes
	pk_streak_policies_id	ON id
	sort_streak_policies	ON updated_utc_ts, id
Foreign Keys
	fk_streak_policies_users_created_by	(created_by) → users(id)
	fk_streak_policies_users_updated_by	(updated_by) → users(id)
	fk_streak_policies_orgs	(org_id) → orgs(id)
	fk_streak_policies_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
Referring Foreign Keys
	fk_members_streak_streak_policies	(id) ← members_streak(streak_policy_id)
	fk_streak_event_logs_streak_policies_id	(id) ← members_streak_event_logs(streak_policy_id)
	fk_members_streak_goals_streak_policies	(id) ← members_streak_goals(streak_policy_id)
	fk_streak_goal_policies_streak_policies	(id) ← streak_goal_policies(streak_policy_id)
	fk_streak_policy_optin_segments_streak_policies	(id) ← streak_policy_optin_segments(streak_policy_id)

Table streak_policy_optin_segments

Idx	Field Name	Data Type	Description
*	streak_policy_id	varchar(50)	Unique Id
*	optin_segment_id	varchar(50) encode zstd
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	the _id of the Program in which the member is enrolled
	delete_flag	varchar(1) encode zstd DEFAULT 'N'::character varying
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	Sorting	ON streak_policy_id
	pk_streak_policy_optin_segments	ON streak_policy_id, optin_segment_id
Foreign Keys
	fk_streak_policy_optin_segments_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_streak_policy_optin_segments_orgs	(org_id) → orgs(id)
	fk_streak_policy_optin_segments_streak_policies	(streak_policy_id) → streak_policies(id)
	fk_streak_policy_optin_segments_updated_by	(updated_by) → users(id)
	fk_streak_policy_optin_segments_users_created_by	(created_by) → users(id)
Options
DISTSTYLE ALL

Table tier_policies

Stores information about the existence of separate tier structures in the program.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	readonly unique Unique ID
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	required Program's mongoDB ID.
*	tier_name	varchar(250) encode zstd	Identifies the name of the tier structure. Most programs track a single tier structure, so this name is usually called something like 'Standard'.
	primary_flag	boolean encode raw	Inidicate if this is the primary tier for this program.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
Indexes
	pk_tier_policies	ON id
	tier_policies_sort_key	ON id
Foreign Keys
	fk_tier_policies_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)
	fk_tier_policies_users_created_by	(created_by) → users(id)
	fk_tier_policies_users_updated_by	(updated_by) → users(id)
	fk_tier_policies_orgs	(org_id) → orgs(id)
Referring Foreign Keys
	fk_member_loyalty_tiers_tier_policies	(id) ← member_tiers(tier_policy_id)
	fk_program_rule_tier_levels_tier_policies	(id) ← program_rule_tier_levels(tier_policy_id)
	fk_reward_policy_tier_levels_tier_policies	(id) ← reward_policy_tier_levels(tier_policy_id)
	fk_tier_policy_levels_tier_policies	(id) ← tier_policy_levels(tier_policy_id)

Table tier_policy_levels

Stores configuration settings for tier levels and how they will be managed in the program.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode raw	unique _id of the record
*	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
*	loyalty_program_id	varchar(50) encode zstd	Reference to [Program] (#programs)
	tier_policy_id	varchar(50) encode zstd	Refers to the id of the parent tier for this level.
*	level_name	varchar(50) encode zstd	Identifies the level of the tier. For example, typical level names are 'Welcome', 'Gold', 'Platinum', etc.
	is_default	boolean encode raw	Whether or not this is the default level upon member enrollmnet. Domain: [t/f]
	expiration_hours	integer encode zstd	Number of hours to expiration of tier level.
	expiration_snap_to	varchar(50) encode zstd	How expiration is calculated from the date of tier assignment. Domain: [day/week start/week end/month start/month end/quarter start/quarter end/half start/half end/year start/year end]
	expiration_warning_days	varchar(100) encode zstd	A comma separated list of days out from expiration when warnings will be generated. For example, [5,10] means generate warnings 10 and 5 days before a tier level is about to expire.
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	level_number	integer encode zstd	Number in the order of levels. Inidicates the order in which levels are achieved.
	level_threshold	integer encode zstd	threshold field is added to configure Tier policy rules with which we can identify total number of points that are need for upgrading member to next tier level
Indexes
	tier_policy_levels_sort_key	ON id
	pk_tier_policy_levels	ON id
Foreign Keys
	fk_tier_policy_levels_tier_policies	(tier_policy_id) → tier_policies(id)
	fk_tier_policy_levels_users_created_by	(created_by) → users(id)
	fk_tier_policy_levels_users_updated_by	(updated_by) → users(id)
	fk_tier_policy_levels_orgs	(org_id) → orgs(id)
	fk_tier_policy_levels_loyalty_programs	(loyalty_program_id) → loyalty_programs(id)

Table users

Users table provides information about the data created by and updated by from source.

Idx	Field Name	Data Type	Description
*	id	varchar(50) encode zstd	readonly unique Unique ID
	org_id	varchar(50) encode zstd	ObjectId of the organization which owns the record.
	login	varchar(255) encode zstd	login id of the user
	email	varchar(65535) encode zstd	email id to which the login is tagged with
	is_active	boolean encode raw	Indicates if the user is active
	is_blocked	boolean encode raw	Indicates if the user is blocked
	delete_flag	varchar(1) encode zstd DEFAULT 'N'
	created_by	varchar(50) encode zstd
	created_utc_ts	timestamptz encode zstd
	updated_by	varchar(50) encode zstd
	updated_utc_ts	timestamptz encode zstd
	orgnl_job_id	bigint encode zstd
	ltst_job_id	bigint encode zstd
	emp_name	varchar(250) encode zstd	Name of the user. Applicable for users created via. Okta integration.
	emp_number	varchar(250) encode zstd	Employee number of the user. Applicable for users created via. Okta integration.
Indexes
	pk_users_id	ON id
Referring Foreign Keys
	fk_account_members_users_created_by	(id) ← account_members(created_by)
	fk_account_members_users_updated_by	(id) ← account_members(updated_by)
	fk_accounts_users_created_by	(id) ← accounts(created_by)
	fk_accounts_users_updated_by	(id) ← accounts(updated_by)
	fk_daily_aggregates_users_created_by	(id) ← daily_aggregates(created_by)
	fk_daily_aggregates_users_updated_by	(id) ← daily_aggregates(updated_by)
	fk_halfyearly_aggregates_users_created_by	(id) ← halfyearly_aggregates(created_by)
	fk_halfyearly_aggregates_users_updated_by	(id) ← halfyearly_aggregates(updated_by)
	fk_lifetime_aggregates_users_created_by	(id) ← lifetime_aggregates(created_by)
	fk_lifetime_aggregates_users_updated_by	(id) ← lifetime_aggregates(updated_by)
	fk_limits_users	(id) ← limits(principal_id)
	fk_location_overrides_users_created_by	(id) ← location_overrides(created_by)
	fk_location_overrides_users_updated_by	(id) ← location_overrides(updated_by)
	fk_locations_users_created_by	(id) ← locations(created_by)
	fk_locations_users_updated_by	(id) ← locations(updated_by)
	fk_loyalty_accrual_items_users_created_by	(id) ← loyalty_accrual_items(created_by)
	fk_loyalty_accrual_items_users_updated_by	(id) ← loyalty_accrual_items(updated_by)
	fk_loyalty_activities_users_created_by	(id) ← loyalty_activities(created_by)
	fk_loyalty_activities_users_updated_by	(id) ← loyalty_activities(updated_by)
	fk_loyalty_activity_best_offers_created_by	(id) ← loyalty_activity_best_offers(created_by)
	fk_loyalty_activity_best_offers_updated_by	(id) ← loyalty_activity_best_offers(updated_by)
	fk_loyalty_activity_line_items_created_by	(id) ← loyalty_activity_line_items(created_by)
	fk_loyalty_activity_line_items_updated_by	(id) ← loyalty_activity_line_items(updated_by)
	fk_loyalty_activity_results_errors_users_created_by	(id) ← loyalty_activity_results_errors(created_by)
	fk_loyalty_activity_results_errors_users_updated_by	(id) ← loyalty_activity_results_errors(updated_by)
	fk_loyalty_activity_tender_items_created_by	(id) ← loyalty_activity_tender_items(created_by)
	fk_loyalty_activity_tender_items_updated_by	(id) ← loyalty_activity_tender_items(updated_by)
	fk_loyalty_cards_users_created_by	(id) ← loyalty_cards(created_by)
	fk_loyalty_cards_users_updated_by	(id) ← loyalty_cards(updated_by)
	fk_loyalty_programs_users_created_by	(id) ← loyalty_programs(created_by)
	fk_loyalty_programs_users_updated_by	(id) ← loyalty_programs(updated_by)
	fk_loyalty_redemption_items_created_by	(id) ← loyalty_redemption_items(created_by)
	fk_loyalty_redemption_items_updated_by	(id) ← loyalty_redemption_items(updated_by)
	fk_member_badges_users_created_by	(id) ← member_badges(created_by)
	fk_member_badges_users_updated_by	(id) ← member_badges(updated_by)
	fk_member_loyalty_ids_users_created_by	(id) ← member_loyalty_ids(created_by)
	fk_member_loyalty_ids_users_updated_by	(id) ← member_loyalty_ids(updated_by)
	fk_member_notes_users_created_by	(id) ← member_notes(created_by)
	fk_member_notes_users_updated_by	(id) ← member_notes(updated_by)
	fk_member_offer_usage_histories_created_by	(id) ← member_offer_usage_histories(created_by)
	fk_member_offer_usage_histories_updated_by	(id) ← member_offer_usage_histories(updated_by)
	fk_member_offers_users_created_by	(id) ← member_offers(created_by)
	fk_member_offers_users_updated_by	(id) ← member_offers(updated_by)
	fk_member_packaged_redemptions_users	(id) ← member_packaged_redemptions(created_by)
	fk_member_packaged_redemptions	(id) ← member_packaged_redemptions(updated_by)
	fk_member_preferences_users_created_by	(id) ← member_preferences(created_by)
	fk_member_preferences_users_updated_by	(id) ← member_preferences(updated_by)
	fk_member_purses_users_created_by	(id) ← member_purses(created_by)
	fk_member_purses_users_updated_by	(id) ← member_purses(updated_by)
	fk_member_referrals_users_created_by	(id) ← member_referrals(created_by)
	fk_member_referrals_users_updated_by	(id) ← member_referrals(updated_by)
	fk_member_reward_usage_histories_created_by	(id) ← member_reward_usage_histories(created_by)
	fk_member_reward_usage_histories_updated_by	(id) ← member_reward_usage_histories(updated_by)
	fk_member_rewards_users_created_by	(id) ← member_rewards(created_by)
	fk_member_rewards_users_updated_by	(id) ← member_rewards(updated_by)
	fk_member_segments_users_created_by	(id) ← member_segments(created_by)
	fk_member_segments_users_updated_by	(id) ← member_segments(updated_by)
	fk_member_streak_actions_users_created_by	(id) ← member_streak_actions(created_by)
	fk_member_streak_actions_users_updated_by	(id) ← member_streak_actions(updated_by)
	fk_member_streaks_users_created_by	(id) ← member_streaks(created_by)
	fk_member_streaks_users_updated_by	(id) ← member_streaks(updated_by)
	fk_member_tier_histories_users_created_by	(id) ← member_tier_histories(created_by)
	fk_member_tier_histories_users_updated_by	(id) ← member_tier_histories(updated_by)
	fk_member_loyalty_tiers_users_created_by	(id) ← member_tiers(created_by)
	fk_member_loyalty_tiers_users_updated_by	(id) ← member_tiers(updated_by)
	fk_member_transient_activities_users_created_by	(id) ← member_transient_activities(created_by)
	fk_member_transient_activities_users_updated_by	(id) ← member_transient_activities(updated_by)
	fk_members_users_created_by	(id) ← members(created_by)
	fk_members_users_updated_by	(id) ← members(updated_by)
	fk_members_streak_updated_by	(id) ← members_streak(updated_by)
	fk_members_streak_users	(id) ← members_streak(created_by)
	fk_members_streak_event_logs_users_created_by	(id) ← members_streak_event_logs(created_by)
	fk_members_streak_event_logs_users_updated_by	(id) ← members_streak_event_logs(updated_by)
	fk_member_streak_goals_created_by	(id) ← members_streak_goals(created_by)
	fk_member_streak_goals_updated_by	(id) ← members_streak_goals(updated_by)
	fk_merge_histories_users_created_by	(id) ← merge_histories(created_by)
	fk_merge_histories_users_updated_by	(id) ← merge_histories(updated_by)
	fk_merged_members_users_created_by	(id) ← merged_members(created_by)
	fk_merged_members_users_updated_by	(id) ← merged_members(updated_by)
	fk_monthly_aggregates_orgs	(id) ← monthly_aggregates(org_id)
	fk_monthly_aggregates_users_created_by	(id) ← monthly_aggregates(created_by)
	fk_monthly_aggregates_users_updated_by	(id) ← monthly_aggregates(updated_by)
	fk_namedlists_users_updated_by	(id) ← namedlists(updated_by)
	fk_namedlists_users_created_by	(id) ← namedlists(created_by)
	fk_orgs_users_created_by	(id) ← orgs(created_by)
	fk_orgs_users_updated_by	(id) ← orgs(updated_by)
	fk_partners_users_created_by	(id) ← partners(created_by)
	fk_partners_users_updated_by	(id) ← partners(updated_by)
	fk_products_users_created_by	(id) ← products(created_by)
	fk_products_users_updated_by	(id) ← products(updated_by)
	fk_program_rule_actions_users_created_by	(id) ← program_rule_actions(created_by)
	fk_program_rule_actions_users_updated_by	(id) ← program_rule_actions(updated_by)
	fk_program_rule_availability_created_by	(id) ← program_rule_availability(created_by)
	fk_program_rule_availability_updated_by	(id) ← program_rule_availability(updated_by)
	fk_program_rule_conditions_users_created_by	(id) ← program_rule_conditions(created_by)
	fk_program_rule_conditions_users_updated_by	(id) ← program_rule_conditions(updated_by)
	fk_program_rule_control_groups_users_updated_by	(id) ← program_rule_control_groups(updated_by)
	fk_program_rule_control_groups_users_created_by	(id) ← program_rule_control_groups(created_by)
	fk_program_rule_folders_users_updated_by	(id) ← program_rule_folders(updated_by)
	fk_program_rule_folders_users_created_by	(id) ← program_rule_folders(created_by)
	fk_program_rule_limits_users_created_by	(id) ← program_rule_limits(created_by)
	fk_program_rule_limits_users_updated_by	(id) ← program_rule_limits(updated_by)
	fk_program_rule_locations_created_by	(id) ← program_rule_locations(created_by)
	fk_program_rule_locations_updated_by	(id) ← program_rule_locations(updated_by)
	fk_program_rule_segments_users_created_by	(id) ← program_rule_mandatory_segments(created_by)
	fk_program_rule_segments_users_updated_by	(id) ← program_rule_mandatory_segments(updated_by)
	fk_program_rule_member_statuses_users_created_by	(id) ← program_rule_member_statuses(created_by)
	fk_program_rule_member_statuses_users_updated_by	(id) ← program_rule_member_statuses(updated_by)
	fk_program_rule_products_users_updated_by	(id) ← program_rule_products(updated_by)
	fk_program_rule_products_users_created_by	(id) ← program_rule_products(created_by)
	fk_program_rule_segments_users_created_by	(id) ← program_rule_segments(created_by)
	fk_program_rule_segments_users_updated_by	(id) ← program_rule_segments(updated_by)
	fk_program_rule_tier_levels_users_created_by	(id) ← program_rule_tier_levels(created_by)
	fk_program_rule_tier_levels_users_updated_by	(id) ← program_rule_tier_levels(updated_by)
	fk_program_rules_users_updated_by	(id) ← program_rules(updated_by)
	fk_program_rules_users_created_by	(id) ← program_rules(created_by)
	fk_promo_code_defs_users	(id) ← promo_code_defs(updated_by)
	fk_promo_code_defs_users_created_by	(id) ← promo_code_defs(created_by)
	fk_promo_codes_users_created_by	(id) ← promo_codes(created_by)
	fk_promo_codes_users_updated_by	(id) ← promo_codes(updated_by)
	fk_purse_policies_users_created_by	(id) ← purse_policies(created_by)
	fk_purse_policies_users_updated_by	(id) ← purse_policies(updated_by)
	fk_quarterly_aggregates_users_created_by	(id) ← quarterly_aggregates(created_by)
	fk_quarterly_aggregates_users_updated_by	(id) ← quarterly_aggregates(updated_by)
	fk_reward_policies_users_created_by	(id) ← reward_policies(created_by)
	fk_reward_policies_users_updated_by	(id) ← reward_policies(updated_by)
	fk_reward_policy_availability_created_by	(id) ← reward_policy_availability(created_by)
	fk_reward_policy_availability_updated_by	(id) ← reward_policy_availability(updated_by)
	fk_reward_policy_control_groups_users_created_by	(id) ← reward_policy_control_groups(created_by)
	fk_reward_policy_control_groups_users_updated_by	(id) ← reward_policy_control_groups(updated_by)
	fk_reward_policy_locations_created_by	(id) ← reward_policy_locations(created_by)
	fk_reward_policy_locations_updated_by	(id) ← reward_policy_locations(updated_by)
	fk_reward_policy_mandatory_segments_user_created_by	(id) ← reward_policy_mandatory_segments(created_by)
	fk_reward_policy_mandatory_segments_user_updated_by	(id) ← reward_policy_mandatory_segments(updated_by)
	fk_reward_policy_member_statuses_users_created_by	(id) ← reward_policy_member_statuses(created_by)
	fk_reward_policy_member_statuses_users_updated_by	(id) ← reward_policy_member_statuses(updated_by)
	fk_reward_policy_products_created_by	(id) ← reward_policy_products(created_by)
	fk_reward_policy_products_updated_by	(id) ← reward_policy_products(updated_by)
	fk_reward_policy_segments_created_by	(id) ← reward_policy_segments(created_by)
	fk_reward_policy_segments_updated_by	(id) ← reward_policy_segments(updated_by)
	fk_reward_policy_tier_levels_users_created_by	(id) ← reward_policy_tier_levels(created_by)
	fk_reward_policy_tier_levels_users_updated_by	(id) ← reward_policy_tier_levels(updated_by)
	fk_rule_match_users_created_by	(id) ← rule_match(created_by)
	fk_rule_match_users_updated_by	(id) ← rule_match(updated_by)
	fk_rule_match_allocation_users_created_by	(id) ← rule_match_allocations(created_by)
	fk_rule_match_allocation_users_updated_by	(id) ← rule_match_allocations(updated_by)
	fk_rule_match_lines_users_created_by	(id) ← rule_match_lines(created_by)
	fk_rule_match_lines_users_updated_by	(id) ← rule_match_lines(updated_by)
	fk_segments_users_created_by	(id) ← segments(created_by)
	fk_segments_users_updated_by	(id) ← segments(updated_by)
	fk_streak_goal_policies_updated_by	(id) ← streak_goal_policies(updated_by)
	fk_streak_goal_policies_users	(id) ← streak_goal_policies(created_by)
	fk_streak_policies_users_created_by	(id) ← streak_policies(created_by)
	fk_streak_policies_users_updated_by	(id) ← streak_policies(updated_by)
	fk_streak_policy_optin_segments_updated_by	(id) ← streak_policy_optin_segments(updated_by)
	fk_streak_policy_optin_segments_users_created_by	(id) ← streak_policy_optin_segments(created_by)
	fk_tier_policies_users_created_by	(id) ← tier_policies(created_by)
	fk_tier_policies_users_updated_by	(id) ← tier_policies(updated_by)
	fk_tier_policy_levels_users_created_by	(id) ← tier_policy_levels(created_by)
	fk_tier_policy_levels_users_updated_by	(id) ← tier_policy_levels(updated_by)
	fk_weekly_aggregates_users_created_by	(id) ← weekly_aggregates(created_by)
	fk_weekly_aggregates_users_updated_by	(id) ← weekly_aggregates(updated_by)
	fk_yearly_aggregates_users_created_by	(id) ← yearly_aggregates(created_by)
	fk_yearly_aggregates_users_updated_by	(id) ← yearly_aggregates(updated_by)

Table weekly_aggregates

The total activity points accumulated throughout the week are saved in a collection known as weekly aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	week	integer ENCODE zstd	Contains the current week number value
	year	integer ENCODE zstd	consists the value of current year
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	week_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the week
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_weekly_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_weekly_aggregates_orgs	(org_id) → orgs(id)
	fk_weekly_aggregates_members	(member_id) → members(id)
	fk_weekly_aggregates_users_created_by	(created_by) → users(id)
	fk_weekly_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY

Table yearly_aggregates

The total activity points accumulated throughout the year are saved in a collection known as yearly aggregates.

Idx	Field Name	Data Type	Description
*	id	varchar(50) ENCODE RAW
*	org_id	varchar(50) ENCODE zstd
	member_id	varchar(50) ENCODE zstd
*	metric_name	varchar(250) ENCODE zstd	The metric name will be the purse name or custom metric name
	year	integer ENCODE zstd	Contains the value of current year
	last_aggregate_date	timestamp ENCODE zstd	The date of activity processed is stored as last_aggregate_date
	year_aggregate_value	decimal(18,6) ENCODE zstd	The total activity points accumulated throughout the year
	is_global	boolean ENCODE RAW
	merge_ids	super ENCODE zstd
	expiration_date	timestamp ENCODE zstd
	delete_flag	varchar(1) ENCODE zstd DEFAULT 'N'::character varying
	created_by	varchar(50) ENCODE zstd
	created_utc_ts	timestamp ENCODE zstd
	updated_by	varchar(50) ENCODE zstd
	updated_utc_ts	timestamp ENCODE az64
	orgnl_job_id	bigint ENCODE zstd
	ltst_job_id	bigint ENCODE zstd
Indexes
	pk_yearly_aggregates	ON id
	distribution	ON member_id
	sorting	ON updated_by, member_id, id
Foreign Keys
	fk_yearly_aggregates_orgs	(org_id) → orgs(id)
	fk_yearly_aggregates_members	(member_id) → members(id)
	fk_yearly_aggregates_users_created_by	(created_by) → users(id)
	fk_yearly_aggregates_users_updated_by	(updated_by) → users(id)
Options
DISTSTYLE KEY