There are total 7 tables which magento use to save and process catalog rules, but the important tables are
This is the main table which stores the catalogrule data, things like rule name, from, to, actions, conditions etc
The data from entire admin form which we fill up is stored in this table
This table store, which all products are applicable for catalogrule. This table stores relationship between rule_id and product_id (other information as well).
This table gets filled up only when we click the Apply Rule button.
This table stores the discounted product price for each product associated with a rule. When displaying price on frontend, this table is directly used to display prices.